Solved VBA syntax help for rowsource for a cascading combo box (1 Viewer)

[crazy_ivan_1]

Hi All,

I have been browsing here and stack exchange for help with syntax and getting my combo boxes to work on my form.

The first combo is ins_filter_cmb. This gets it record sources from a query but there is also a UNION to add an "All" at the top of the list. This is a list of instrument categories.

SELECT [tbl_instrument_cat].[instrument_category], [tbl_instrument_cat].[instrument_cat_ID] FROM tbl_instrument_cat UNION SELECT "All","0" FROM tbl_instrument_cat ORDER BY tbl_instrument_cat.instrument_cat_ID;

The second combo is ins_select_cmb. I am trying to create a filter that restricts this box based on the instrument category of the first box but only where the field instrument_steri_yes is true

This is my VBA for the second combo

Private Sub ins_filter_cmb_AfterUpdate()
If Me.ins_filter_cmb.Value = "All" Then
'If IsNull(Me.ins_filter_cmb.Value) Then
Me.ins_select_cmb.RowSource = "SELECT instrument_name, instrument_steriyes, instrument_category " & _
                                "FROM tbl_instruments " & _
                                "WHERE instrument_category = '*' " & _
                                " AND instrument_steriyes = '-1'"
Me.ins_select_cmb.Requery

Else
'Me.ins_select_cmb.Value = ""
'Me.ins_select_cmb.Requery
Me.ins_select_cmb.RowSource = "SELECT instrument_name, instrument_steriyes, instrument_category " & _
                                "FROM tbl_instruments " & _
                                "WHERE instrument_category = " & Me.ins_filter_cmb & _
                                " AND instrument_steriyes = '-1'"
Me.ins_select_cmb.Requery
End If
End Sub

What I am trying to do is:
If the first box says "All", then all the instruments are listed where instrument_steriyes is TRUE, otherwise the listed instruments are based on their category. The combo box only shows the instrument_ategory and the instrument_name

When I run the form, the All filter does not work at all and when I select a caterogy, I get an Input Prompt for the category (which is listed in the title of the prompt), where when I fill in the category name in the input field, it filters the combobox then.

I am trying to figure out where my syntax is incorrect.

 

[Uncle Gizmo]

Do you mean something like this?

Combo Filter < All> or Some

Combo Filter < All> or Some - Nifty Access

Combo Filtering You can set this up yourself by following the instructions below, or you can study the example file which can be downloaded from the Nifty Digital downloads site for a few dollars.. will normally appear as the first entry in your list because the angle bracket puts it at the...

www.niftyaccess.com

 

[pbaldy]

If you don't want to filter by the field, leave it out:

WHERE instrument_steriyes = '-1'"

By the way, if that's a Yes/No field you wouldn't want the single quotes around the -1.

 

[crazy_ivan_1]

Thanks for the replies. I got the "All" to work

The filter doesnt work in VBA for any other category. I still get an input prompt

 

[pbaldy]

The parameter prompt is Access telling you it can't find something (whatever is listed in the prompt). Double check your spelling. If you're still stuck, can you attach the db here?

 

[arnelgp]

Me.ins_select_cmb.RowSource = "SELECT instrument_name, instrument_steriyes, instrument_category " & _
"FROM tbl_instruments " & _
"WHERE instrument_category = '*' " & _
" AND instrument_steriyes = '-1'"

is [instrument_steriyes] a Yes/No field:

" And [instrument_steriyes] = -1;"

[ instrument_category] criteria should be changed to:

"WHERE [ instrument_category] Like '*' " & _

 

[pbaldy]

Me.ins_select_cmb.RowSource = "SELECT instrument_name, instrument_steriyes, instrument_category " & _
"FROM tbl_instruments " & _
"WHERE instrument_category = '*' " & _
" AND instrument_steriyes = '-1'"

is [instrument_steriyes] a Yes/No field:

" And [instrument_steriyes] = -1;"

[ instrument_category] criteria should be changed to:

"WHERE [ instrument_category] Like '*' " & _

I already mentioned the quotes if the data type was Yes/No. Since you're building the SQL in code anyway, why would you include a criteria on a field you didn't actually want to filter on? Why make the engine evaluate that field? Plus, that criteria will exclude Null values, which isn't desired.

 

[crazy_ivan_1]

hi all,

I figured out what was wrong.

I accidentally deleted the query from the row source so it was not generating the query.

Sorry for all the hassle.

Thanks for all the tips.