• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

3 Related Combo Boxes

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,
James
0
volite
Asked:
volite
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this codes

Private Sub Form_Load()
ClearComboBoxes
    '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
Next
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
0
 
voliteAuthor Commented:
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?
0
 
voliteAuthor Commented:
Oops!

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,
James
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now