Results 1 to 6 of 6
  1. #1
    Tompham89 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2015
    Posts
    6

    Angry Access Parameter Value at the last cbo box

    Hi everyone,


    How are you today? I am having trouble with my example database. It would be nice if you guys could help me out, I really appreciate your help. The problem is I am writing the same SQL code for every combo box, but the last one requires me to enter the parameter value. I am building a database that limits the data in the second combo box depends on the selected value of the previous combo box. I did the same thing on Locations which is dependent on the Customer, then the ProductType depends on the Locations, but I can't make it work when go from ProductType to Product. It would be nice if you could help me to make it work. Thank you so much. Here is the database.
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    this is a common requirement and there are plenty of examples about - google 'cascading combos'

    To get you started, take a look at this example

    http://www.access-programmers.co.uk/...d.php?t=275155

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    ProductName field in ProductTypes should instead be ProductType.

    Set RowSource properties as follows:

    cboLocations:
    SELECT * FROM Locations WHERE CustomersID = [cboCustomers] ORDER BY Locations

    cboProductTypes:
    SELECT ProductType FROM ProductTypes WHERE LocationsID=[cboLocations] ORDER BY ProductName;

    cboProducts:
    SELECT Products FROM Products WHERE ProductTypes = [cboProductTypes] ORDER BY Products;

    Then all you need in code is to requery the comboboxes.
    Code:
    Private Sub cboCustomers_AfterUpdate()
    Me.cboLocations = Null
    Me.cboProductTypes = Null
    Me.cboProducts = Null
    Me.cboLocations.Requery
    End Sub
    
    Private Sub cboLocations_AfterUpdate()
    Me.cboProductTypes = Null
    Me.cboProducts = Null
    Me.cboProductTypes.Requery
    End Sub
    
    Private Sub cboProductTypes_AfterUpdate()
    Me.cboProducts = Null
    Me.cboProducts.Requery
    End Sub
    Suggest you use a naming convention of plurals for tables and singulars for field names. For instance table Products and field Product.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Tompham89 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2015
    Posts
    6
    Thank you so much for helping me out guys, I am still working on it, I will let you know as soon as I get it to work

  5. #5
    Tompham89 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2015
    Posts
    6
    I could not get it to work, could you please help me to fix the database file, and I will learn from there, thank you so so much !

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Why can't you get it to work, what happens - error message, wrong results, nothing? All I did to your db were the changes specified in post. What did you do? Provide your attempt for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2013, 01:20 PM
  2. Use Parameter in select statement (Sql in Access)
    By mohammadyou in forum Access
    Replies: 6
    Last Post: 06-04-2012, 01:40 PM
  3. parameter in access query
    By jassie in forum Queries
    Replies: 2
    Last Post: 02-29-2012, 12:03 PM
  4. Replies: 13
    Last Post: 01-10-2012, 09:56 AM
  5. Passing Parameter from Excel TO Access
    By jo15765 in forum Queries
    Replies: 1
    Last Post: 11-29-2011, 03:28 PM

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