automatically show value in field based on previous field 1

[Achmed]

Hi everybody,

I'm trying to make a form that will allow users to select, for example, desired options in a new car. The first field is the make of car, so if they select Porsche from the Make list, the next field is a drop-down for Model, containing 911 and Boxter (this part works, I'm just basing the source data for the Model list on criteria using the Make selected). My problem is when it comes to the Engine field, I want the base engine to automatically be selected, and that drop-down to contain the optional engines as well as the base engine. I can make it so that the Engine drop down list contains this data, but I don't want to have to select an engine if an upgrade isn't desired. All of the data is stored in tables associating the various makes with the models, and the models with the base and optional engines. I hope this is clear! Thanks in advance.

-Alan

 

[papparuff]

In this example, I determine what the basic engine is after I select the Make of automobile. My engine table is a field that has a "b" for basic engine or an "o" for optional engine.

The name of the combobox that I select the auto make from is called cboAutoMake and I use it's After Update event to populate the combobox that is storing the appropriate engine sizes.
The combobox that holds the engine sizes is called cboEngineSize, and for this example, I've got a Value List with the following properties:

Row Source: "285 cu in";"o";"318 cu in";"b";"326 cu in";"o"
Column Count: 2
Column Widths: 1;0

Here is the code that will do what you are asking. Of course, you are going to have to make changes to meet your specific needs, but this is the general code you want to use.

Private Sub cboAutoMake_AfterUpdate()

Dim ctl As Control
Dim i As Integer
Set ctl = cboEngineSize

' Enumerate through each column and find the row
' that equals a "b" (for basic engine)
For i = 0 To cboEngineSize.ListCount - 1
' If the 2nd column of the cboEngineSize is equal
' to "b" then set the cboEngineSize value
' to the "b" row (engine size)
If cboEngineSize.Column(1, i) = "b" Then
cboEngineSize.Value = cboEngineSize.ItemData(i)
Exit For
End If
Next i


End Sub
John Ruff - The Eternal Optimist