Results 1 to 4 of 4
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

    3 Comboboxes each need to update form, 2 Comboboxes also need to be able to filter by other combobox

    I have been learning Access for a couple months and am now in a pickle.

    I work for a company that has multiple vendors in multiple cities/states. A single vendor could have locations in up to a dozen different cities and each city could have twenty or more vendors.

    I currently have a form (frmVendorDetails) based on a query (qryVendors) which is based on 4 tables (tblVendors, tblVendorInfo, Cities, & States). tblVendorInfo is what ties the other three together. Each of the other three have only 2 fields, ID and whatever it's titled. There are foreign keys for all three in tblVendorInfo.

    On my frmVendorDetails, I have 3 comboboxes (cboVendor, cboCity, cboState).

    I need to be able to filter the form by using each of the comboboxes on their own. (I have been able to accomplish this).

    Now, I need to be able to filter cboCity if either cboState or cboVendor has a value.



    And finally, I need cboVendor to filter if there is a value in cboCity or cboState.

    I have tried using cascading comboboxes, but that doesn't allow me to use each combobox individually.

    Here's what I'm trying now and when I try to click on cboCity, it's blank after this code runs.

    Code:
    Private Sub cboVendor_AfterUpdate() 
    
    If IsNull(cboVendor.Value) Then Exit Sub
    
    Dim strWhere As String
    
    strWhere = "ID=" & Me.cboVendor
    
    Me.Form.Filter = strWhere
    Me.Form.FilterOn = True
    
    Me.cboCity.RowSource = "SELECT Cities.ID, Cities.City, tblVendors.VendorName, tblVendors.ID" & _
    "FROM tblVendors INNER JOIN (Cities INNER JOIN tblVendorCities ON Cities.ID = tblVendorCities.City_ID) ON tblVendors.ID = tblVendorCities.Vendors_ID" & _
    "WHERE tblVendors.ID = " & Nz(Me.cboVendor) & _
    "ORDER BY Cities.City"
    
    cboCity = Null
    cboCity.Requery
    
    End Sub
    Any ideas would be helpful. Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use this to see how the SQL is coming out:

    BaldyWeb-Immediate window

    Offhand, you need to add spaces between lines. In other words, your result for the first 2 lines will be:

    ...tblVendors.IDFROM tblVendors...

    Note the lack of space before the word FROM.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Yep, I was missing the spaces at the front. Code works great now! Thank you!!! It's so often the most simple answer that gets missed.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report filter form using multiple comboboxes.
    By Stephenson in forum Programming
    Replies: 9
    Last Post: 10-24-2015, 10:00 AM
  2. Replies: 4
    Last Post: 09-25-2015, 11:12 AM
  3. connecting parent combobox to child comboboxes
    By aparnawangu in forum Forms
    Replies: 4
    Last Post: 08-04-2015, 09:50 AM
  4. Replies: 17
    Last Post: 04-29-2015, 11:48 AM
  5. Cascaded comboboxes in a form
    By luca in forum Forms
    Replies: 2
    Last Post: 11-22-2009, 05:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums