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
voliteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.