Storing Duplicate Data 2
By Richard Rost
6 months ago
Store Historical Customer Address for Accuracy Part 2
In this Microsoft Access tutorial I will show you how to set default values in forms when copying customer address data into new orders, avoid common #Name errors, and handle situations where opening a form can create unwanted blank records. You will also learn tricks for updating default values on multiple fields, customizing buttons to add or view orders, and making sure your forms work smoothly whether opened from the customer form or a main menu. This is part 2.
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, Storing Duplicate Data, default value property, pound name error, set default value with VBA, copying customer address to orders, open form to new record, avoid blank records, button event code, address city state zip country fields, do command openform, build event, add order button, main menu button, replace embedded macro, new record issue, order form from customer form, automate order address, form controls tricks
Transcript
Today's part two of my Storing Duplicate Data series. If you haven't watched part one yet, go watch part one and then come on back.
So in part one we talked about why it's important to be able to have the address here for the customer and also have a separate address here. When you put new orders in, you know what the address was at the time of the order.
Now this involves making sure if you go to a new record here that this guy is open. If you're the kind of person that likes to just open up the order form and go to a new record and put in an order, you get all these pound name errors here.
Let's talk about this first and then we'll talk about the other thing. Now we can just pick a customer and have all that data fill in.
The problem here is that if you have a default value pointing to a form that's not open, Access is like, burrah, I got no idea. I can't figure out what you're talking about because this form isn't open.
So we're not going to set the default value here in the form's design time properties. But what we can do is we can have the button that opens this form say, "Hey, your default value is this other field on this other form."
Let me show you an example. First, let's get rid of these default values. Now, so you don't have to do all of them at the same time, watch this trick. Highlight them all. Now they're all different, so you can't select them all and delete them, but watch this. You can put a number one in there. Hit tab. Now you just set those all to one and now you can delete that one, which deletes all those values.
So individually, they're all deleted now. See that? If they're different, they won't show up here unless they're exactly the same thing. But you can change them all to the same thing and then delete that same thing. That's a cool little trick. That alone is worth the price of admission.
Save this now. We normally open the order form from the customer form. Here it is. Go back over here. And if I go to a blank new record, I want the default values to appear from here.
We can set those in this button. Now there's a couple of caveats though.
Watch this. If I go into the button, here's the button code that actually opens up that form. Now we could set those values here, but there's a problem. Watch what happens. The forms order f address equals address, which is the address on the form that you're on, the customer form.
If I save that and then close this. Close it. Close it. Let's open it back up again. If I go to orders now, it's going to - well, first of all, it didn't go to a new order. It's just changed the order on this one. So let's address that too while we're at it.
Let's go back to our code. Let's say we want to open up that - let's pretend that button is going to open up and go to a new record. So here, we're going to say comma, comma, acformadd. Instead of opening up the orders for that customer, we're going to add a new order. And then we're going to set the address. So save it. Close it.
Ready? Click the button. And it appears to have worked just fine, but notice what happens here. This record is now dirty. What does that mean? Well, that means we're actually adding a new record, which is kind of what you want to do. But if at this point you decide you don't want to add that order, now you just added a blank order for the customer.
If you go into the order table, you'll see there's another blank order down here for that customer. And every time your user clicks that button, they're going to add another order, and then they're going to add another order. You want to try to avoid that because then you get all these extra blank orders in this system. Here they are.
So we don't want to actually set the value of address from this button that opens the form. But what we can do is we can set that default value property to address.
So it's going to be forms order f address dot default value.
We're still not done yet, though. Watch what happens now. Save this. Close it. Close it. Open it. Click.
And we get back to pound name again. Why am I back to pound name? Well, because this form has no idea what you're talking about. What is address? I know that I'm address, but I have no value.
The trick here is you have to call that - whoops, I opened up the wrong thing. The trick is you have to call it by what it thinks that other field is. You have to put in here exactly what that string was before for the default value. It looks like this: quote equals forms customer f address, just like that.
So now when the order form opens up, it's going to set the address field's default value equal to what we had before. Make sense? Now you'll do this with all of the fields: address, city, state, zip, country. We got city, we got state, we got zip, we got country.
Then we just copy and paste. Copy. Paste, copy, paste, copy, paste, copy, paste.
So this will open up the form and set the default value properties, but it won't change the record that's currently open.
Click the button. Look at that. Well, my defaults are in place. I can close this. I can go to a different customer. I can click on this one. Look at that. All the defaults are still in place, because this button controls that.
Now if I open up the order form from somewhere else and go to a new record, I'm not getting pound name errors because the basic default value property is not trying to look for a form that's not open. See how that works? It's a neat little trick, but once you know it, it's really cool.
So now we should probably change this button here too. Let's redesign this button. This is going to be my add an order button. Then we'll slide this one down and we'll make another button to replace the one that we just destroyed. Copy, paste. View orders. We'll make this one view orders.
This guy can have its name as view orders button. Then right click build event. Then we'll put this back into what it should have been before, which was do command, open form order f, comma, comma, comma, customer ID equals the customer ID on this guy.
So I did the invoicing and the blank template. The add order - this one here - will put a little comment in here: add new order.
Open the form and set the default value properties to what that form sees as the default value, which is the other form.
Now that I've done this, let's talk about the other thing we were going to do, which was for people who like to open up - they don't want to go through the customer form.
I've had clients like this before too. They want to sit down, they've got a stack of paper invoices or whatever for the day. They want to open up the order form from here and just go to town.
So we'll put a button on the main menu. Copy, paste. To add an order, add new order. Give it a name. We don't want Alex yelling at us. Order button.
Can you have an add order button on the main menu and on the customer form? Absolutely. As long as it's on a different form, that's fine.
Right click, build event. Oh, look what happened. It went into the macro editor. Why? Because when I built this main menu form, some of these buttons, I did for beginner students. Remember that? And I used the command button wizard to create these.
So we're going to have to go in here, go to events. This is fine. Delete the embedded macro that's in there. Just hit delete. I like to tab off it, come back to it and then hit the dot, dot, dot button and that'll put us back in the code builder.
Again, this is just going to be do command dot open form order f, comma, comma, comma, acformadd, it's going to add a new order.
Ready? So back to the main menu. I'm going to hit the add new order button and now I'm ready to enter a new order.
What I want to happen at this point is we're going to drop this down, pick a customer. Now notice at this point, the order is added and I want it to fill in his address.
I ran a little long today explaining that other stuff with the pound name error, so we'll cover this in tomorrow's video.
So you know the drill. Tune in tomorrow, same bat time. Blah blah blah. No, seriously, we'll cover it tomorrow. You can watch it right now, but that's going to do it for your TechHelp video for today.
Hope you learned some cool tricks in today's video. I wasn't planning on going into that much depth with the stuff, but once I started going into it, I thought we had to talk about it. It's really cool stuff.
So I hope you learned something. Live long and prosper. I'll see you tomorrow for part three.
TOPICS:
Handling #Name errors with default values in Access forms
Copying customer address to new order forms
Removing default values from multiple fields simultaneously
Setting field default values via button code
Opening forms on a new record using acFormAdd
Avoiding creation of blank records when opening forms
Assigning default values to multiple fields programmatically
Proper syntax for referencing fields across forms
Copying and pasting VBA code to set multiple defaults
Configuring Add Order and View Order buttons
Editing button properties and code builder tips
Switching from embedded macros to VBA for buttons
Adding "Add New Order" functionality to a main menu form
Populating customer information after order form is opened
Managing different workflows for entering orders
COMMERCIAL:
In today's video, we are continuing with part two of the Storing Duplicate Data series. You'll learn how to avoid those frustrating pound name errors in Access by setting default values on your order form the right way, without creating extra blank records in your database. I'll show you simple tricks for removing existing default values, how to set up your buttons to properly fill in customer addresses, and the best way to handle adding orders from both the customer form and the main menu. You'll also see the differences in handling embedded macros and code for your buttons. 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 primary issue does the video address when entering new orders in the orders form in Access?
A. How to link customers with product prices
B. How to ensure new orders don't display pound name errors if source forms are closed
C. How to back up your data automatically
D. How to send emails from Access
Q2. Why should you avoid setting default values that reference controls on another form at design time?
A. Because it will always slow down your database
B. Because the default value may display errors if the referenced form is not open
C. Because it makes your forms impossible to print
D. Because users will not be able to edit those fields
Q3. What simple trick is suggested for removing multiple different default values at once in Access?
A. Close and reopen the form
B. Assign the same temporary value to all, then delete that value
C. Use the Clear All command
D. Select and delete each value individually
Q4. What is the consequence of directly assigning field values from the button that opens a new order form?
A. No changes occur unless the form is saved
B. Multiple blank records can be added to the orders table
C. The customer table gets overwritten
D. The application will crash immediately
Q5. How does the recommended method set the default value of address fields on the order form?
A. By directly copying and pasting the current address into the record
B. By assigning the .DefaultValue property using a string reference to the customer form field
C. By linking the order table and customer table directly
D. By importing customer data from an external source
Q6. Why would you encounter the pound name error when opening the order form from somewhere other than the customer form?
A. The address field uses incorrect formatting for phone numbers
B. The default value references a control that does not exist or is not open
C. The field is set as required in table design
D. The field's caption property is not set
Q7. When copying the logic for default values, which customer details are intended to be copied to the order form?
A. Only the customer name
B. Only the country field
C. Address, city, state, zip, and country
D. None; information is re-entered every time
Q8. What is the benefit of using the button method to set the order form's default values compared to setting them at design time?
A. It allows dynamic filling without adding blank records and only when needed
B. It reduces the file size of the database
C. It prevents any kind of user error
D. It enables real-time data validation
Q9. Is it acceptable to have an 'Add Order' button on both the main menu and the customer form?
A. No, only one button is allowed per database
B. Yes, as long as they are on different forms
C. No, it causes a conflict with Access macros
D. Yes, but only if they have the same code
Q10. What should you do if the button event on the main menu opens the macro editor instead of the code builder?
A. Reset your computer
B. Delete the embedded macro, then use the code builder
C. Ignore the error and continue
D. Export the macro and re-import it
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-A; 9-B; 10-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 is part two of my series on storing duplicate data. If you have not yet seen part one, I recommend watching that first to understand the background, then coming back to this section.
In the previous lesson, I explained the importance of maintaining both the customer's current address and also storing a separate address for each order as it was at the time the order was placed. This allows you to track historical data accurately, even if the customer's information changes later.
One of the challenges that arises is when you try to create a new order record without first opening the customer form. If you prefer to open the order form directly and add a new order, you might encounter error messages like the dreaded pound name error in your fields.
Let me address how to handle this, and then move on to the next topic. By simply selecting a customer, we want all the address fields to fill in automatically. However, if the form tries to reference a default value from another form that is not open, Access cannot resolve that reference and does not know where to find the data.
Because of this, it is not a good idea to set the default value for the address fields during form design time if that value points to another form's control. Instead, you should have the button that launches the order form handle setting the default value based on the currently open customer form.
For example, first, I remove the default values from all the relevant fields. Since you cannot select and delete differing default values all at once, I use a quick workaround by changing each of their default values to a common value like '1', then deleting that, which clears them all simultaneously. This little trick can save you some time.
After saving, I focus on the process of opening the order form from the customer form. When you start a new order, you want the address information to default to what is shown on the customer form at that time.
To implement this, I edit the button's code that opens the order form. Initially, I tried directly assigning the field values, but that creates a problem. If the order form simply opens to an existing order, it just changes the address there, which is not what we want. If you adjust the code to open a new record instead, this seems to work at first. However, this method immediately creates a new blank record as soon as the form is opened, just by clicking the button, even if you do not proceed to enter data. This leads to unnecessary blank orders accumulating in your table every time someone clicks the add order button, which you definitely want to avoid.
To fix this, instead of setting the value directly, you set the default value property of each field on the order form. This way, when you create a new record, the fields are pre-filled, but no new record is actually added until the user begins entering data. In other words, you instruct Access to set the default value property of the address, city, state, zip, and country fields to match what is on the customer form.
There is a small adjustment required for this to work. The default value property should be set using the specific string Access expects, which references the customer form's controls, such as '=Forms!CustomerF!Address'. This ensures that the order form can grab the correct current values from the open customer form.
Once this is in place, you can copy the code for each address-related field, so that every time you open the order form from the customer form, all these fields get the correct default values. Now, if you open the order form and go to a new record, the address and other details appear filled in automatically, and there are no stray pound name errors. The form behaves as you want, and you avoid creating unwanted blank records.
I followed up by redesigning the buttons for adding and viewing orders. The 'Add Order' button is now clearly labeled, and the 'View Orders' button opens orders related to the currently selected customer. When building buttons on the main menu, it is important to remember that the button wizard may have originally created macro-based events, especially if you built the menu for beginners. You will want to switch those to VBA by removing the embedded macro, then creating a code-based event handler.
The general approach is the same - whether you are adding an order from the customer form or from a button on the main menu, you want to make sure the order form opens up ready to accept a new order, and then sets up its default field values appropriately. Users can then easily select a customer and have their address information filled in automatically.
For those who like to process a batch of new orders from the main menu, not necessarily from within a customer's detail, you can feel comfortable adding an 'Add New Order' button to the main menu with its own event handler, totally separate from the buttons on the customer form.
I ran a little long in this session addressing the pound name error and how to prevent blank orders, so I will continue with the remaining portions of this process in the next video. Stay tuned for part three, where I will address filling in address data when orders are added directly from the main menu.
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
Handling #Name errors with default values in Access forms
Copying customer address to new order forms
Removing default values from multiple fields simultaneously
Setting field default values via button code
Opening forms on a new record using acFormAdd
Avoiding creation of blank records when opening forms
Assigning default values to multiple fields programmatically
Proper syntax for referencing fields across forms
Copying and pasting VBA code to set multiple defaults
Configuring Add Order and View Order buttons
Editing button properties and code builder tips
Switching from embedded macros to VBA for buttons
Adding "Add New Order" functionality to a main menu form
Populating customer information after order form is opened
Managing different workflows for entering orders
|
