Update combo box list

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I'm trying to make one combo box on a form have its
rowsource updated based upon entry into another combo box.

The source table for the combo box values looks like this:

StatID, Stat, Stat2
1, Frozen, Freezer 1
2, Frozen, Freezer 2
3, Off-site, Germany
4, Off-site, England
5, In-process, n/a

The AfterUpdate code for the primary combobox is this:

Private Sub StatusCombo_AfterUpdate()
Me.Status2Combo.RowSource = "SELECT Stat2 from
PickTestTable where Stat = " & Me.StatusCombo & " Order by
Stat2"
Me.Status2Combo = Me.Status2Combo.ItemData(0)
End Sub

It keeps asking me for a parameter when I try to use the
first combobox. If I enter one of the Stat entries into
the parameter box, it actually works, obviously I don't
want to have to do this.

Can someone help?
..
 
It sounds like the problem is in the RowSource of StatusCombo. What is the
parameter you are being prompted for?

BTW, it looks like Stat is a text field, so your RowSource should look like
this:

Me.Status2Combo.RowSource = "SELECT Stat2 from PickTestTable where Stat = '"
& Me.StatusCombo & "' Order by Stat2"

If there is a possibility of a quote being in Stat, then use this:

Me.Status2Combo.RowSource = "SELECT Stat2 from PickTestTable where Stat = '"
& Replace(Me.StatusCombo, "'", "''") & "' Order by Stat2"
 
Back
Top