synchronize combo boxes

[PeterTow]

I am using Access 2007 and need to sychronize 2 combo boxes -car makes & car
models I have a table named "make" with a field named "make" and table named
"Car Model" with a field named "Model" I have made a form with both combo
boxes, the "Model" combo box works fine bring up all the data into the form
but I can not get the "make" box to bring up and send only the right data to
the Model combo box. I have tried everything that I get off the net to load
up the "after update" but do not know how to alter the codes-I have done a
lot with excel but this is the first time with access - spent 46 hours on
this one box and still not there can anyone help
Peter

 

[Doctor]

If you have Access 2007, open it up. On the left hand side of the screen, you
will see a list of templates. Under Microsoft Online, Click Sample. One of
the samples that you can open is called, "How to syncronize Combo Boxes."

 

[PeterTow]

Hi Thanks for that have tried but can not get it to work have done the test

 

[Doctor]

Have you started any code yet? If so, go ahead and post it and I'll try to
help you with it. Also, include the name of your two combo boxes. And include
the SQL for the query you are trying to get to work with your second combo
box. If you get that to me, I could probably write your code real quick for
you. I could probably come close enough to help you out even if you are
unable to provide the SQL/

 

[PeterTow]

Private Sub cboMake_AfterUpdate()
Me.cboMake.RowSource = "SELECT Make FROM" & _
" Car_Model WHERE Model = " & Me.cboMake & _
" ORDER BY Model"
Me.cboModel = Me.cboModel.ItemData(0)

End Sub

This is what I've got The first combo (Make) is just the car makes and the
second Combo (Model) is car models
Thanks

 

[Doctor]

Your criteria for the select statement says WHERE model equals Make. The
logic should be WHERE Model equals Model.

Me.cboMake.RowSource = "SELECT Model FROM" & _

" Car_Model WHERE MakeID= " & Me.cboMake & _
" ORDER BY Model"

Just to make sure, this should be based off of two tables: MakeTable and
ModelTable. Your Make table should contain a field in the table that is
related to Models in your model table.

That should do it.

 

[Doctor]

Peter, I was thinking of a few questions. I think that I was slightly
confused about which combo box is which. You said in your first post that
combo 1 is Make and combo 2 is the model. If this is the case, your code
below is changing the wrong combo box.

Combo 1 (Make) should lookup values in your Make table. Then after any
change (AfterUpdate) to the Make combo box, you want to change what is
displayed in the Model combo box.

Me.cboModel.RowSource = "Select Model From Car_Model Where Make = " &
Me.cboMake & " Order By Model"

Again, for this to work you must already have a field in your Model table
that is related to the Make table.

 

[PeterTow]

Could not get this to work I did get someone to do some code for me directly
into the data base and this works, both boxes have the right data in, but
now it does not fill in the boxes on my form they all remain blank - this is
the code I am using
Private Sub cboMake_AfterUpdate()
With Me.cboModel = ""
Me.cboModel.Requery
Me.Requery
End With
End Sub

Private Sub cboModel_AfterUpdate()
Me.Requery
End Sub