Synchronize combo box & cascade data
Posted on 2010-01-06
I am struggling to get what I am looking for in Microsoft Access 2007 and I know it just can't be this difficult. I have a database with the following information:
Vendor Database (table) - Feilds are as follows: Vendor#,Vendor Name, Vendor Address 1, Vendor Address 2, City, State, Zip
Vaccine Admin (table)
Vaccine Admin Detail (form)
The Vaccine Admin table feeds an upload into our ledger package for accounts payable creation. The Vaccine Admin Detail form is where an outside groups keys the data for the payments.
The problem is they weren't using standard vendor names therefor creating 'new' vendors during the upload process. To eliminate that, I have thought to create cascading combo boxes that allow the person to choose current vendors (from the Vendor Database using the Vendor Name feild) OR add a new one (if it isn't in the vendor table). The problem I am having is getting the Vendor address (Address 1) to reset if the wrong vendor name was choosen (hence the synchronizing) and the cascading of the remainder of the address information (Address 2, City, State and Zip) so it will flow into the Vaccine Admin table.
Here's the code I was trying (in a qry, since I'm not very familiar with VBA) for the synchronizing but I was getting an error message :(
FROM VendorDatabase INNER JOIN Vendor Name ON VendorDatabase.Address1=VendorDatabase.VendorName
Any guidance you can provide (even high level) for the synchronize and cascade would be greatly appreciated!!