Three dependent combo boxes bound to same table

[mcgwn]

I have an access form in which users enter data for a given site. Various subforms populate detailed site attribute tables based on the site id. One such table ("coordinates") includes three ways of designating the 7.5 minute topographic quad on which the site is located. Possible values for these are provided in a lookup table ("quads") and are the local (Arizona) name, the local number, and the USGS code, e.g.:





My users may only know one of these, but I need all three written to "coordinates" with the site ID. I am trying to accomplish this using three combo boxes with row sources that change on click or update events:

Option Compare Database


Private Sub AZname_Click()
Me.AZname.RowSource = "SELECT AZname FROM Quads"
End Sub


Private Sub AZname_AfterUpdate()
Me.USGSname.RowSource = "SELECT USGSname FROM Quads WHERE AZname = '" & Me.AZname & "'"
Me.USGSname = Me.USGSname.ItemData(0)
Me.AZnumber.RowSource = "SELECT AZnumber FROM Quads WHERE AZname = '" & Me.AZname & "'"
Me.AZnumber = Me.AZnumber.ItemData(0)
End Sub


Private Sub AZnumber_Click()
Me.AZnumber.RowSource = "SELECT AZnumber FROM Quads"
End Sub


Private Sub AZnumber_AfterUpdate()
Me.USGSname.RowSource = "SELECT USGSname FROM Quads WHERE AZnumber = '" & Me.AZnumber & "'"
Me.USGSname = Me.USGSname.ItemData(0)
Me.AZname.RowSource = "SELECT AZname FROM Quads WHERE AZnumber = '" & Me.AZnumber & "'"
Me.AZname = Me.AZname.ItemData(0)
End Sub


Private Sub USGSname_Click()
Me.USGSname.RowSource = "SELECT USGSname FROM Quads"
End Sub


Private Sub USGSname_AfterUpdate()
Me.AZname.RowSource = "SELECT AZname FROM Quads WHERE USGSname = '" & Me.USGSname & "'"
Me.AZname = Me.AZname.ItemData(0)
Me.AZnumber.RowSource = "SELECT AZnumber FROM Quads WHERE USGSname = '" & Me.USGSname & "'"
Me.AZnumber = Me.AZnumber.ItemData(0)


End Sub

I want a selection on any of the fields to autopopulate the others, but also for the unfiltered row source list to be shown when any of the fields are clicked, even if they have already been populated by the initial selection in that or another field. Currently, you can select a value for one field, which then sets the other values, but the row source lists remain filtered when clicked.

[Gicu]

For each of the three combos ensure they have in their row sources all three columns (set their column count to three and the column widths appropriately), then in the AfterUpdate event use the column(i) to update the other two.

If you have "SELECT USGSname, AZName, AZNumber FROM Quads Order By USGSname;" as the row source for USGSname then:

Private Sub USGSname_AfterUpdate()
Me.AZname = Me.USGSname.Column(1)
Me.AZnumber = Me.USGSname.COlumn(2)
End Sub

Leave the rowsources alone in code, do not touch them after you set them in design view.
Cheers,