Account Balances 5
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 5
This is part 4 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
Today we're going to add our own Add Account button. We'll open up a form in Data Entry mode. We'll set certain fields as Required. We're going to perform some manual data validation in our code using a Form On Unload event, but then we'll have to switch it to the Before Update event, and I'll explain why (hint: it's all about timing). We'll create our own Save and Cancel buttons (and turn off the default close button). Then we'll do some other stuff with zero length strings, and have a bunch of fun.
Members
There's no extended cut today, but here's the updated database:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Suggested Template
Links
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, add record button, data entry mode, required, zero length string, before update, form on unload, cancel, save button, cancel button
Intro
In this video, I will show you how to add an "Add Account" button to your Microsoft Access account list form, set required fields with custom data validation, and handle save and cancel operations with user-friendly buttons. We'll walk through the timing differences between the form OnUnload and BeforeUpdate events, manage issues with nulls and zero-length strings, and adjust table properties for proper validation. You'll also learn how to prevent common design pitfalls and make your forms more controlled and user-friendly. This is part 5.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today, we're going to add our own Add Account button on the account list - this guy, right there. So when we click on that, Add a New Account will open up the detail form for each account in data entry mode so we're just adding a new record.
We're going to set certain fields as required, which I normally don't do, but I do want to do it for this database for reasons I'll explain later. We'll do some manual data validation in our code because I don't like the stock required prompt that you get from Access. We're going to put our own in there.
We're going to start by using the form OnUnload event and then I'm going to show you why it doesn't work, because that's normally how I do data validation - in the form unload. But then we're going to switch it to a different event and I'll explain why. It's all about timing, and you'll see why as I experience this.
Then we'll create our own Save and Cancel buttons here. I'm going to turn off the default Close button and handle it in my own buttons, so you can either save the record, which will do the data checks, or you can cancel it, which will abort that. That's pretty cool.
Then we're going to do some stuff with nulls and zero-length strings and have lots of fun today. So are you ready? Here we go.
Prerequisites for today: You should know what data entry means when it comes to a form. That means you're just adding records - you can't edit existing ones. Go watch my Required video. I explain why I don't like to use it that often, but for this database I'm going to. Go watch my Null video if you don't know about null values yet (ISNULL and that stuff). This one is optional, but go watch this too - Zero Length Strings. This is where you've got a value in a field and then you delete it. It doesn't become null, it becomes a zero-length string, and it's an issue, so go check this out. These are all free videos. They're on my YouTube channel and on my website.
You're on part five now, so you must have watched the previous four. So just go watch that stuff.
Okay, back at it. I took a little break after part four so I'm feeling much better now.
The first thing we're going to add today is an Add Account button. We need a way to easily add an account since I turned off the ability to add records here. I like to put Add and Delete buttons down below. We'll talk about deleting in a minute, but let's start with Add.
So what we're going to do is go to Design View, we're going to drop a button down there, and cancel the wizard, and we're going to make this one say "Add Account." Actually, we're not going to put a delete button on here. We'll put the delete button on the detail. All right, we'll make this guy say "Add Account BTN." Right-click, build event, Code Builder pops up, and we're going to say DoCmd.OpenForm "AccountDetailF", , , , acFormAdd which means we're in add a new record only. If you want to specify any other options, it'll just open it up. Otherwise, it does it on a blank new record.
Save it. Let's close it and see what happens. We'll put it back up again and I'm still in the Rick's data file. Let me just switch back over to Company. Okay, make sure I don't do that in the future when I actually put some of my real data in there. I've still just been playing around with it.
All right, so Accounts, Add Account. All right, this snapshot - because remember, this guy is modal. Yes, modal - not popup. I don't like popup, because popup isn't very wise as to what monitors you have. If I design something like this on my big external monitor - normally my actual PC's a laptop, so that sits below it. So if you move from machine to machine - like if I open up something else on my other laptop, for example, it might have the position saved and it's off the screen. So that's one of the reasons I don't like popup form. Modal I love, though. I don't want the user to be able to do stuff like clicking behind it and changing things. I want to keep you right here.
Now, this brings up - since I'm in here adding stuff, this brings up two things - I want to make it so that Account and Type are required fields. Account Number, Routing, and Color and all that - that's not, but these two things should be required. So while I'm thinking about it, let's go back to the table.
Now, see, this is one of the problems with a split database. We've got to go to the actual backend table. If you have multiple backends, you're going to have to make this change in each of them. There's no easy way around it. So I'm going to just do it in one for now and then we'll do it in this one here. In fact, we have to close this because it's linked to it, so that lock file goes away. Now we can open up "CompanyA" and make that change.
In the AccountT, right-click, Design View. I'm going to make AccountTypeID required, and I'm going to make the AccountName required. Everything else is fine, just those two things.
Save that, we'll check to make sure the existing rules applied are okay. This should be AccountTypeID and AccountName.
Now, I'm going to go do that off camera in the other two database files, but if you have multiple company files you have to make this change in each of them. Can we make this change programmatically? Yes, you can do it with both VBA and with SQL, and I've got lessons for both - they're on my website. If anyone wants to know which ones, let me know and I'll tell you. It's not easy, but this is something that once a database is finished, you pretty much don't have to do often.
But I have had situations in the past where I've built databases for clients of mine where they're using their database and they've got a year's worth of data in it. But they want to make a few minor changes, and I've got to update their tables in the backend database with the new database, right? So I have two options: I can either log in remotely, which I've done before, using PC Anywhere (which I used back in the day, but today I use Google Chrome or Remote Desktop or whatever) and do the edits there. Or you can program your frontend database to examine the table and say, "Okay, I see you're missing this field or this property. I need to add it." Or sometimes I've made my client a one-time use button. I tell him, "Okay, when you get this new version, open up your manager menu, click on this upgrade button and it will upgrade the table." Then you can either run the SQL statement or you can run whatever developer DAO code you've got. I'll put links down below to the actual classes where I cover this stuff, but it is definitely possible.
Then the client gets their frontend update and it says, "Hey, you've got to update your backend tables. Click this button," and then it does the update. You can add fields, you can change properties, you can do whatever you want with code.
So there, I've added it. Now if I go into my Accounts and I try to add an account and I just type in a name and I close it - it let me do it. Uh-oh, what happened? This is something people also email me about all the time. So I left this in here. I forgot about this for a second, but I left this in here.
Why? If you go in here - well, we have to refresh this, by the way, when we close that - but when we come back into here, look, there's my new account. Oh, the CompTypeID is zero. Let's get rid of that default value too so that we have to pick it.
So I'm going to delete that and once again, let's take a trip to our backend database. Yeah, I know, but this is how database development works. I'm trying to show you the actual way that when I build a real database, you have to go back and forth sometimes. So, AccountT Design View, and it's going to say the other one's open. Yes, see "An object bound to AccountT is open." And you can tell that because if you look here, you'll see there's an LACCDB file - that's the lock file and it's zero. But this tells you that someone's got that table open.
So we've got to go back to the front end, close that, and then that LACCDB file should delete itself. Let me give this a second. There it goes.
Yes, that's right, the relational combo box. Let me and menus also found do it. Go away!
Oh, the database file is open itself. Okay, so yes, if this is open, it'll have a lock file there too.
Now I can come in here, Design View, and AccountTypeID - we're going to make the default value null. That way, it doesn't have a value until the user gives it one. Again, do this for both of your backends, or all three or whatever you have.
Ok, I changed all of mine, so now if I open it up and I try to add another new account - Add Account - if I try to close this now, "You must enter a value in..." blah blah blah. Now, this isn't very user friendly.
So before it gets to that point, I like to put something in the form itself to check, and I can give an error message myself instead of this default error. So you need to get this too.
Let's hit Escape a couple times here, go into Design View.
So we're going to do our own check in this form's OnUnload event. Why OnUnload? Well, because Unload can be canceled. You can check some stuff like make sure fields have values, and if they don't, you can cancel it. I talk about this more in my Prevent Shutdown video, where we actually put an OnUnload event in the main menu, which will check and make sure that the user wants to shut down the database if the main menu tries to close - same kind of concept.
So find OnUnload.
We're checking two things: we're checking AccountName and we're checking AccountTypeCombo. Use the name of the field, not AccountTypeID - AccountTypeCombo. We're just going to go in here and say:
If IsNull(AccountName) Then
MessageBox "Account name is required."
Cancel = True
Exit Sub
End If
We'll do the same thing for the type:
If IsNull(AccountTypeCombo) Then
MessageBox "Account type is required."
Cancel = True
Exit Sub
End If
That way, they get a unique error message for each. You can lump them together if you want. What happens is it checks to see if it's null, and if you don't know what null is, go watch the video.
If it is null, then you're going to MessageBox "Account name is required," and set Cancel to True. Cancel will make that event not happen, so it cancels the unload and leaves the form open.
Now, normally, this is how I handle checking for stuff when a form closes, but you're going to see this is going to be a problem. I'm leaving this in the video because I want you to experience this too.
If I put something in here and I try to close it, I still get this - it's ignoring my event: "Can't save changes." The timing of a close, etc... Sure, and it's actually going to still lock there, watch. Right, it's required so you can't close it, and even if I hit Escape a bunch of times, I still can't close it. So you have to put a bogus record in - you get stuck in this loop. This is one of the reasons I generally don't like required, but I want it for this particular instance.
So, what's the problem here? Well, the problem is it's all a matter of timing.
I wanted to leave this in the video to explain this to you because I almost always use the form OnUnload to check stuff like this - but I almost never use Required too. So what's happening is the Required property's being checked before the record gets saved into the table, and that runs before the form gets unloaded. So in this particular case, FormUnload is the wrong event to use. We want to use the form's BeforeUpdate event. That runs before the data is committed to the table, and that runs before FormUnload.
The mistake I made just a few minutes ago - and I'm thinking to myself, as soon as I saw that required message, I'm like, yeah, the bell went off in my head. You're right, it's been a few years since I've seen this one.
So you have to check this stuff - if those are required fields, before you commit them to the table. So I'm going to take this stuff, I'm going to cut it out, we're just going to get rid of the form unload event, and we're going to use form's BeforeUpdate for this guy.
Click, come over here, event, we're going to look for the BeforeUpdate event. Now paste that in. So this will run before the changes are committed to the table.
Save it, close it, close it, open it. Now if I go X and try to close it, it says "Account type is required." Hit OK. "You can't save the record at this time. Do you want to close the database object anyways?" We'll say No. But now, at least I can come in here and hit Escape and then I can still close this if I don't want to make those changes.
I like to actually give the user a Cancel button in here too. So let's open this up, Design View. Grab a button in the form footer and let's make a Save and a Close button of our own.
Cancel this. Let's make a Save button. And in fact, what we could do is we could disable this close button over here.
We're going to go to Format, and we're going to turn off the Control Box and the Close button, so we're controlling everything through our buttons. Most users want to gravitate over here, but this actually makes things a lot easier.
Now, in your Save button, what we'll do here is put this stuff in here. Then we don't need that form BeforeUpdate. Yes, I know I'm going around in circles, folks. Like I said, I didn't plan this whole database out all in advance. I'm kind of just building it as the whim hits me, which is how I build a lot of projects.
So now, what we're going to do here is we don't need the Cancel = True - we'll just say "Account name is required. Exit Sub." Then "Account type is required. Exit Sub." and it won't let them close the database. If it gets down to here, DoCmd.Close acForm, Me.Name, acSaveYes. That saves changes.
So now, if I go to Add, if I put something in here, I can't close it over here; I can't get behind it, so I have to hit the Save button. Count type is required. I've got to put an account type in; otherwise, I can't get around it.
If the user knows to hit Escape, then they may be able to close it and save or cancel. So "Account name is required." So we need to give them a Cancel button too.
Copy, paste, Save or Cancel. The Cancel button is basically going to just undo any changes (hope I didn't name the button badly). Back over here, open this up, Cancel button. You can assign this as the cancel button here so if the user hits Escape, it pushes that button. You can also do this with a Default with Enter, but I don't like that because we've got a long text field on here, so I don't want to just type in and hit Enter and then it pushes that button.
This will be a Me.Undo that undoes the changes, right, and then closes the form without saving.
Ready? Add Account, put something in here, just hit Cancel, and it closed it. Add Account, put something in here, Save, "Account type is required." Say No, can't do it. Cancel, and we're good and there should be nothing here.
Beautiful. I like controlling my forms like this. I often will turn off these things - the Max/Min button - it's there, you just can't see it. What's going on with the design? Format - where's that? Both enabled, but they're not showing up because the form is modal. Let's turn those off.
Add Account; now I'm not accidentally clicking it. It's weird, it doesn't show up, but it's working. You can still double-click there - oh, no, you can't. Okay, it turns that off too. Nice, now they have to use my buttons. I have the power; I have the control.
I know I took a little bit of a roundabout loop there, but I just wanted to show you my thought process. I want to leave this in the video so you can see that, yes, even I'm human, sometimes I forget stuff too. I almost always use the Form Unload event to do data validation like that, to check stuff, because I don't really use required property that much and I'll control it if they don't have a value in there. But for this database, I do want to use the required properties because I'm going to have other ways to get to that data. And this way, I don't have to put checks everywhere. Because if you're controlling this stuff yourself, you have to put checks everywhere the user can modify that data.
I also want to leave this in the video to show you that even if you're trying to do something one way and it's not working, I bet you there's another way that it could be done. If one method isn't working, there's two or three other ways. There's a million ways to do everything in Access, that's why I love it so much. If the form unload event doesn't work, try the after update or the before update or something else.
And while I'm thinking about it, let's work on our form captions here. Design, change Format caption: "Account Details." Little things like this that I usually polish the database when I'm finished with it, but I'm going to try and do some of this as I notice it. "List."
We got Accounts, there's our Account List and there's Account Details - and why did you not open? Oh, you're not opening. Oh, I got another bug here, it looks like double-clicking is not giving it to me. This is something I've seen happen before too.
What's happening here is when I double-click on this, it's opening this form up and it got stuck in data entry mode. Here's why: If you make design changes, if the form opens and you make a design change, you do some stuff here, any settings that this form has that may have been changed in your code get saved in it sometimes - specifically like your Order By, things like that. Your Filter - see, it's set to AccountID equals 2, but the Filter OnLoad is not on, so you normally don't see that.
But if you make a sort change - like an Order By - that Order By OnLoad gets saved. So if you make a sorting change, save the form design, that gets saved. Well, what got saved was this Data Entry equals Yes because we were in data entry mode a minute ago and I made some design changes and it saved that setting. Since this doesn't specifically tell the form not to open in data entry mode, it's opening it in the last mode it was stuck in.
So there's two things we're going to do here: one, we're going to turn that back to No, and two, I'm going to go in here just in case that happens again. I'm going to specifically tell this event (double-click event) - where DataMode is, you get DataMode, Filter, etc. We're going to put this in acFormEdit, which is the default, unless the form itself is in data entry mode, which was what just happened - this guy got stuck.
Weird stuff, but you just have to know how to deal with it. And now we're back in it. It saves its position too.
One more thing we have to do is when this guy closes and it comes back here, when we require this list. So we're going to go back to our code, Design View. Either one of these buttons should require that list - now Cancel shouldn't, because Cancel means you didn't make any changes - so just the Save button. So we're going to Forms!AccountF.Requery. Not a record set Requery, do an actual Requery so you see the new accounts. If you do a record set Requery, it doesn't show you new records.
Add Account, new account, Account Type, oh, Save it - can't do it. One more thing I'm thinking of - I always add these little things on as I'm going. Account Type is required, let's drop that box down for them, let's be helpful. Account Type is required, here we'll do accountname.SetFocus, and here we're going to do accounttypecombo.SetFocus and then accounttypecombo.Dropdown. We're going to actually open that box up for them.
Save it. I'm going to cancel it and then go back and put it - and it should have saved my copy. Copy it to the clipboard just in case. So I try to save it now - Account Name is required - but it put me there. OK, New Account, and now I save it, Account Type is required, and then it dropped it down.
And one more thing - I don't like it when my message boxes say Microsoft Access on top. So comma, let's go under exclamation. When I'm building the database, I just go through with the message box quick, and then this is one of those polishing things afterwards. Exclamation, and then the title will be Account Name. Same thing here. Save it.
So now if I get rid of this, Tab, you're ready, now sit... and oh, oh, that brings up another thing. Well, let's take a peek at that. Let's go back to AccountT. I got a blank account name there - what happened?
A lot of good learning opportunities in this video. It's required, but I was able to blank it. Why is that, does anybody know? When you're in a form like this guy, if you take a text field and just highlight everything and hit delete, you didn't null it out. What you did was you set it equal to an empty string. Empty string. So I'm going to put some stuff back in here, so we're going to delete that record.
So, there's one more table change we need to make and a little bit of a change to our code. Let's go back to our backend table again. Design View, open it up.
Design View, go to your AccountName. There's this field here called Allow Zero Length. That means you're saying it's okay for the user to have something in that box and then delete it. It's not null. There was a value there, but now I'm telling it zero - it's now zero length. Same thing happens if you took double empty quotes. It'll make it zero length.
Sometimes you want to allow zero length. For example, and I have a video on this, middle name, for example, is a perfect reason why you might want to allow zero length. You might have people who don't have a middle name, so you want to allow that to be zero length. Null means I don't have any data. Zero length means I have data and it's blank. I know the person's middle name and he has none, versus I don't know this person's middle name. That's the difference between null and zero length. Big, confusing topic for a lot of newbies and myself included. It took me years before I really got this concept. No one explained it to me like that. Books at the time were so confusing. I wish I had me to teach me when I was younger.
So set Allow Zero Length to No in all of your backends, again. Close it, save it, now go do those other ones off camera.
Now I'm back, I'm going to add an account. I'm going to put XXX in here, try to save it. Account Type is required, that's fine. I'm going to leave it blank still. Now, I'm going to blank this, and now it yells at me: "You must enter a value in the Account Name field." Now, if you want to have friendly error messages here too, you can put them in the BeforeUpdate events for these boxes as well. The event we have for the entire form checks everything, but you can also put them in the BeforeUpdate event for here. For example: Design View, and go to the BeforeUpdate event for the AccountName.
It'll be a similar message to this one: AccountName BeforeUpdate.
If IsNull(AccountName) Then
MessageBox "Account name is required."
Cancel = True
Exit Sub
End If
We'll do something similar for the AccountTypeCombo, right here, BeforeUpdate. We could make these the same thing, but then you have to pass back the actual value - it gets to be a bit of a headache. Again, here, we just need True. We don't really need the Exit Sub here because it's going to drop out of the sub anyway, but I like to leave it there in case later on you decide to put more code down here. At that point, you won't forget to drop out.
Save it, close it, close it. Here we go. Put something in here, good to go, Save it. Found type is required, that's fine.
Leave that blank, back over here, delete that, Tab off it, and I get "Account name is required" and I'm stuck sitting on it. I can't leave it now.
Now, this is a case where you're actually going to be stuck here unless you know to hit Escape, and that's one of the reasons I sometimes like to see the edit bar over here, which I might turn back on again.
Or here's another way you can handle it - I can't tab off of it because it's required and it doesn't allow zero length. What I could do here is say "Account name is required" and you could prompt them and say "Would you like to cancel?" or something like that, but that's up to you, however you want to handle it. You could just put something in here and then hit Cancel and it'll cancel your changes. Or, if they know enough, they do this - try "Account name is required," they could just hit Escape and cancel out. So you can make that a training issue or you can put a prompt on it, like an extra box, "What do you want to do?"
I might, now that I'm thinking about it, turn on that record selector, I think, because it really can't hurt anything because we've turned off the ability to do deletes. So yes, let's turn it back on. Sometimes seeing that pencil is a training issue - sometimes seeing that pencil really lets people understand what's going on better with the database. So let's turn on the record selector.
Add Account, now if I'm doing something, okay, Tab, I come back here, delete, "Account name is required." You can still see that you're in edit mode. You can make it a training issue at this point - hit Cancel and it'll abort it. In fact, you can even put that on your label.
I know I went around and covered a bunch of different weird things today, but like I said, I warned you up front. This is just kind of stream of consciousness with the series, so you're getting whatever I think of at that moment. It's not super well planned out like my regular courses and seminars are or even most of my TechHelp videos. This is just me sitting down building a database for myself mostly.
But yes, sometimes I forget things and then an issue comes up and then I remember - that's right, that's how you have to handle it. So you're kind of seeing stuff as I'm thinking about it.
Okay, that's going to do it for today. We'll do the Delete button next time.
So there you go, there's your TechHelp video for today. I know I did a lot of roundabout stuff, but I think for some of you it's going to be a cool learning experience to see all the different ways that you can do stuff like that, why you can do certain things certain ways and you can't do it another way. Again, however I encounter it is what you're getting.
I hope you learned something. I hope you had some fun. Tomorrow, we're going to tackle deleting.
As always, live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is the primary purpose of adding an "Add Account" button to the account list form?
A. To allow users to edit existing accounts easily
B. To let users quickly add a new account by opening the detail form in data entry mode
C. To provide a shortcut for deleting accounts
D. To export account records
Q2. Which fields were made required in the database to ensure data integrity when adding a new account?
A. AccountNumber and Routing
B. AccountTypeID and AccountName
C. AccountColor and Routing
D. AccountID and CompanyName
Q3. Why does Richard prefer to show his own validation messages rather than rely on Access's default required field prompt?
A. Access's default message is more detailed than needed
B. He prefers custom messages for a better user experience
C. Access does not have any required field validation
D. Access default messages only work for numeric fields
Q4. What is the main drawback of using the form's OnUnload event to validate required fields when the required property is set at the table level?
A. The event runs too late, after the data is already saved
B. The event does not trigger at all
C. The required property is checked before the form unloads, causing users to get stuck
D. It lets users bypass validation by closing the form
Q5. Which form event is best for performing validation before required fields are committed to the table?
A. OnOpen
B. OnUnload
C. AfterUpdate
D. BeforeUpdate
Q6. What is the benefit of using Save and Cancel buttons with the Close button and Control Box turned off on a data entry form?
A. It allows users to minimize forms
B. It ensures validation and user choices are properly handled, avoiding accidental closure
C. It lets users open multiple forms at once
D. It disables data entry features
Q7. When working with split databases and multiple backends, what is necessary when making table field property changes?
A. Update the property in only the frontend
B. Make the field property changes in each backend database separately
C. No action is needed; updates propagate automatically
D. Only update the main company's backend
Q8. What issue arises if "Allow Zero Length" is set to Yes for a text field?
A. The field cannot be updated anymore
B. Users can enter an empty string, making the field appear blank even if required
C. The field will always have a default value of 0
D. Users can only enter numbers in the field
Q9. What is the difference between Null and a zero-length string in a text field?
A. They are the same thing in Access
B. Null means unknown or missing, while zero-length string means blank but known
C. Null means blank, and zero-length means missing
D. There is no such thing as a zero-length string in Access
Q10. What happens if you try to save a new account without entering values in the required fields, after implementing the correct validation method in the Save button?
A. The record is saved with missing values
B. The form closes without saving
C. The user is shown a custom error message and cannot proceed until required fields are filled
D. The form submits an incomplete record and displays an Access error
Q11. Why is setting the default value of AccountTypeID to Null important in the new setup?
A. It pre-fills the combo box for easier selection
B. It ensures that a type must be selected and prevents a default value of 0 from being treated as valid
C. It makes all account types required by default
D. It disables the combo box entirely
Q12. What is a good reason for enabling zero-length strings for some fields, according to the video?
A. For all required fields to avoid blank entries
B. When you need to distinguish between "no data" and "data is intentionally blank," like a middle name
C. It makes validation easier for numeric fields
D. For fields that store only numbers
Q13. When is it appropriate to provide field-specific validation in a TextBox's BeforeUpdate event instead of the form's BeforeUpdate event?
A. When you want to prevent the user from moving off a field until a valid value is entered
B. When validating all required fields at once
C. Only for numeric fields
D. When you want to allow users to leave a field blank
Q14. Why might you still want to leave the record selector visible on an Access form, even if you've added your own navigation and control buttons?
A. Because it makes the database look more professional
B. To give users a visual cue that they are in edit mode and allow easier cancellation
C. To provide an export feature
D. To let users delete records directly
Q15. Which approach allows you to update backend table structures programmatically for clients?
A. Manually editing tables one by one
B. Using VBA or SQL code in the frontend to adjust the backend
C. Sending updated database files to overwrite the backend
D. None, backends cannot be programmatically modified
Answers: 1-B; 2-B; 3-B; 4-C; 5-D; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-B; 13-A; 14-B; 15-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
Today's TechHelp tutorial from Access Learning Zone covers how to add a custom "Add Account" button to your account list in Microsoft Access, and how to appropriately handle required fields and data validation when adding a new record.
In this lesson, I walk you through placing an "Add Account" button on the main account list form. When this button is pressed, the detailed account input form opens in data entry mode, allowing a user to add a new account record without being able to edit existing ones.
For this particular database, we need certain fields to be required. While I generally avoid setting required fields at the table level, there are specific reasons for enforcing it here, and I discuss those considerations during the lesson. Instead of relying on Access's default required field prompt (which is not especially user-friendly), I demonstrate how to implement our own data validation in VBA. This allows us to provide tailored error messages to users when they forget to enter necessary information.
Initially, my approach is to use the form's OnUnload event for data validation, which is my usual method. The OnUnload event lets you cancel closing the form if the data is incomplete. However, in this case, we run into issues because required table-level validations actually fire before OnUnload, triggering Access's stock error messages. After explaining why this timing causes problems, we switch our validation code over to the BeforeUpdate event, which is called before any changes are committed to the table and well before the form unloads. This proves to be the correct solution in this scenario.
I also show how to set up custom Save and Cancel buttons on the form, giving complete control over the user's workflow. The standard close button is disabled, so users must use the provided Save or Cancel options. Save runs our custom validation and attempts to store the new record, displaying helpful messages when required data is missing. Cancel undoes any new or changed information and closes the form without saving.
As we build this out, we encounter some classic Access issues relating to nulls and zero-length strings. Access distinguishes between a field that contains no value (null) and one that contains an empty string (""). It is easy for users to inadvertently leave required text fields as empty strings, so I show you the importance of disabling "Allow Zero Length" on required text fields to ensure empty strings are rejected, not just nulls. The distinction between null and empty string is a subtle but important one, and I provide some examples and discussion around when you might want to allow or disallow zero-length strings.
For those unfamiliar with concepts like data entry mode, null values, or zero-length strings, I point to several prerequisite videos available for free on my YouTube channel and website. It's essential for anyone working with Access forms to understand these ideas, so make sure you're up to speed before tackling this lesson.
Throughout the process, I highlight the back-and-forth nature of real-world Access database development, especially working with split front-end and back-end databases. Structural changes to tables, such as setting fields to required, must be applied to each back-end if you have multiple companies or sets of data. I touch upon methods for making these changes programmatically with VBA or SQL, as this is often needed for deployed databases.
An additional practical tip: I show how design changes and saved form properties can sometimes cause forms to reopen in unintended modes (such as data entry mode), especially after code modifications or design edits. I explain how to identify and fix this behavior to ensure that your forms always open as expected, whether the user is double-clicking a record to edit or adding a new one.
We wrap up by polishing some final details, such as setting helpful focus behaviors on validation errors (for example, automatically dropping down a combo box when a required selection is missing), customizing message box titles, and deciding whether to keep the record selector visible as a training aid for users. I also discuss user behavior like using Escape to cancel edits and consider whether additional prompts are needed if a required field is left blank.
This lesson demonstrates my approach to solving problems as they arise and emphasizes there are usually several ways to accomplish any given task in Access. If one method is causing trouble, try another event or validation approach. The flexibility of Access is what makes it such a powerful tool.
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
Adding a custom Add Account button to the account list
Opening a detail form in data entry mode
Setting specific fields as required in an Access table
Manual data validation using VBA instead of Access prompts
Issues with using the OnUnload event for data validation
Switching data validation to the BeforeUpdate event
Creating custom Save and Cancel buttons on a form
Turning off the default Close button and Control Box
Handling required fields with custom error messages
Dealing with nulls and zero-length strings in Access
Modifying field properties in a split database backend
Setting Allow Zero Length property for text fields
Updating default values to avoid undesired field content
Refreshing the account list after adding a new record
Dropping down combo boxes via VBA to assist selection
Setting focus to required fields after validation errors
Customizing message box titles for user-friendly prompts
Using the BeforeUpdate event on individual controls for validation
Managing problematic data entry with zero-length strings
Training users with visual cues like the record selector
|
