Quick Queries #38
By Richard Rost
7 months ago
Fix Parameter Prompt, Reference Subform, Import PDF
In this Microsoft Access tutorial I will show you how to use the correct subform syntax, discuss important troubleshooting steps like closing and reopening objects after code changes, give advice on finding Access development work, answer questions about importing PDF data using ChatGPT, explain handling message box titles and attachments, clarify auto number use, suggest word substitution techniques in long text fields, and respond to various viewer questions.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, subform syntax, referencing subform fields, forms collection, VBA changes not updating, force form reload, Access consulting advice, PDF to CSV extraction, using ChatGPT for data extraction, CSV import routine, sum function mistakes, parameter value error, field name spelling error, finding YouTube links on mobile, Access attachments strategy, global message box title, sequential numbering, resetting autonumber, character substitution logic, whole word replacement in VBA
Transcript
Quick Queries are answers to queries about Microsoft Access, and they could be about queries or not. So let's see what we got today.
First up is the good old subform syntax for the name of a subform, and this one throws a lot of people. I'll be honest, it threw me too when I was first learning Access. It took me a long time to remember this format. If you're in a form and you say StarshipF, which is your subform name, dot Form, bang StarshipID, then it works.
Below, I've got this with the full name of their forms: form name, field name. So, it's just a matter of you have to remember this is the syntax. If you're in the form, let's say you're in the order form and you're talking about your order details subform, it'd be Order Details Subform dot Form bang FieldName, like FirstName, OrderID, whatever you want.
You can also always refer to it from the top, and from the top is from the very top, the Forms collection. Forms, OrderForm, OrderSubform dot Form. This is the tricky part here that always gets people: dot Form, then bang FieldName. You can always start from the top and just start with Forms.
Here are a couple of other videos that will help you out more with this: how to get a value from a subform, and this one, which is just how to get the value from an open form. You can see right on this page, I've got the syntax right here. Single form is that. Subform is that. It's a bit longer, but again, the dot Form is the part that usually throws most people. That is the part I had the most trouble with.
Next up, this one doesn't have anything to do with the problem that Ryan here is having, but it has to do with the fact that the problem was caused because he wasn't closing the table and reopening it. This will happen with forms and it sometimes happens with queries.
Whenever you make any changes to something, especially VBA changes in a form or a report, close it and then reopen it. You want to save it, close it, and then open it. Like I say in a lot of my videos, there's a reason why I say silly things like that and repeat them all the time: because they work. I don't know how many hours I've lost and how many hairs I pulled out of my head because I would make a VBA change and then switch right back over to the form and then click the button and nothing happened. I'd go back to the editor, make some changes, come back to the form, and it still wouldn't work.
You have to shut the form down completely and then reload it. Give the events a chance to run all fresh and it fixes so many problems. If that doesn't help, close the database and reopen it and see if it works then.
Definitely, whenever you make any VBA changes, even if it's something tiny and you don't think it's a big deal, save it, close it, open it, and it tends to work. If that doesn't work, then you post in the forums and say you have a problem.
Next up is a question from Jerry. He says he is having a hard time finding any job opportunities for Access developers in Canada. I replied with, you don't really see a lot of companies posting job wanted specifically for Access developers. The way you are going to get work as an Access developer is to go out on your own as a consultant. Hang your shingle and look for work that way. That's what I did.
You don't see a lot of companies looking specifically for Access developers, mostly because they don't know that Access is a good tool that will do what they want. A lot of small businesses and mid-sized businesses can definitely use an Access database. They know that they have a need for a database solution; they just don't know that Access is the right one for them.
I have a whole page where I talk about advice for Access consultants if you are thinking about becoming one or if you are one and you want some of my advice from 30 years of doing this stuff. Before I was a teacher, I was a full-time Access developer too. I also have a video that I did about Access jobs, so check those out.
Here is another question from Jim. He says, can VBA be used to create a PDF to Excel conversion program? Whether it's Excel or you want to get the data into Access, it doesn't really matter. The answer is yes. Generally, if you can get it especially as a text format, then you can very easily create an import routine.
But honestly, this is one of those things that I think is a really good job for ChatGPT. It can take a PDF and you just basically paste it in there and say, can you extract the data from this PDF and give it to me in a CSV format, and it will just do it.
While I'm all about teaching people how to do things - I mean, that's what I do - I love teaching people how to programmatically write stuff and generate VB code. Sometimes some things are just easier: just drop it in GPT and say, extract this for me or convert this for me or whatever.
I will honestly do this a lot with things like I write a lot of Python scripts, but for some of the more elaborate ones, I know how to do it in VBA, so I just kind of pseudo-code it in VBA and say, I am trying to do this in Python or in JavaScript or in some other language that I need but am not an expert with. GPT is great at converting one thing to another and converting PDFs to usable data. That's one thing where GPT works really well.
Here, for example, here is a sales document I've got. Just like this, I just dropped the spreadsheet inside of a Word document and made a PDF out of it. Pretty straightforward, pretty simple. Here's GPT. I am just going to take this guy; we are going to click, drag, and drop. Now it has the PDF. I am just going to say, please extract - yes, I always say please - please extract the start data from this PDF and let me have it in CSV plain text format. Hit go. That is going to do its thing, it's going to read the document, and there is your data. No crazy scripts, no code to write, it just does it.
So, that's one of those things where if you just want a quick, fast, simple solution, just drop it in GPT and it works great. It will also cut through all the mess too. If you look at the original, I intentionally put some nonrelevant stuff in there, and it cut through all that and just got the chart data out of the center like I asked for. That was really nice, too. Pretty handy tool.
Next up, this is a comment on one of my Excel videos, but Min says, everyone's Sum in Access will be a great video. I already made it a while back. It's right here. Always watch my videos to the end, even if you don't want to watch all the stuff at the end. I always mention if there are other related videos at the end of a video.
Sunny says, watching my search button tutorial, they don't know why, but it requires them to enter the search value, then a parameter value, then the final result. It seems to only happen once. Generally, this happens if you have something spelled wrong. Anytime you see an "Enter Parameter Value" pop up and you're not expecting it, chances are something is spelled wrong. So, even though it's working, it might be getting the value from the right field. You might have another thing in there that's spelled incorrectly. Double-check all your field spellings and go see this video for more information.
Next up, this is a comment on my "Where Are the Links" video. It's this video where I show you how to find the links that I mention below the video window on YouTube, which YouTube does a pretty good job of hiding for you so you can't easily find them. But Trubor wants to know, how do you do this on a tablet?
My tablet isn't handy, but I can show you on my phone because the Android YouTube app is pretty much the same on both the tablet and on your phone. They don't make it easy to find. Here it is on my phone: I just played one of my videos, and you can see right under here is a little "more" link. It's right below the title, it says "more," right there. Click on that, and it will expand a little bit more into a bigger window, but then you still have to click "more" again to see the whole thing. Now you will see all the links and "learn more" and the other stuff that I talk about in the video. YouTube, you have to make this easier for people to find. It's so tucked away even I had a hard time finding it.
Easy Speak wants to know if I see comments on past videos. New ones that come in, I try to at least read them as they come in. Every couple of days I make sure I check them and I approve them. I have to approve all of them. So new ones that come in now, I definitely read them as they come in. I try to reply to them as best I can. If they're good ones, I put them in a Quick Queries video.
But there were a few years going back where I didn't check comments at all. I was really busy. So you might see some comments from five, six, or ten years ago that still have no replies on them. I am slowly going back through the old ones. Once in a while you will see me do a Retro Quick Queries where I answer ones from seven, eight, or ten years ago. Those are always fun to get to.
If it's a new one, I am on the ball. I check comments at least once a week. I don't always have time to reply to them. Sometimes I just give you a thumbs up, but I have read it.
This user mentioned something interesting. In my attachments video, I talked about not putting attachments in your database because it leads to database bloat. This person had a great idea. We had to use attachment once and I created a separate ACCDB file to hold them. That's not a bad idea. If you do want to use attachments, put them in a separate database and then link to that table. You can still work with the attachments, but you're not going to bloat your primary database. So, that's definitely a good solution.
I still don't like storing attachments inside a database because as far as Access databases go, you are better off just putting the attachments in the file system. But if you absolutely have to do it, that's not a bad idea.
Clik wants to know if it's possible to change all the titles of your message boxes in Access at once. If they're all the same thing, you could do a global search and replace in your VBA code. In the future, if you want to have them all the same thing, you can store them in a global variable. So, is it possible? Yes. It depends on how you want to set it up and how you have it set up.
If you don't currently have any titles in them, and you just have the prompt and then maybe the type - like is it just Yes/No, Yes/No/Cancel, and you didn't specify a title - that might be a little trickier to do a search and replace for if there are no titles currently. You might have to do them by hand. But once you do that, if you want to have them all the same thing, then I would use a global variable or a constant.
Shadow Dragon seems to think that the Microsoft Access team has an SQL Server mole buried in them trying to get people to intentionally inflate their Access database to pass the two gigabyte limit. Is it possible? Hmm. I don't know.Theo says he watched my reset database video and he wants to know how he can reset the auto number to something specific. That's what I'm gathering here.
I show you how to delete all the data and then, by compacting and repairing the database, it will bring all the auto numbers back to one. He wants to know if you can increment it to a number like 105.
Yes, it's possible. You could just add a bunch of blank records and then delete them, and the auto number will be set to whatever you want. But I have to remind you, auto numbers are not for you. You should not care what they are. They could be set to any random number. It would not matter. They're really only for internal database use, for relationships, for making sure records are unique, that kind of stuff.
If you want some kind of number that you control, make your own counter variable. In a video like this, I teach you how to make your own sequential numbering. If you keep track of the order numbers, for example, and you want them to be sequential, this is what you do. Don't use an auto number for that.
There is also a trick where you can replace an auto number if you need to, if you accidentally deleted it, and that has to do with an append query. Watch this if you need to learn how to do that. But again, auto numbers are not for you. You shouldn't care what they are.
Giovanni brings up a good point in my character word substitutions video where I teach you how to take a long text field and substitute things like characters that have umlauts over them with regular lowercase character ones. I mentioned you could also probably use it for word substitution, although that was kind of an unintended side effect. Then I showed how you could do NASA.
But Giovanni is right. If you get the word "nasal," it will replace it with NASA. So you need a little more logic in there to determine if it's a whole word or not. I would envision checking for a space in front of it, or if there's nothing in front of it, like if there's a previous sentence and there's a period or something, you have to look for delimiters around the word. So it would be a little more complicated, but it's definitely doable. Or just check to make sure there isn't another letter on either side of the word. If anything to the left of "NASA" is a non-letter, or to the right at the end of "NASA" is a non-letter, then the substitution is okay. So it could be a little more complicated, but it's definitely doable. Thanks for the heads up. I didn't think of that.
All right folks, that's about going to do it for another Quick Queries. Hope you learned something. Enjoy your weekend. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Access subform syntax for referencing fields
Referencing subform controls using dot Form syntax
Referencing controls starting from the Forms collection
Importance of closing and reopening forms after VBA changes
Saving changes and reloading Access forms to trigger updates
Advice on finding Access developer work as a consultant
Using VBA to import data extracted from PDFs
Using ChatGPT to convert PDFs to CSV format
Resolving Enter Parameter Value popups in Access searches
How to access video description links on YouTube mobile app
Preventing database bloat with attachments by using a separate database
Changing all message box titles in Access using global variables
Resetting and incrementing Access auto numbers
Creating sequential numbers with a custom counter field
Replacing Access auto numbers using append queries
Whole word text substitution in Access with delimiters
Improving word substitution logic for accurate replacements
COMMERCIAL:
In today's video, we're discussing some common Microsoft Access questions in this Quick Queries episode. We'll talk about the correct syntax for referencing subform fields, why you should always close and reopen forms after making changes, and the real-world prospects for Access developer jobs in Canada. We'll discuss using VBA to help with PDF to Excel conversions, troubleshooting those mysterious parameter pop-ups, and the best strategies for handling attachments and message box titles in your databases. You will learn helpful tips for finding video links on mobile devices and tricks for resetting and managing auto numbers. Plus, we will touch on the challenges of character and word substitutions in long text fields. 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 correct syntax to reference a field on a subform from within the main form in Microsoft Access?
A. SubformName dot FieldName
B. SubformName bang Form dot FieldName
C. SubformName dot Form bang FieldName
D. SubformName dash FieldName
Q2. When referencing a field on a subform from the top-level Forms collection, what extra part of the syntax must you remember?
A. You must always use square brackets around field names
B. You do not need any extra syntax
C. You must include dot Form before referencing the field
D. You must use parentheses
Q3. After making changes to VBA code in a form or report, what is the best practice before testing it?
A. Compile the code manually
B. Close and reopen the form or report
C. Switch between design and form view
D. Only save the object
Q4. What is often the cause of an "Enter Parameter Value" prompt unexpectedly appearing in Access?
A. Macro security settings
B. An invalid database format
C. A misspelled field or control name
D. Too many records in the table
Q5. What is a good way to extract table-like data from a PDF and convert it to CSV if you want a quick and easy solution?
A. Use VBA code exclusively
B. Export directly from Adobe Reader
C. Paste the PDF into ChatGPT and ask it to extract the data
D. Print the PDF and type the data manually
Q6. According to the video, what is typically the best way to find database-related jobs as an Access developer?
A. Search for "Access Developer" openings on large tech job boards
B. Apply for internships at large corporations
C. Offer consulting services directly to small and mid-sized businesses
D. Focus exclusively on government job postings
Q7. If you must store file attachments related to your Access data, what is one recommended approach to minimize database bloat?
A. Store attachments as fields in your main table
B. Use external file storage with file paths stored in the database
C. Create a separate ACCDB file for attachments and link to it
D. Convert all files to plain text and add them as memo fields
Q8. What method is suggested in the video for changing all message box titles at once in your Access application?
A. There is no way to do this
B. Use a global variable or constant for the title text in VBA
C. Rebuild the message boxes from scratch
D. Only change them manually one by one
Q9. What is the recommended approach if you want a sequential number controlled by you for records in Access?
A. Rely solely on auto number fields
B. Use a counter variable or your own sequential numbering logic
C. Manually enter numbers for each record
D. Use the primary key by default
Q10. Why are auto number fields generally not recommended for use as user-facing sequential numbers?
A. They produce numbers too slowly
B. Their values can be random and are intended for internal use only
C. They are always negative numbers
D. They cannot be indexed
Q11. In the video, what is the key point when performing word substitutions in long text fields in Access?
A. Substitute only uppercase letters
B. Substitute words regardless of surrounding characters
C. Make sure substitutions match whole words by checking characters around the word
D. Substitute all vowels first
Q12. How can you view the "more" section with links and extra information below a YouTube video on a tablet or phone?
A. It is not possible to view this section on mobile devices
B. Click the video thumbnail again
C. Tap the "more" link below the video title, then expand to see the rest
D. Use the browser's back button
Q13. What is a possible side effect of not closing and reopening an object after making VBA changes in Access?
A. The changes might be lost
B. The changes may not take effect until the object is reloaded
C. You may get an unhandled exception error
D. Access will crash
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-C; 7-C; 8-B; 9-B; 10-B; 11-C; 12-C; 13-B
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, I'm answering a series of questions about Microsoft Access and related programming topics. Quick Queries are short answers to questions from viewers, and they can cover a wide range of subjects – not just queries themselves.
The first topic is about the correct syntax to reference a subform and its controls, which often confuses people new to Access. When you want to refer to a control inside a subform from the main form, you must remember the specific format: start with the subform control's name, then add ".Form", and finally use an exclamation mark to reference the control you want. For example, if your subform is called Order Details Subform and you want to access a control named OrderID, you would use Order Details Subform.Form!OrderID. You can also go from the very top using the Forms collection, such as Forms!OrderForm!OrderDetailsSubform.Form!FieldName. The ".Form" portion is the key part that many people overlook, and it caused me trouble when I was learning Access myself. If you need more guidance, I have tutorials on how to get values from subforms and from open forms, and right on my website you will find the specific syntax for single forms and subforms.
Another issue that can trip you up in Access is failing to close and reopen objects like tables, forms, or queries after making changes. Especially when modifying code in VBA modules, it's essential to save, close, and reopen the object before testing new changes. Skipping this process can result in seemingly unchanged behavior and hours of frustration. When you make any adjustment in VBA, no matter how minor, always save and reopen. If that still doesn't fix your issue, close and reopen the entire database.
After that, I address a question about Access job opportunities, specifically in Canada. The reality is that you will rarely see companies advertising specifically for Access developers. Most businesses do not realize Access can solve their database problems. The way most Access developers find work is through consulting. Set yourself up as a consultant, promote your services, and find clients who need custom database solutions. On my site, I offer lots of advice for aspiring consultants, based on decades of experience, as well as a video discussing Access jobs.
Another viewer asked if it is possible to build a VBA solution to convert PDF files to Excel or Access tables. The answer is yes, as long as you can extract the content as text or in a structured table format, you can write an import routine. However, for fast and simple conversions, tools like ChatGPT can be extremely effective. You can copy and paste the content of a PDF or upload the file, ask for the data to be extracted in CSV format, and get a usable result instantly. I demonstrate this by dropping a sample PDF into GPT and requesting the tabular data in CSV format. It neatly extracts the relevant data and ignores nonessential information. This approach can often be quicker and less prone to errors than building custom scripts.
Someone commented on a past Excel video that a tutorial on the Sum function in Access would be useful. I have already created that video and recommend always watching to the end because I mention related topics that can lead you to other helpful resources.
Another common problem is when Access presents an "Enter Parameter Value" dialog unexpectedly during a search. This usually happens when a field name or a control is misspelled somewhere in your code or query. Even if your process seems to work, double-check all the names to ensure nothing is amiss.
A viewer wanted to know how to find video links mentioned on my YouTube videos when using a tablet. On mobile devices, including both phones and tablets, the YouTube app makes finding these links tricky. Look for a small "more" link below the video title; tapping this will expand the description and reveal all relevant links. It can be a bit buried, but with this approach, you can always uncover the links you need.
Some viewers ask whether I see comments on old videos. I do monitor new comments and try to read and approve them every few days. While I may not always have time to reply to every comment, especially on older videos, I am working back through comments from previous years, and occasionally I make Retro Quick Queries videos based on those. Rest assured that current questions are being read and, if possible, answered.
One suggestion I received was about storing attachments outside the main Access database to prevent bloat. Someone shared the idea of creating a separate database solely for the attachments, then linking to it from the primary database. While I still prefer to store attachments in the file system rather than inside a database file, this separate database method is a solid alternative if you must use attachments.
Another question concerns updating message box titles across an entire Access database project. If you want all your message boxes to have the same title, you can use the global search and replace feature in your VBA editor. For the future, you can use a global variable or a constant to standardize these titles. If you did not specify any titles previously, making the update will be a bit more manual, but after the first round of updates, using a variable makes things easier.
A commenter speculated humorously that someone on the Access development team is trying to force users onto SQL Server by causing database bloat, but I have no evidence of that.
Another question was about resetting the auto number field in a table to a particular value. Normally, Access will reset AutoNumber fields to 1 when you delete all the records and compact the database. If you want to set the next number to, for example, 105, you can add empty records up to that value and then delete them. However, I always advise that AutoNumbers are not intended for users and should only be used by the database for record identification. If you need a user-facing sequential number, create your own counter system rather than relying on AutoNumber. If you accidentally delete an AutoNumber field and need to restore it, you can use an append query. I have tutorials on both sequential numbering and fixing lost AutoNumbers.
There's a discussion about the need for more sophisticated character and word substitution in long text fields. When replacing words, you have to be careful to match whole words rather than parts of words, or you risk accidental changes. For instance, substituting "NASA" might accidentally change the word "nasal." You can build logic to check the characters on either side of your target word to make sure they are not letters. This makes the substitution logic more robust, but it is definitely a doable task.
That wraps up this Quick Queries session. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Access subform syntax for referencing fields
Referencing subform controls using dot Form syntax
Referencing controls starting from the Forms collection
Importance of closing and reopening forms after VBA changes
Saving changes and reloading Access forms to trigger updates
Advice on finding Access developer work as a consultant
Using VBA to import data extracted from PDFs
Using ChatGPT to convert PDFs to CSV format
Resolving Enter Parameter Value popups in Access searches
How to access video description links on YouTube mobile app
Preventing database bloat with attachments by using a separate database
Changing all message box titles in Access using global variables
Resetting and incrementing Access auto numbers
Creating sequential numbers with a custom counter field
Replacing Access auto numbers using append queries
Whole word text substitution in Access with delimiters
Improving word substitution logic for accurate replacements
|
