Synchronized Combo Boxes

[Theresa]

Hi:

I have 2 combo boxes on a subform, cboRepl_Fixture and cboLamp. I have the
following code in the AfterUpdateEvent of cboRepl_Fixture:

Private Sub cboRepl_Fixture_AfterUpdate()
Me.cboLamp.RowSource = "SELECT Lamp FROM" & _
"Replacement_Lamp_tbl WHERE Repl_Fixture_ID = " & Me.cboRepl_Fixture
& _
"ORDER BY Lamp"
Me.cboLamp = Me.cboLamp.ItemData(0)
End Sub

When I view my subform, cboLamp is blank. I have adjusted the number of
columns to 2 and adjusted the width to see both, but it is still blank.

What am I doing wrong?

Thanks,

 

[Douglas J. Steele]

See whether putting a space before ORDER BY makes a difference:

Private Sub cboRepl_Fixture_AfterUpdate()
Me.cboLamp.RowSource = "SELECT Lamp FROM" & _
"Replacement_Lamp_tbl WHERE Repl_Fixture_ID = " & Me.cboRepl_Fixture & _
" ORDER BY Lamp"
Me.cboLamp = Me.cboLamp.ItemData(0)
End Sub

 

[Theresa]

No....it didn't seem to do anything.

See whether putting a space before ORDER BY makes a difference:

Private Sub cboRepl_Fixture_AfterUpdate()
Me.cboLamp.RowSource = "SELECT Lamp FROM" & _
"Replacement_Lamp_tbl WHERE Repl_Fixture_ID = " & Me.cboRepl_Fixture & _
" ORDER BY Lamp"
Me.cboLamp = Me.cboLamp.ItemData(0)
End Sub

 

[Graham Mandeno]

Hi Teresa

You will also need another space between "FROM" and "Replacement_Lamp_tbl".

Also, I assume that Repl_Fixture_ID is numeric, otherwise the value from
cboRepl_Fixture will need to be enclosed on quotes.

Another thing: you say you have "adjusted the number of columns to 2", but
your query is returning only one column.

A good debugging technique is to set a breakpoint at an appropriate place
(in this case, on the "Me.cboLamp =" line) and examine the relevant values.

In this case, ?cboLamp.RowSource would give:

SELECT Lamp FROMReplacement_Lamp_tbl WHERE Repl_Fixture_ID = 99 ORDER BY
Lamp

You can easily see that there is a space mssing. If the problem is not
obvious then you can copy the SQL string and paste it into a query, then try
to run the query. That will give you much better information on the cause
of the error.