combo Box filtering (1 Viewer)

I have a form that is used I to display tractor purchase price information. To get to the particular tractor I want to look at, I have three combo boxes that help me find the tractor I want.

The first box "Brand", displays a list of different tractor brands
The second box "Model", displays the different models we have in stock based on Brand
The third box is the "Model Description", displays the descriptions of the different model.
For Example:
Brand: Select John Deere from the list
Model: Select a JD Model (there are over 20 models) - (ie. 5100)
Model Description: For each model, there are different levels (there are 4 different 5100's)

Anyway, the first time I open this screen, the combo boxes are blank (although it has no bearing on the functionality, the screen displays the first record in the table (I would love to display a blank screen, but have not yet figured a way to display a blank screen)

On subsequent lookups, if I click the Brand, the model box clears, but the description box retains the previous lookup. I want to have both model and model description cleared as soon as I click Brand. Any ideas?

One way of setting a group of controls hidden is to use the Tag property
Set all those controls to have the same tag value e.g. X

NOTE: a similar approach can be used to enable/disable; lock/unlock a group of controls. See the example database here:
https://www.access-programmers.co.uk/forums/showthread.php?t=293584

In a standard module, add this procedure


In the Form Load event add this line


In the after update event for the first combo, add the line



For the second part of your queastion, add this line to the after update event of the brand combo
(replace with the name of your combo here)

To clear the other two comboboxes all you need is to add some code to the OnEnter event of the Brand combo box (of course use your control names for the three combo boxes):

Private Sub cboBrand_Enter()
Me.cboModel=Null
Me.cboModelDescription=Null
End Sub

Cheers,
Vlad

to initially set you form to
blank screen, add code to
the Form's Load Event to move
to new record:

Private Sub Form_Load()
DoCmd.GoToRecord ,,acNewRec
End Sub


on selecting new Brand and you
want to blank the other two combos,
add code the the Brand combo's
AfterUpdate Event:

Private Sub cboBrand_AfterUpdate()
Me.cboModel = ""
Me.cboDescription = ""
End Sub

I would store this information in three tables, for example:

tblBrand
BrandID
BrandName
tblModel
ModelID
BrandID
ModelName
tblDescription
DescriptionID
ModelID
DescriptionDetails

That would link everything together, you could add additional fields to tblDescription for anything else that you wanted to store to that particular tractor, engine details etc.

Then on the form for the model combo query, put a filter where the BrandID = Brand combo box, and then the same for the description. You would need to add an afterupdate event to the brand and model combo box so that it updates the next combo box in the chain. Or you could even have it so that if there is no value (isNull) then it updates them anyway and that will leave them blank.

If you added additional information to the description table, you could then have a sub form or other controls on the form that pull that additional information based on the description combo box.