Synchronize combo box & cascade data

Posted on 2010-01-06
Medium Priority
Last Modified: 2013-11-28
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 :(

SELECT VendorDatabase.VendorName
FROM VendorDatabase INNER JOIN Vendor Name ON VendorDatabase.Address1=VendorDatabase.VendorName
WHERE (((VendorDatabase.Address1)=[Forms]![VaccineAdminDetail].[cboVendor]));

Any guidance you can provide (even high level) for the synchronize and cascade would be greatly appreciated!!

Thank you!
Question by:LERNWebmaster
LVL 22

Accepted Solution

Kelvin Sparks earned 1000 total points
ID: 26197855
Selecting an existing vendor or creating a new one isn't a big issue. Only the Vedor table has addresses, so all you needin the vacine tables would be the Vendor ID. I'd need more detail as to what code you're using and where to help you. To see the address etc in the vacine forms, I'd use a single combo, that has columns (even if hidden) for the remaining vendor fields (addresses etc) and have forms filled using the combo after update event and filled from the columns. i.e. if the combo rowsource fields are Vendor ID, Vendor Name, Address 1, address 2, thenhave the column widths as 0,5,0,0 which means the vendor name only displays. In the after update event have VBA to say
Me.TxtAddress1 = Me.combo.Column(2) '' first column is Column(0), next is Column(1) etc....
Me.txtAddress2 = me.combo.Column(3)
Every time you change the combo value the form will see the new values.
LVL 27

Expert Comment

ID: 26197947
<...so it will flow into the Vaccine Admin table.>
I agree with Kelvin, only the Vendor# should appear in the Vacine Admin table.
LVL 44

Assisted Solution

Arthur_Wood earned 1000 total points
ID: 26199332

One potential problem that I see with the fields that you have described, is the use of Blanks in field (and Table) names.  Access does not handle Blanks at all well, as they are seen as separators between field names.  If you feel it is ABSOLUTELY necessary to use blanks, then enclose the Field (or Table) name in [...].  Otherwise, either eliminate the blanks altogether, or replace the blanks with an under-score character (_)
Vendor Name becomes [Vendor Name], or VendorName or Vendor_Name

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question