3 Related Combo Boxes

Posted on 2004-11-14
Last Modified: 2012-05-05
Hi Experts,

OK, this should be a snap for you geniuses -

I have a form where there are 3 combo boxes ("Type", "Brand", "Model")
They are fields within a table called "Instruments", which has 3 fields of the same name.

What I'd like to have happen is to make a selection from the "Type" combo and have the "Brand" combo update with only the Brands that match that Type.  Then, I would like to choose a Brand from the "Brand" combo and have the appropriate Models update in the "Model" combo.  

Is this possible, oh great ones?
Thanks in advance for any help,
Question by:volite
    LVL 119

    Accepted Solution

    try this codes

    Private Sub Form_Load()
        'Load row source for combo boxes on form load
        Me.cboType.RowSource = "Select Distinct Type From Instruments "
        Me.cboBrand.RowSource = "Select Distinct Brand From Instruments "
        Me.cboModel.RowSource = "Select Distinct Model From Instruments "
    End Sub

    Function ClearComboBoxes()
    Dim x As Integer
    For x = 1 To Me.Controls.Count - 1
       If Controls(x).ControlType = acComboBox Then
       Controls(x) = ""
       End If
    End Function

    'update combo box row source

    Private Sub cboType_AfterUpdate()
    Me.cboBrand.RowSource = "Select Distinct Brand From Instruments " & _
                            "Where [Type]= " & Chr(34) & Me.cboType & Chr(34) & ""
    End Sub

    Private Sub cboBrand_AfterUpdate()
    Me.cboModel.RowSource = "Select Distinct Model From Instruments " & _
                            "Where [Type]= " & Chr(34) & Me.cboType & Chr(34) & " " & _
                            "And [Brand]= " & Chr(34) & Me.cboBrand & Chr(34) & " "
    End Sub

    Author Comment

    Thanks for your response, Capricorn ---

    I plugged it in, and I got the message:
    "The record source 'Select Distinct Brand From Instruments Where [Type]= "Cornet"' specified on this form or report does not exist"

    After putting in your code, I went back to form view and selected "Cornet" from my cboType.  The error popped up when I tried to select a Brand from cboBrand.  

    Any thoughts?

    Author Comment


    I double-checked and found that I had the wrong table name, so it works!  

    One question on this though,
    I'm going to be using this in my customer database, so each customer will have a particular/different Type, Brand and Model.  Will I be able to store this data for each customer.

    Also, is there a way that I can have the combo boxes refresh if a change is made?  

    Thanks again,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    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.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now