cascading combo box issue

[ShostyFan]

This is my statement:

Dim InsName As String


InsName = "SELECT [Tbl_RentalRate].[Instrument]," & _
" [Tbl_RentalRate].[RentalType]," & _
" [Tbl_RentalRate].[RentalRate] " & _
"FROM Tbl_RentalRate " & _
"WHERE [Instrument] = " & Me.cmboInstrument.Value
Me.cmboRentalType.RowSource = InsName
Me.cmboRentalType.Requery


I select the instrument form cmboInstrument all is good, when I go to cmboRentalType to view the filtered list, a Parameter Value box opens and I have to type the value already selected in cmboInstrument field to get the filter to work.
What is going wrong with the statement that the parameter value box is opening?



Thanks.

[ranman256]

you dont need to use .value, (Me.cmboInstrument.value) value is the default property.

you cant use Me, in a query. ME is for a form to see objects in the form. DONT USE 'ME' AT ALL.
in a query you must use the full path to the form object:
forms!frmFormName!cmboRentalType

you dont need to rebuild the sql every time. just assign a query to the combo.
cmboRentaltype.rowsource = "qsQuery2"

in the query qsQuery2, it has that sql that looks at the combo. NO need to keep rebuilding it.

[ItsMe]

In your SELECT statement, you have the RentalRate field twice.

Maybe something like ...

 Dim strInsName As String

 strInsName = "SELECT [Tbl_RentalRate].[Instrument]," & _
              " [Tbl_RentalRate].[RentalRate]" & _
              " FROM Tbl_RentalRate" & _
              " WHERE [Instrument] = " & Me.cmboInstrument.Value
 
 Me.cmboRentalType.RowSource = strInsName

 Me.cmboRentalType.Requery

[ShostyFan]

Thanks Ranman, your suggested made for a much cleaner solution that is working great.