Quick Queries #13
By Richard Rost
2 years ago
IIF not IFF. Toggle Design View & VBA Window. More!
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- IIF not IFF
- Toggle Between Design View and VBA Window
- VBA Editor Code Turns Red, Loses Spacing
- Change Height of Text Box in Continuous Form
- SQL or Recordset Loop
- Application Title, Form Caption Centered or Left Aligned
- Combo Box Shows ID Instead of Name
- Web Browser Control shows Animated GIFs
- Is it better to use Excel or Access for budgeting?
- Selling Your Database. Monthly Subscriptions.
- 2 GB File Limitation, Multiple Back-End Databases
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq
Intro In this video, we answer a variety of Microsoft Access questions submitted by users, including topics like the correct use of the IIF function, shortcut keys for toggling between Design View and the VBA editor, dealing with timer event issues in the VBA editor, dynamic text box sizing in continuous forms, choosing between SQL and record set loops for updating data, combo box display settings, considerations for choosing between Microsoft Access and Excel for budgeting, securing distributed Access databases, changing form background colors, and understanding the two-gigabyte file size limit in Access.Transcript It's that time again, folks. Time for another Quick Queries. Number 13. I think I'm going to make this a regular weekly thing because I get so many emails and questions, and I've got buckets and buckets filled with questions. And a lot of them are great questions, but they don't really need a whole video.
But let's take a look at what we got in the bin this week. First up today is a forum post in my Access Expert 9 class. One of my students, Matthew, asked a question. He couldn't get something to work right, went through a bunch of screenshots, and it turns out the solution was that he was using IFF instead of IIF. IIF.
And when did Edge start popping up this little thingy here? That's kind of weird.
Anyway, this is more of a public service announcement than a question. But I see this all the time. This happens a lot. I used to do it myself when I first got started. So that's why in my videos, when talking about the IF function, I always say IIF when I'm teaching it in my videos because I used to do this myself all the time.
I used to put IFF instead of IIF. It stands for Immediate IF. That's what IIF is. So just keep that in the back of your brains when you're working with it. And if you have no idea what I'm talking about, there's a video for you. It's basically an IF-THEN statement inside of a function. So you could say: if this, then the value's that; otherwise, the value's that.
Next, UpdateOne of my Gold Members asks, "Is there a way... Is there a shortcut key to toggle between Design View and the VBA window?" And one of my moderators, Kevin, posted ALT+F11. That's a nice easy way to switch between the two. I run into this a lot myself, and I didn't even know that, Kevin. But, yeah, if you're in, even a form, you get ALT+F11 the first time, it'll bring up the VBA editor. I'll just slide it over here. And then you can just use ALT+F11 to jump between them. See? Pretty cool.
I'll probably forget that, but if you like shortcut keys, there you go.
Here's another question from the forums from one of my Gold Members, Massey. He says that the code turns red and he loses spacing between the words whenever he's doing editing in his VBA editor. He says, sometimes the cursor jumps backwards, all that stuff. And again, Kevin to the rescue. He says you've got a timer event running somewhere. I see this a lot.
And I'm going to keep repeating the stuff in these videos because the questions keep coming in. It's the same questions over and over again a lot of times. But if you've got any form in your database open with a timer event, you're going to see this kind of behavior. So you've got to shut down any timers you've got going on.
If I'm in my main menu, go to design view, if I put a timer, let's put a timer interval on here, let's say just in the background it's doing something, let's say DIM X as a Long, and then X equals X plus or X equals whatever, just anything, any timer event running, right? Save it, close that, now open this form back up, now that timer event's going to kick off every three seconds, okay, and if you're working on a different form, let's say you're in the Customer Form, right, Design View, date coming over here and doing some more editing, comma, do this blah blah blah blah blah, right, there. I didn't touch it, and when that event ran, it just took over, and now this line is in red. I change my colors, obviously, that's because that timer event is messing things up. And I come in here, and I want to change this, get rid of that thing, see? It puts it on the end there. Or if I'm going to say, this is some text, and then comma, see? It's grabbing the focus every three seconds. Even if it's not doing anything visibly in the database, if it's a background process or something that runs, it's going to cause a problem for you, so the key is: make sure if you're doing editing, shut that form down, and I didn't want to make changes.
Next up, a question from one of my Gold Members, Eduardo. He asks if it's possible to dynamically change the height of a text box in a continuous form. You cannot. If you change the properties of one object, you change them all. So a text box, let's say "First Name," alright, you've got a bunch of them on that continuous form, but they're all "First Name." And so if you change one of them, it's going to change all of them. You can change things like conditional formatting; so some of the boxes might appear green, some red, based on the contents, but you can't change the actual properties of the object itself without changing all of them.
Next up, in the Access Developer Forum on my website, Gregory, one of my students, asked if there is a preferred method for changing a bunch of data. Because you can either use an SQL statement, or you can use a record set loop. And there's other methods too, but these are the two biggies. And we actually had a pretty good conversation here on the website about it. You can read. I'll put the link to this down below.
But, as I mentioned here, I started programming in BASIC on my old TRS-80, my CoCo, when I was about eight years old. And so my brain tends to think in loops, like a FOR NEXT loop or a WHILE loop. And so that's the first way that I learned to program, is to iterate through things. It wasn't until I was in my twenties until I learned SQL. Now, SQL does things differently. You just send a command to the server, and the server basically does all that stuff in the background, and you don't have to think in loops anymore. You can say, you know, update this field, from this table where the customer is male, or whatever. So you don't have to think in loops.
Now, generally, SQL statements are faster, especially if you're using a database server like SQL Server. And as long as you can represent what you're trying to do in simple SQL, then I suggest using SQL. The only time I usually use a record set is if the conditioning, what you have to do to the data, is different depending on what's in that record. You know, so you can loop through the record and say okay this guy's got this condition and this condition and this condition so do that. It's not just one blanket thing that happens to all the records; if there are different conditions based on the data in the table, and you're going to process it differently, then use a record set loop.
So if you want to read more about this stuff, it's right here. I'll put a link down below. I'll head over to the YouTube. I say this every time I do one of these videos. I don't check YouTube comments anywhere near as often as I check the comments on my website. So if you have a question you want to post, you can go to my website. You'll probably get a faster response. I got to my YouTube comments maybe once a week, if I have time.
This user, CD4 whatever that is, wants to know if he can change the title bar of the forum, probably the form, to center, and how can you do it. This is something you really can't control. What he's talking about is this caption here, right, in the forms and in Access, the application itself. In some versions of Access, this will be centered on the form, and on Access, and in some versions, it will be left aligned and I don't know the rhyme or reason, and it was different from Windows 7 to Windows 8, Windows 10, Windows 11, so I'm, there's no way to control it and um, it's based on what version of Windows and Office you have so, I tried looking online and Google searched myself to see if I could figure out exactly what's what with that but I couldn't find any definitive information on it
So don't worry about it. The best I can tell you is don't worry about it.
OK, here's one I see a lot. People say that I set up a combo box and I see a number, 123, seeing the ID field instead of the name of the state. Obviously, this person's trying to set up a customer table. What kind of costumes? Halloween costumes? No, I'm just kidding. I'm just kidding. I see that a lot by the way. I pick on people all the time in the forums for that. Instead of customer they put costumer.
But yeah, basically you don't have your combo box set up correctly. That first column is supposed to be hidden and then the second column that actually has the name of whatever you're trying to have in it will display the value that you want the user to see. But that first column, column zero, right, that has that ID. That's the actual bound value. That's getting saved to the table. And run through my Relational Combo Boxes video, and that will explain that. If you're seeing an ID and you're not expecting to, then you've got your combo box set up wrong. You've got to hide that column. And you'll see that in the wizard when you use it to build your combo box. It says hide key column, yes or no. And you won't see that hide key column if you build your combo box based on a query and not a form.
I actually talk about this topic in a lot more detail in my Access Beginner Level 8 class. So go watch that for more information.
GoneFishin2006, one of the more prolific commenters on my channel, mentioned that in my recent video on images in a subfolder, I said that the image control won't show animated GIFs. And it's GIFs, not GIFs. It's graphic, not giraffe. But that the web browser control does show them. And in fact, I mentioned that when I did a video on the web browser control months ago. So yeah, I'm aware of that. I just think it'd be nice if the Access team added that feature to the image control, too.
So yeah, yeah, I know. I get it. You know how many people mentioned this to me? I figured you guys knew I knew. I just didn't. OK, whatever. And yeah, I try to stay away from ActiveX Controls as much as possible. There are a couple of good ones, but usually, I avoid them, especially if you have a database that you're distributing to other people.
William D., one of my Platinum Members, asks, is it better to use Excel or Access for budgeting? You know, the whole thing about whether it's better to use Access or Excel for something comes down more about the complexity of the task at hand. You can obviously use Excel for budgeting, and you can use Access for budgeting. Invoicing, for example, you can use Excel. You can also use Access. It depends. In my particular case, I look at how much you need interrelated data. That's what Access is good for. If you've got data that a bunch of different sources of data, like different tables, customers, and products, once you start needing relationships, then I definitely move over to Access. But I start a lot of my projects in Excel. Budgeting, for example, I've got my account balances database that I use for you know every day I track my account balances for my checking accounts and credit cards and all that stuff, and that started off in an Excel spreadsheet, and then as it got more complicated, and I realized I want to do more with it and automate things, then I moved it over to Access because it got more complicated. I started having relationships between the different accounts making transfers and that kind of stuff. And also, automation. I think Access is a much, much better tool once you start getting into programming. Yeah, there's VBA in Excel, and there's a lot you can do too, but Access is more of a program interface than Excel is.
So for me, I would always say start with Excel. Excel is a good prototyper for Access, too. So you can put something in Excel and lay it out the way that you want it. And keep in mind, eventually, you're going to move it over to Access once it gets more complicated. And then you can work out the layout and see where the bugs might come in, for example. And then once you've got that good and working in Excel on perfect, then you can start porting it over to Access. And you'll have an idea for what tables you'll need, what fields you need in each table, what types of data you're working with, and so, which is better, it depends on your needs. You might be fine with just a little text file; I don't know. But I would definitely say start off in Excel and move over to Access if it gets complicated to the point where you want to do automation or you want to have multiple tables with accounts and stuff like that. So that's really kind of up to you. You can do it with both.
Ellen, one of my Platinum Members, asks, "I've built a database that I'd like to sell, but I don't want people being able to copy it, read some of my tables, or change the design. Is it possible to bill them for a monthly subscription?"Alright, you've got a lot in this. Let me unpack it slowly here. First of all, as far as them changing the design goes, that's solved by simply making an ACCDE file. Once you do that, it basically scrambles the design of your database so that they can only use the database, like use the forms and reports, and they can't get into your VBA code or your form design. Can it be reverse-engineered? Yes, there are companies out there that can do it, but it's not something that's very simple to do.
Now, as far as being able to copy it, there's not really much you can do about that. Once they have the ACCDE file, they can make copies. They can put it on different machines. Now you can control them registering that software. For example, you might have to make them call you to get a registration code to unlock it. So if they did copy the ACCDE file to another machine that isn't registered, it just won't work. And that's just code you'd have to build into your database. I actually do have a registration seminar that I made years ago where you put this in your database, and it'll pop this up when they run the database. It'll say, you've got to call us to get a password. They'll give you a key. That's their key for it. And then you can give them a password, and it'll unlock the database. So if they do copy it to another machine, they'll have to register it on that machine.
Now as far as reading some of your tables goes, one of the downsides of Access is that the data in tables is not encrypted in any way. So if you have like proprietary data that you release with your database, then if they get into that backend file, they'll be able to look at whatever's in your tables. Your only two options are to use SQL Server Online. So if they run the database, it'll connect to your SQL server, and you can control the data that they have access to there. Or you could encrypt the data in your tables. And again, I have a seminar on that. Basically, you just come up with an encryption scheme, and it'll scramble the data in the tables so that if they do open the tables up, they'll just see gibberish. And they can delete it or change it, but they won't know what it is. They can't see it.
So, if you're saving sensitive information, proprietary information in tables, by encrypting them, then you put the decryption code in your database, and it'll decrypt it on the fly as they open up the records. So that's another possibility. SQL Server is definitely more secure, but this will work with just an Access solution.
Steven asks if it's possible to change the background color of a form while typing details into it. So you can go from blue to orange to green when the form is being completed. Yeah, sure. You can absolutely do this. I do something very similar in my Progress Bar 2 video, where I change the color of the progress bar as it goes across. But instead of changing the progress bar properties or the percentage, just change this property here, Me.Section(0).BackColor, and set it to whatever you want. I'll put a link to this video down below.
Here's another question that comes up a lot. People are worried about the Access file size limitation. It's two gigabytes, but it's per file. So essentially, it's per table. Now, if you've got more than two gigabytes of data that goes into one table, then you have two choices. You can either prune that data'take all the records that you don't necessarily always need and put them in another table, really a kind of archive table'but if you get sales data going back twenty years, and you only need the last, like, three or four years of it, archive it. Or, upgrade to SQL Server.
SQL Server can have much, much larger tables. I've got databases pretty big'hundreds of thousands of records, and yes, I've got it split into multiple backend files. A lot of it's an SQL server, like the customer records and stuff like that, but a lot of my like course data and things like that are still in access tables. Two gigabytes is huge, people. That's a lot of space. And, of course, remember we're not saving blobs in our database.
What's a blob? Any kind of object, document files, Word documents, PDF files, images. We don't save that stuff in our tables. We save those at the file system level in a file folder, and then we just link to it from our database. The number one reason why when people are yelling at me about the two gigabyte limit is because they're storing stuff that they shouldn't be in their database. Things like images, Access was not designed to store that kind of stuff. It can, but whenever you put an image in your table, it's going to take up twice the space that that image would normally take up on the file system.
No, don't do that. Yeah, you could have fifteen or twenty linked tables, and each of those can be two gigabytes in size. That sounds practically unlimited now.
I'll let's go about do it for today. I try to make this a weekly thing. We're going to try to do Quick Queries Mondays. So we'll see going forward. But that's going to be your TechHelp video.Quiz Q1. What is a common mistake people make when using the IIF function in Access?
A. Using IFF instead of IIF
B. Using IF instead of IFF
C. Typing IE instead of IIF
D. Forgetting to include THEN
Q2. What shortcut key can be used to toggle between the Design View and VBA window in Access?
A. CTRL+F5
B. ALT+F11
C. SHIFT+F12
D. CTRL+ALT+F2
Q3. What problem might occur in the VBA editor when a form has a running timer event?
A. The code becomes password-protected
B. The editor crashes immediately
C. The code turns red, loses spacing, and the cursor jumps
D. The timer automatically stops
Q4. What is the recommended action before editing code if you have timer events running in open forms?
A. Ignore the timer, as it is harmless
B. Increase the timer interval
C. Shut down any forms with active timer events
D. Save the form frequently
Q5. Is it possible to dynamically change the height of a text box in a continuous form in Access?
A. Yes, by VBA code
B. Yes, using conditional formatting
C. No, changing one property changes all
D. Yes, using macros
Q6. When is it better to use an SQL statement instead of a recordset loop to update data in Access?
A. When each record requires different processing
B. When dealing with simple, uniform changes
C. When working with text files
D. When you cannot use VBA
Q7. What is typically displayed in a properly set up combo box where the first column is hidden?
A. The ID value only
B. The first column as visible
C. The descriptive value, like a name
D. Both ID and name, always shown
Q8. Why might a user see numeric IDs instead of descriptive names in a combo box?
A. The combo box wizard was not used
B. The combo box bound column is set incorrectly or is not hidden
C. The combo box is read-only
D. There are duplicate records in the table
Q9. What is the main limitation of the image control in Access regarding animated GIFs?
A. It allows too many images at once
B. It does not support animated GIFs
C. It only works on Windows 7
D. It does not display static images
Q10. When deciding between Excel and Access for budgeting, what is a key reason to move from Excel to Access?
A. When you prefer a colorful interface
B. When relationships between different sets of data become necessary
C. When you want to use fewer tables
D. When you dislike automation
Q11. What does saving an Access database as an ACCDE file primarily do?
A. Encrypts all table data
B. Prevents users from copying the database
C. Locks design changes and hides VBA code
D. Increases performance speed
Q12. What is a common way to prevent unauthorized use of an ACCDE Access database on unregistered machines?
A. Save it as an XLSX file
B. Use an online activation or registration code system
C. Email a copy to each machine
D. Use a USB flash drive only
Q13. How can you prevent users from reading sensitive data in Access table files?
A. Hiding records in forms
B. Making tables hidden windows objects
C. Encrypting the data in the tables or using SQL Server
D. Deleting the data entirely
Q14. What is the recommended method for storing large files like images or documents when using Access?
A. Save them directly in Access tables as BLOBs
B. Save them in plain text fields
C. Store them in the file system and link to them from Access
D. Save them as attachments to emails
Q15. How can you change the background color of a form dynamically based on user actions?
A. It is not possible in Access
B. Use conditional formatting only
C. Change the Me.Section(0).BackColor property via code
D. Right-click and select a color
Q16. What happens if your Access database approaches the 2GB file size limit due to a single table?
A. The database automatically splits itself
B. Access deletes extra data
C. You should prune old data or upgrade to SQL Server
D. The database shows a warning but keeps working indefinitely
Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-B; 11-C; 12-B; 13-C; 14-C; 15-C; 16-C
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 tackling another round of questions that I receive from students, viewers, and forum members. As always, I've got quite a backlog of these questions, and many of them are interesting but do not necessarily require an entire dedicated video. So in this series, I'll group them together and address them in a more rapid-fire format.
First, I want to highlight something that comes up a lot in my Access Expert 9 class. One student recently couldn't get some of his Access code to work. After examining his screenshots, it turned out he was using IFF instead of IIF. This is a common mistake among Access users, and I remember making it myself when I first began working with VBA. The function is spelled IIF, which stands for Immediate IF. It operates as an IF-THEN statement inside a function, allowing you to return different values based on whether a condition is true or false. Just remember, it's IIF, not IFF.
Next, a Gold Member asked whether there is a shortcut key to quickly toggle between Design View and the VBA editor in Access. One of my moderators pointed out that you can use ALT+F11 for this purpose. This shortcut brings up the VBA editor, and pressing it again returns you to where you were. It's a useful trick to remember if you find yourself frequently jumping back and forth between coding and designing your forms.
Another question from the forums involved VBA editor issues such as code turning red, spacing disappearing, or the cursor jumping around unexpectedly. Usually, this happens when you have a timer event running in an open form somewhere in your database. Timer events in open forms can interfere with the code editor, causing problems like these. The solution is to close any forms with active timer events before editing your code.
Then, Eduardo, another Gold Member, wanted to know if it is possible to dynamically change the height of a text box in a continuous form. Unfortunately, you cannot do this. If you change a property like the height for a control on a continuous form, it affects all instances of that control across every record. You can use conditional formatting to change colors based on content, but you cannot adjust properties like height for just one row.
In the Access Developer Forum, Gregory had a question about the preferred method for updating a lot of data in a table. Should you use an SQL statement or a VBA recordset loop? Both are valid, but generally, SQL is faster and more efficient as it lets the database engine handle everything internally. If your update logic is simple and the same for every record, use SQL. If you need more complex decision-making or varied processing per record, then use a recordset loop in VBA. I tend to think in loops because I started programming in old-school BASIC, but SQL is the right tool for simple, broad updates.
I also want to remind everyone that while you can post questions in the YouTube comments, I'm much more likely to respond quickly to questions posted on my website. So if you need help, the forums on my website are your best bet.
CD4 asked if it's possible to center the title bar text on Access forms. The answer is that you can't control this behavior. The alignment of form captions depends on your version of Access and Windows, and Microsoft has changed it at different times. There is no option or setting to force the caption to be centered; it's handled by the application and the OS.
Another common issue is seeing an ID number, like 123, in a combo box instead of the actual text (such as a state name). This usually means that your combo box is showing the bound column, which contains the ID by default. To fix this, you need to hide the key column (column zero) so that the user sees the descriptive text. This setting appears in the wizard when creating a combo box, asking if you want to hide the key column. I cover this topic in more depth in Access Beginner Level 8, so refer to that class if you want a detailed walkthrough.
GoneFishin2006 pointed out, regarding my video on images in subfolders, that animated GIFs won't play in the standard image control, but will work in the web browser control. This is something I am aware of, and I agree it would be helpful if Microsoft added GIF support to the standard image control. I generally avoid using ActiveX controls unless necessary, as they can create compatibility issues when distributing databases.
William D. asked whether Excel or Access is better for budgeting. My advice is to start with Excel, especially for simpler projects or prototyping, and move to Access as your needs become more complex. If your budgeting is just a spreadsheet of numbers, Excel is fine. But as soon as you introduce relationships between accounts or need automation, Access is the better choice. One of my own projects started in Excel and eventually needed to move to Access for those reasons.
Ellen asked about selling a database without letting people copy, read sensitive tables, or change the design, and whether it's possible to set up monthly subscriptions. To prevent design changes, you need to distribute your database as an ACCDE file. This locks out design and code changes, though a determined attacker could theoretically reverse engineer it. Preventing copying is more difficult, but you can use a registration system, requiring users to enter a code for each machine; I have a seminar on how to do this. As for securing the data in your tables, Access does not encrypt data by default, so anyone with that backend file could read it. You can either use SQL Server for secure online data storage or encrypt sensitive data in your own tables, then decrypt it in your forms. I have resources on both approaches.
Steven wanted to know if you can change the background color of a form while entering data. Yes, this is possible, and I have a video on progress bars where a similar approach is used. You can simply set the background color property through code as the user progresses.
A question that also comes up a lot concerns the two-gigabyte file size limit in Access. The two-gigabyte limit is per file, so you can distribute your data across multiple backend files or use archiving if you have very large tables. Alternatively, SQL Server supports much larger databases. Remember, do not store large objects like images or PDF files within your Access tables. Instead, keep these in the file system and link to them in your database, as storing them in Access quickly eats up space and leads to problems.
That covers this week's batch of Quick Queries. I'm aiming to make this a weekly feature, so keep your questions coming. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Difference between IIF and IF in Access functions
Shortcut key to toggle between Design View and VBA window
Troubleshooting red code and loss of spacing in VBA editor
Impact of timer events on VBA code editing
Changing the height of text boxes in continuous forms
Using SQL statements vs recordset loops for updating data
Centering or aligning the form title bar in Access
Configuring combo boxes to display names instead of IDs
Handling animated GIF images with image and web browser controls
Choosing between Access and Excel for budgeting
Protecting Access databases with ACCDE files
Techniques for restricting end-user access and copying
Encrypting table data for privacy
Changing form background color while typing
Understanding Access database file size limits
Dealing with BLOBs and large data in Access
Best practices for storing images and documents with Access |
