Updating the Row Source Property (1 Viewer)

[girobert]

I have a form with five combo boxes that are all related in a single table. I have a form that creates a dynamic query, and the row source property calls off of this query. This works really well when populating the combo boxes for the first time (ie: When a user chooses a choice in combo box "A", all of the following comboboxes are populated only with results that are applicable to the choice in the first combobox. I tried to design it so that the combo boxes were continuously updated depending on the users choice. I did this by using an event procedure on the LostFocus and GotFocus events that created the dynamic query. Then the Row source was one of the fields in the dynamic query.

My question is: Is it possible to make the Row Source property update every time something on the form is changed? If I choose an option in combobox "a", and then look at the options in combobox "b", and then go back and change my choice in combo box "a", the choices in combobox "b" stay the same. If I look at the dynamic query the form creates, it has all of the choices that should be there, so the row source is not looking at the query again. Thanks

 

[Robert Dunstan]

Have you tried coding the RowSource property for each of your combo boxes by using the OnChange event in Combo box a?

 

[girobert]

Sorry Robert, I am not sure I understand exactly what you mean. Should I copy my code from the GotFocus event to the On Change event? Here is the code I have that creates the dynamic query:

Private Sub Combo29_GotFocus()

Dim db As Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Brk")
On Error GoTo 0

where = Null

where = where & " AND [Breaker #]= '" + Me![Combo29] + "'"
where = where & " AND [Station Name]= '" + Me![Combo31] + "'"
where = where & " AND [Manufacture]= '" + Me![Combo33] + "'"
where = where & " AND [kV Class] = '" + Me![Combo35] + "'"
where = where & " AND [Year of Mfg] = '" + Me![Combo37] + "'"

Set QD = db.CreateQueryDef("Dynamic_Brk", "Select Distinct * from [breaker info] " & (" where " + Mid(where, 6)))

End Sub

One of the places that I think I may be having trouble is the deletion of the dynamic query. I have this exact same code for the Click, GotFocus, and LostFocus events (may be a little redundant).