I have the following code:

Private Sub FAFA4_Change()
FAFA5.Value = ""
FAFA5.Requery
FAFA23.Value = ""
FAFA23.Requery
End Sub

The objects are ComboBoxes. My purpose is to have the second box (FAFA5) popluated based on the choice in the first box (FAFA4), and so on.

This code works with one major glitch - it applies itself to all records rather than just the one that was changed.
Thus, if someone changes the choice in ComboBox FAFA4 for record 2, all the other records also have their other ComboBoxes cleared and requeried.

How to I modify the code to only apply to the ComboBoxes for the record that was changed?

Thanks,

Bryan Brassell

Bryan A. Brassell

Can you give us a little more detail?

Are these combo boxes all listed in the same record, or does the table that these all belong to have just the couple fields?

Basically, can you send the list of the fields in the recordset? I'm sure I could help if I had more information.

John

Bryan,

First, don't do this on a continuous form. That will eliminate the "all or nothing" problem you're having. Use a single form that you can scroll record-by-record with using record selectors.

As for the comboboxes changing their rowsources based on the selection of other comboboxes, that's not too hard once you know what SQL language to write. Suppose, for example, you have Me.cboChoice1 and Me.cboChoice2. You would reset Me.cboChoice2's rowsource on Me.cboChoice1's AfterUpdate event:
   What exactly to use for the SQL statement depends on Choice1's relationship with Choice2.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division