I have a somewhat complicated question (at least in my mind) for all of you Access gurus.
I’m working on developing an Order form that (for the purposes of this question) involves three tables:
- Orders
- Customers
- cust_CustomerAddresses
The main Orders form is linked to the sales_Orders table, and there’s a dropdown box on the form where you select the Customer to attach to the order. That dropdown box is linked to the CustomerID field in the Orders table, and populates with Customer names when selected.
After a user has selected a customer, they then need to select the Billing and Shipping address for the Customer. Since some Customers have multiple addresses for their various facilities, a single Customer record might have two or three different Billing/Shipping addresses, so automatically updating a pre-built Billing/Shipping addresses subform isn’t an option – instead the user will need to select an address. I figured I wouldn’t use a subform that’s embedded in the main form, instead I’d just use a popup to select the address and have that popup send the AddressID and street/city/state info back to the mainform. Only the AddressID is stored in the sales_Orders table for the Order record, the street/city/state/zip is just shown to the user for reference/confirmation purposes.
I’ve given it a lot of thought, and here’s what I came up with so far:
Once a Customer is selected, two buttons appear in the addresses section on the main Orders form - basically a “Select Address” button beneath both the Billing Address section and the Shipping Address section. When clicked I want this button to launch a popup subform where the user can browse through the addresses on file for the selected customer, and then press a “Select” button once they’ve found the record they want to use for the billing and shipping address, which will then pass the AddressID back to the Orders form along with the address details. Once an address has been selected (and as long as the Customer selection doesn’t change), I want the “Select Address” button to change to “Edit Address.”
I figured I could use OpenArgs to pass the CustomerID of the customer selected via the dropdown box to the popup Customer Address subform when the Select Address button is clicked. There, that ID would be used to filter the available addresses to show only those records where the CustomerID is a match. What I don’t know how to do is pass the AddressID of the selected address back to the Orders form, since the Orders form is already open, and my only experience using OpenArgs applies to cases where I’m opening a new form and passing a variable to it.
Also, I wanted to make such that if the Customer Selection changed on the Orders form and a new customer was selected, then the selected Addresses were cleared – but only if a new Customer was selected. If a user used the Customer dropdown and selected the same Customer, I’d want the addresses to stay put, but if they selected a different customer I want them to clear and the buttons to revert from “Edit Address” to “Select Address”.
Finally – I’d like users to be able to add new addresses to the Customer record if the address they need isn’t yet attached to the customer record. I assume that will be pretty straightforward since the record source for the popup form will be the cust_CustomerAddresses table, but since I passed the CustomerID to the subform programmatically I wanted to be sure I’d still be able to add new addresses using this method.
So, my questions are:
1. - I know how to pass the CustomerID from the Orders form to the popup CustomerAddress subform, but how do I pass the AddressID of the selected address back to the already-open Orders form, and populate some textboxes with that addresses information (street, city, state, zip, etc.)?
2. - I can imagine how to write an event that would clear the customer address selection fields if the Customer Selection dropdown box was used to select a different customer by comparing the CusotmerId of that already-selected customer to the CustomerID of the newly selected customer, but how would I get the CustomerId of the customer that’s already selected if I’m attaching the event to the AfterUpdate property of the combobox? Should I use “Before Update” instead?
3. - Will users still be able to add new addresses in the popup Customer Address subform, just like a normal subform, even though I passed the CustomerID to the form via VBA? Should I store the CustomerID in an invisible textfield and use that? Also – will the standard “New Record” command button macros work in that subform?
I’m really sorry for all of the questions – this is probably the most complex thing I’ve ever tried to do with Access and I just wanted to run it past you all before building it only to find it won’t/can’t work. You guys have been so helpful and knowledgeable that I feel like I’ve learned more reading these forums the last few weeks than years of building simple Access databases.
Windows 7 64bit 
Access 2016
Reply With Quote
Originally Posted by orange
