Quick Queries #49
By Richard Rost
8 months ago
Screen Capture Reports, Overlapping Windows Setting
In this Microsoft Access tutorial, I will answer a variety of viewer questions including how to reverse the fill-in-PDF process, tips for reproducing forms using screen captures, using event handlers like Enter and Got Focus, setting up overlapping windows versus tabbed documents, when to use Execute or RunSQL with queries, best practices for custom DLookup functions, managing many-to-many relationships for sales reps and states, and a discussion on the speed differences between recordsets and SQL. This is part 49.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, PDF to Access, Fill In PDF, SendKeys, screen capture, Print Documents, overlapping windows, tabbed documents, event handler, Enter event, Got Focus event, SelStart, SelLength, recordset loop, spaced repetition, Execute, action query, select query, RowSource, RecordSource, DLookup, MyDLookup, many-to-many relationship, junction table, sales reps, recordsets, SQL server, iterative looping
Intro In this video, we'll talk about a variety of Microsoft Access topics in Quick Queries number 49. You'll hear answers to questions about importing PDF forms into Access reports using screen captures and overlays, setting up overlapping windows instead of tabbed documents, choosing between Enter and Got Focus events for field focus, and the difference between RunSQL, Execute, and select queries. We'll also discuss naming custom functions, building many-to-many relationships for scenarios like sales reps and states, and comparing the speed and use cases for SQL versus recordsets in Access.Transcript This is Quick Queries number 49.
For those of you who do not know, Quick Queries is when I take all the little questions you guys send me throughout the week, whether they are comments on YouTube, emails, on my forums, on my website, or whatever, and I put them together in a big ball of question and answer goodness and serve it to you on a plate of - I do not know where this is going, but let's get into it.
First up, SM Hamid wants to know if we can do this in reverse - PDF to Access. He is talking about my Fill In PDF video where I teach you how to take a PDF that has blanks in it and then you can use Access to open up that PDF in your web browser, fill it in, and then save it using SendKeys. Not the best solution, and yes, there are other ways to automate PDFs, but that requires having the full paid version of Acrobat, which I personally never have. In fact, Sammy, one of my moderators, asked me this morning if I had ever worked with that. I have not. I just have not really had the need for it because everything that I have always wanted to do with PDF files, I can do straight from Access.
I can create a document that looks exactly like the form you want to replicate and then just export it as a PDF. So if your question, Hamid, is to do this in reverse, meaning to take a PDF file and bring that into Access, then the only way that I can suggest to do that would be to do a screen capture, and I show how to do that in my Print Documents video where I take different actual documents and bring those over into Microsoft Access.
Here you can see an old original Basic Dungeons and Dragons character sheet. You just open it up on your screen, take a screen capture of it, drop it into a report, and then you could put text boxes over the report and then print the whole thing out. No need for Acrobat. So can it be done? Yes. Is it the most elegant solution? Probably not, but it will work. I used to do it all the time.
I had this one company, in fact, they did medical billing and they are like, no, we have to reproduce this form exactly. We have to print on this form. I am like, well, does it have to be that actual form or can I reproduce the form? They are like, well, you can reproduce the form, but it has got to match this exactly, like down to the barcodes on it. And yep, I matched the whole thing perfectly, so they are still using it to this day. I think I built this like 20 years ago. So it all depends on how much work you want to put into it.
Next up, T-coving says, I am torn. Do I believe you will get to more cool stuff tomorrow or not believe because you said you would get to more cool stuff tomorrow? I need an air handler. If I say I am going to get to more cool stuff tomorrow, I would say nine times out of ten, I actually do. Once in a while, it has happened where something came up, I got busy, whatever.
Like today, for example, I had some oral surgery last week and my tooth has gotten infected, so I was really on the shelf as to whether or not I was going to record Quick Queries today, but I am pushing through. I am doing the best I can. So yeah, if I say it, I pretty much always keep my word.
Arm Tets says, would not I be better off putting an event handler into the Enter or Got Focus events? Okay, so I think what he is talking about is I do this thing where, if you click on a field, instead of the entire field's text being selected (which is the default), it puts the cursor at the end of the block using the SelStart and SelLength. And that is certainly fine.
If you want to put it in the Enter or the Got Focus events, that will work. I usually use my mouse to click around stuff, but if you are very keyboard heavy and you like to tab around, then by all means, put it in the Got Focus event too. That will work either way. Whatever you like.
Kind of funny that I watched this and thought, yeah, I got it, only to go into Access and start typing and realize I did not have it. But I figured out I do not know how many times I have done this myself. I have read something in a book and then I am like, all right, that seems easy enough, I got it, and then I go open up Access and I am like, all right, what did it say?
That is why repetition is the best way to learn something. Well, the best way to learn something is to teach someone else. I have found that out. When I teach someone else something, it sticks better in my brain. But just doing it - that is why in a lot of my courses, I will repeat something that I have covered three or four times already. I will do it again, like writing a recordset loop. Let's do it again.
Some people get upset because, oh, you just repeated yourself. Well, yeah, that is how you learn, by repeating. It is called spaced repetition. You cover it once, and then you maybe cover it again in two or three lessons. Then you cover it again maybe in five or six lessons, then you cover it again maybe ten lessons after that. The spacing gets bigger, but you still keep hitting those things that you want to reinforce.
Chelsea says, how do you get the tables to pop out in the way you can have them off to the side to view both? I think what you are talking about is overlapping windows. Right, this style like here where every window, every form, every table, every query opens up in its own little window inside of Access, as opposed to the default, which is the tab document interface. I cannot stand that.
I have a video here explaining what it is and I cover this in my Access Beginner 1 class. It is my free class - everyone should go watch it. I cannot stand the tab interface. If you look at any database, these are overlapping windows and you can rearrange them and you can put them over here and you can put them over there. This is how I like my database to behave and you can change it under Options, and then Current Database, and you have Overlapping Windows or Tabbed Documents.
Hit OK, you have to reopen the database, and then you get this mess. Every form is like this with a tab, and you open up a table and it is the whole thing, and you open up this one, it is the whole thing. I do not like this. No. That is why I prefer overlapping windows. This is one of those things that I have to keep explaining every couple of videos on how to do it because people do not watch the Access Beginner 1 class. Go watch that. It is free and it covers everything you need to know to get started with Access and lots of good tips and tricks. Even for people that say, oh, I have been working with Access for five years. Oh, okay, great. Go watch this. You will still learn some things.
Mark Jackson says, I understood Execute only works with action queries - can you use non-action queries? Well, a non-action query is a select query, and no. RunSQL and Execute both only work with action queries - inserting, appending, deleting, make-table. If you are going to use a select query, then that has to either be in a saved query itself or as the RowSource or RecordSource for another object. You cannot just say, you know, CurrentDB.Execute and then a select statement.
Riel Ramirez was talking about naming. I think you talked about - what was it - a Will Riker function last time or something like that? He says if you name your function DLookup instead of MyDLookup, you do not need to replace the code. Yeah, but then you cannot easily use the classic DLookup then. I like to make it so you can still use both. So it is very easy to do a global search and replace to change DLookup to MyDLookup. Then if you do want to use the old DLookup, you still can, but there your way goes - you put them together however you want. But yeah, you are right.
Joanne, thank you very much for the tip, I appreciate that, and everybody else, you should follow along with Joanne's lead. Hit that tip button, or whatever it is called, super thanks, or likes, or I do not know, whatever it is. It would be like if we were sitting at the bar and you sent me a drink. Coffee bar. Coffee bar.
Timothy A. thought, some states are covered by many sales reps. How would you manage the many-to-many relationship if one or more sales reps covered multiple states? Just like that - you would set up a many-to-many relationship. You would have a junction table, you would have a list of states, you would have a list of sales reps, and then you just match them up with who covered what. You are probably going to want to have some means to decide who has what territories inside that state, maybe by county or whatever, I do not know, but is it possible? Yes, definitely possible - just set it up with a typical many-to-many relationship. There you go. There is a video.
Gifted Logical is talking about my Faster DLookup video, and he says he has been preferring recordsets over SQL for a while due to their speed and easier syntax. Yeah, I started using recordsets myself first before I really got into SQL because I am coming from a BASIC / Visual Basic / C programming background, and so that is where I learned to loop through stuff - whether it is items in an array or records in a table. I learned recordsets first - that iterative looping. But then I came to learn SQL and SQL really is a lot faster than using recordsets for most things, especially when you are dealing with database servers.
If you have got Access connected to an SQL server, it is much, much faster to have the server run some SQL than it is to try to loop through records locally in a recordset. So keep both in your toolbox. But what I generally do is I will write a recordset to get something working, and then once it works, then I will try to see, okay, is this something I can do with SQL instead of a slow iterative recordset? So yeah, I still think in recordsets too, but SQL is definitely faster 99 percent of the time.
All right folks, that is going to do it for today. It is a short one. Like I said, I am medicated today, so I am going to go, I am going to lay down and rest my jaw, and we will see you Monday for the next regular TechHelp video.
But that is it. That is Quick Queries for today. Hope you learned something. Live long and prosper my friends. Have a good weekend. I will see you Monday.
TOPICS:
Screen capturing a PDF form for use in Access
Importing an image of a form into an Access report
Overlaying text boxes on a form image in Access
Matching the layout of printed forms in Access reports
Using SelStart and SelLength to control cursor position in text fields
Choosing between Enter and Got Focus events for field focus behavior
Setting Access to use Overlapping Windows instead of Tabbed Documents
Changing window display preferences under Access Options
Running action queries with RunSQL and Execute methods
Understanding action versus non-action (select) queries with Execute
Naming custom functions to avoid conflicts with built-in functions
Setting up a many-to-many relationship for sales reps and states
Creating a junction table to manage many-to-many relationships
Comparing the speed and use cases of SQL vs recordsets in Access
COMMERCIAL:
In today's video, we're answering your questions from the week in Quick Queries number 49. We'll talk about getting information from PDFs into Access, using overlapping windows instead of tabbed documents, setting event handlers with Got Focus or Enter events, and the difference between RunSQL, Execute, and select queries. You'll hear about naming custom functions, tips for managing many-to-many relationships like sales reps covering multiple states, and whether recordsets or SQL are better for different tasks. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main method discussed for importing a PDF form into Microsoft Access when you cannot directly read PDF data?
A. Use a screen capture of the PDF and insert into a report
B. Use a paid Acrobat plugin to import the PDF directly
C. Convert the PDF to a text file and import it
D. Use Access to automatically parse all PDF fields
Q2. According to the video, when filling in PDF forms with Access, what tool is needed to automate saving filled-in PDFs without relying on SendKeys?
A. Free Adobe Reader
B. Full paid version of Acrobat
C. Microsoft Edge web browser
D. Google Chrome PDF viewer
Q3. In Access, why might you create a report with an image of a form and overlay text boxes, as described in the video?
A. To allow data entry on an exact replica of a form
B. To automatically extract all text from a scanned form
C. To enable editing of the original PDF directly
D. To reduce the file size of the database
Q4. What is the benefit of using spaced repetition, as discussed in the video?
A. It makes the database interface more user-friendly
B. It reinforces learning by repeating topics over time
C. It increases the maximum size of Access tables
D. It automatically generates Access forms
Q5. According to the video, what is the primary difference between the "overlapping windows" and "tabbed document" interface in Access?
A. Overlapping windows allow viewing multiple open objects side by side
B. Tabbed documents are required for all database types
C. Overlapping windows only work for forms, not tables
D. Tabbed interface prevents saving changes
Q6. Where can you change the Access setting from tabbed documents to overlapping windows?
A. Options > Current Database
B. File > Export
C. Tools > Advanced
D. Edit > Preferences
Q7. What is required if you want to use both a custom DLookup function and Access's built-in DLookup at the same time?
A. Name your function uniquely, such as MyDLookup
B. Replace the VBA library
C. Only use macros instead of functions
D. Use DLookup2 as a backup
Q8. What is the correct way to manage sales reps covering multiple states and states covered by multiple reps in a relational database?
A. Create a many-to-many relationship using a junction table
B. Put all state-rep pairs in a single flat table
C. Use only lookup fields in the sales reps table
D. Assign each state to only one rep
Q9. Why might someone prefer recordsets over SQL when manipulating data in Access, especially initially?
A. Recordsets are familiar to those from a programming background
B. Recordsets are always faster than SQL
C. SQL cannot be used in Access at all
D. Recordsets automatically generate forms
Q10. According to the video, when working with Access connected to SQL Server, which method is generally faster for data operations?
A. Running SQL queries on the server
B. Looping through local recordsets
C. Exporting data to Excel first
D. Using only macros for data changes
Q11. If a user wants the cursor to go to the end of the text box on focus in Access, which event could be useful to handle this according to the video?
A. Enter or Got Focus event
B. On Load event
C. After Update event
D. On Error event
Q12. What is a main reason the presenter disapproves of the "tabbed document" interface in Access?
A. It restricts arranging multiple objects side by side
B. It does not allow saving files
C. It prevents editing reports
D. It uses more system resources
Q13. Why does the presenter repeat certain lessons multiple times in his courses?
A. To enhance student learning through spaced repetition
B. To artificially extend course length
C. Due to lack of new content
D. To meet video length requirements
Q14. What is a benefit mentioned about learning by teaching others?
A. Knowledge is reinforced and retained better
B. You do not have to practice the skills yourself
C. It replaces the need for any repetition
D. It makes Access automatically update your skills
Answers: 1-A; 2-B; 3-A; 4-B; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I am addressing a new round of questions that have come in from viewers throughout the week. These questions come from all sorts of places - YouTube comments, emails, my forums, and my website. I always gather them up into one session where I do my best to answer each as thoroughly as possible.
To start off, there was a question about reversing the process I showed in my Fill In PDF video. That video demonstrates how to use Access to fill out a PDF form automatically, opening it in your web browser, filling the fields, and saving it using SendKeys. While it is possible to automate PDFs more elegantly, those methods usually require the full paid version of Adobe Acrobat, which I do not use or generally need. In my experience, I have always managed my PDF tasks straight from Access by creating a replica of the form in Access, then exporting it as a PDF. If you want to go in the other direction and get a PDF into Access, your best bet is to use a screen capture of the PDF and import that image into a report. I walk through this approach in my Print Documents video, and it works, although it might not be the most sophisticated solution. I have done this for clients who needed to duplicate forms exactly, even matching barcodes, and it worked just fine, lasting for years.
The next question came from someone who commented on whether I follow through on my promise to show "more cool stuff tomorrow." My track record is pretty good; if I say I will cover something in the next video, I almost always do unless something unforeseen comes up. For example, I am recording this session after some dental surgery, so sometimes things can delay me, but I try my best to deliver as promised.
Moving on to a technical question about whether it is better to use the Enter or Got Focus events for moving the cursor to the end of a text field in Access. Either method will work. I personally use the mouse often, but if you are tabbing between fields with the keyboard, handling this in Got Focus works well. It really comes down to your work style.
A viewer reflected on the learning process, noting how it is easy to think you have mastered something after watching a video or reading a book only to forget the details when you try it yourself. I completely agree. Repetition is key. Even when people complain that I go over things multiple times in my videos, it is intentional. Spaced repetition solidifies concepts, so you will see key topics revisited multiple times across my lessons. Teaching what you just learned is also a great way to reinforce knowledge.
Another person asked about how to set up Access with overlapping windows so you can have multiple tables or forms open and visible at the same time, rather than being stuck with the default tabbed interface. This is just a setting in Access. You can change it under Options, then Current Database, and select Overlapping Windows instead of Tabbed Documents. After restarting the database, you can move windows around as you like. I have a free video for beginners where I explain this, and I encourage even experienced Access users to watch my free Beginner 1 class, since everyone tends to pick up something useful.
Mark Jackson's question was about whether the Execute command in Access works with select queries. The answer is no. Both RunSQL and Execute only work with action queries - those that insert, update, delete, or create tables. To use a select query, you need to use it as the RowSource or RecordSource for another object, or as a stored query.
Riel Ramirez brought up naming functions, suggesting that if you name a function DLookup instead of MyDLookup, you will not need to update existing code. That is one approach, but if you do that, you lose easy access to the regular DLookup. I prefer using unique names like MyDLookup so I can quickly swap them in or revert as necessary. It is up to your personal coding style.
There was a note of thanks from Joanne, along with a reminder to support the channel or say thanks if you found the content helpful. I appreciate the support, whether it is a tip, thank you, or just a like.
Timothy A. brought up managing a many-to-many relationship, for example when several sales reps cover multiple states. The classic solution is to use a junction table that links your sales reps and your states. From there, you can expand to handle territories within a state as needed - for example, down to the county level. It is a standard setup in relational database design.
One viewer talked about the speed of recordsets compared to SQL, stating a preference for recordsets due to their syntax. I started out the same way myself, coming from a background in BASIC, Visual Basic, and C. Iterating with recordsets was my first method for data handling, but once I learned SQL, I realized it is generally much faster, especially when working with data on a server. I still use recordsets at times to get code working, but then I try to convert it to SQL for performance.
That covers all the questions for today. As I mentioned, it is a shorter session this time because I need some recovery time, but I will be back Monday with another regular TechHelp video.
For a full video tutorial with detailed, step-by-step instruction on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Screen capturing a PDF form for use in Access
Importing an image of a form into an Access report
Overlaying text boxes on a form image in Access
Matching the layout of printed forms in Access reports
Using SelStart and SelLength to control cursor position in text fields
Choosing between Enter and Got Focus events for field focus behavior
Setting Access to use Overlapping Windows instead of Tabbed Documents
Changing window display preferences under Access Options
Running action queries with RunSQL and Execute methods
Understanding action versus non-action (select) queries with Execute
Naming custom functions to avoid conflicts with built-in functions
Setting up a many-to-many relationship for sales reps and states
Creating a junction table to manage many-to-many relationships
Comparing the speed and use cases of SQL vs recordsets in Access |
