Synchronize combo box & cascade data

Posted on 2010-01-06
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

    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

    < 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


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now