Filter Combo Box list based on a selection in another Combo Box

[WCStarks]

I have done this in a different context, so I thought I knew what to do it, but I think the form referencing may be messing me up. I have a form called Assets, which is a NavigationSubform. It has two combo boxes:
1) Category
2) Type
3) Both get their lists from tables.
4) The Asset_Types table, and the dependent Type combo box have two fields, Asset_Type and Asset_Cat. The value in Asset_Cat contains Category names the Asset_Types are associated with. So the WHERE clause needs to filter the Asset_Cat values against the value selected in the Category combo box.


Below, is the VB I have, that doesn't quite work. I get a not very helpful vague syntax error. Can someone help me make this work?

Private Sub Type_GotFocus()
    Me.[Type].RowSource = & _
    " SELECT [Asset_Types].[Asset_Type], [Asset_Types].[Asset_Cat]" & _
    " FROM [Asset_Types]" & _
    " WHERE [Asset_Types].[Asset_Cat] = " & me.[Category] & _
    " ORDER BY [Asset_Types].[Asset_Type];"
End Sub

[ranman256]

you dont need any sql or code. Both combos are assigned to a query.

the combo cbType uses cboCat in its query:
select AssetType from tAssetTypes where [cata] = forms!myForm!cboCata

then when user picks an item in cboCata, simply refresh cboType so the list sees the new choice:

sub cboCata_Afterupdate()
   cboType.requery
end sub

[WCStarks]

I tried your suggestion first thing this morning, and you are right, it works! I just added the Asset_Category field to the Asset_Type combo's internal query, and added the refresh as indicated. I actually tried it, initially, without using the Forms! syntax, and found it worked just fine without it. Thank you very much. It is nice to learn there is more than one way to "skin the cat". The other context where I did a similar On Got Focus Event procedure, was between parent and child forms. I tried borrowing from it, but couldn't get it to work right in this new context. In the other context, I sometimes needed to associate a Role with multiple Activities, so I needed to use the InStr() function. I don't think that will be the case here, in this new context.

[WCStarks]

Actually, I was mistaken, this did not work. I didn't look carefully enough. It so happened that the few types displayed in the lines allotted to the combo box, showed only those items alphabetically associated with that category, from the value that was already present in the Type combo box. I had played with an asset that had already been classified, and re-selected the category to cause the Requery, but when I checked further, no actual filtering occurred. I now realize that I hadn't done the expression as you specified, so I redid sql, generated from the Design View, as follows, but it errors on the Forms expression in the Where clause.

SELECT Asset_Types.*, Asset_Types.Asset_Type, Asset_Types.Asset_Cat, Asset_Categories.Asset_Category
FROM Asset_Types, Asset_Categories
WHERE (((Asset_Types.Asset_Cat)=[Forms]![Assets]![Category]))
ORDER BY Asset_Types.Asset_Type;

Perhaps I should restate, that the Assets form is in a NavigationSubForm, with the tab named "Assets".

I got to thinking that there may be a drawback in this approach anyway. For when one might want to change a type value for an existing category value, the refresh will not happen, to filter the list.

[June7]

Why are you using wildcard for fields? If using SQL statement in RowSource, don't use the full form path. Don't include Asset_Categories table.

Name comboboxes different from the fields, such as cboCat. Don't you need the Asset_ID field, assuming there is one? Or are you saving text value instead of number ID?

SELECT Asset_ID, Asset_Type FROM Asset_Types WHERE Asset_Cat=[cboCat] ORDER BY Asset_Type;

[WCStarks]

The Assets table has an ID, but I didn't create IDs for the lookup tables, since they didn't participate in the table relationships like the other main tables. So, the Asset_Types table doesn't have a number ID field. I notice that when Access created the combo box initially, it created a field named "*" in the control query builder, apparently as a place holder or something.

I renamed the two combos and went in and modified the On Got Focus event procedure as follows:

Private Sub cboType_GotFocus()
   Me.[Type].RowSource = & _
   " SELECT [Asset_Type],[Asset_Cat] " & _
   " FROM [Asset_Types] " & _
   " WHERE [Asset_Cat] = " & [cboCategory] & _
   " ORDER BY [Asset_Type];"
End Sub

But I still get "compile error, expected: expression", with the first "&" highlighted. I've tried the WHERE clausewith ' " & ' before the cboCategory as here, and the quote after the cboCategory, but it makes no difference. I don't understand why Access is reporting a compile error.

[June7]

My suggestion is to use SQL statement in combobo RowSource property in the form design, not set the RowSource with VBA.

If you prefer to use VBA to set RowSource, use the new combobox name for Type:

Private Sub cboType_GotFocus()
Me.cboType.RowSource = "SELECT [Asset_Type] FROM [Asset_Types] WHERE [Asset_Cat] = '" & Me.[cboCategory] & "' ORDER BY [Asset_Type];"
End Sub

If the value of cboCategory is a number type, remove the apostrophes.

You would not use & on the first line because not concatenating any text from that line, just use line continuation character (_).

[WCStarks]

Well, I finally got it working. I moved the Select statement up to the RowSource line without the & _ , fixed the Me.[Type] to Me.[cboType], and moved the quote in the WHERE clause as follows:

Private Sub cboType_GotFocus()
   Me.[cboType].RowSource = " SELECT [Asset_Type],[Asset_Cat] " & _
   " FROM [Asset_Types] " & _
   " WHERE [Asset_Cat] = [cboCategory]" & _
   " ORDER BY [Asset_Type];"
End Sub

I don't understand why moving the SELECT statement should make any difference, but it does.

I also don't understand what you meant by "Wildcard for fields". How was I doing that?

Thanks for your help

[June7]

In my previous post:

"You would not use & on the first line because not concatenating any text from that line, just use line continuation character (_)."

In your first post the sql statement used * wildcard to pull all fields.

SELECT Asset_Types.*,

Since you have included the cboCategory name within quote marks, this SQL construct is not dynamic. Might as well just put the SQL directly in the RowSource property then VBA only:

Me.cboType.Requery

[WCStarks]

Yes. I understand about the & now. As far as the * goes, as I mentioned above, I believe Access made up a pseudo-name field called *, in place of the missing Asset_Types ID. I didn't do it.

[June7]

Yes, sometimes Access will include the wildcard when query is built. If you look at the list of fields under a table in query design view you will see the * wildcard at the top. If you want all fields, this is a shortcut method to include and result is a shorter, easier to read SQL statement. Has nothing to do with a missing field, ID or otherwise.