More combo box woes

[Ed H]

This is a continuation of an previous request for help. I have one combo box
that when an item is selected, fills a second combo box. To complicate
things, this is in a subform (Member Vehicles) called by pressing a button
on main form (Members). The subform(Member Vehicles) has multiple records
of the same type (datasheet style). Combo Box 1 is Vehicle Make (Toyota).
When selected, combo box 2 (Models) is filled with the models for that
vehicle maker (Celica, Camry, RAV4). I am using the code attached to obtain
the models and load the combo box. The behaviour I'm seeing is that when I
choose a model in record 1, the same value is displayed in record 2s Model
combo box, an as of yet unfilled row (record). How do I stop record 2 combo
box Model from being populated, when I select a model in record 1?

Example
Combo Combo
Record Licence Make Model Colour Province
1 AABBCC Toyota Rav4 Blue On
2 Rav4

Dim STR_SQL As String
Dim cboType As ComboBox

STR_SQL = "SELECT TAB_VehicleMakeModel.FLD_VehicleModel FROM
TAB_VehicleMakers " & _
"INNER JOIN TAB_VehicleMakeModel ON
TAB_VehicleMakers.FLD_VehicleMake = " & _
"TAB_VehicleMakeModel.FLD_VehicleMake " & _
"WHERE (((TAB_VehicleMakers.FLD_VehicleMake)= '" &
Me.FLD_VehicleMake & "'))" & _
"ORDER BY TAB_VehicleMakeModel.FLD_VehicleModel;"
Set cboType = Me!cbo_VehicleModel
Rem With cboType < I commented this out, but to no avail
cboType.RowSourceType = "Table/Query"
cboType.RowSource = STR_SQL
cboType.ColumnCount = 1
Rem End With < same here

End Sub

 

[Tom Wickerath]

Hi Ed,

This sounds like this may fall under the following:

Tip #16: Data in a combo box control on a continuous form/datasheet disappears
http://www.fmsinc.com/free/tips.html#tip16foraccess

Also, see a post made by Access MVP Sandra Daigle (3/10/2006 in
microsoft.public.access):

http://www.microsoft.com/office/com...cess&mid=5bba3894-65ce-4714-8739-c069d9155fd3


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Ed H]

Tom, your the best. Sandra's method is clever but scary. I wonder what that
would be like to debug. Anyway, I will give both suggestions a try.

Ed