Form Filter Not Working; Prompts For Value (2 Viewers)

[B99]

Hi all, I have a form with a subform where there are combo boxes at the top to select one or more values and then filter the records returned on the subform based on the selected values. It uses the same logic/code as the demo in this UA post: Multi Value Fields (big thanks to theDBGuy for pointing me in the right direction).

The form and subform are fully designed and show all data by default (which is good). Then I started to slowly add code for one combo box on the After Update event but it doesn't filter the results. It also asks for a parameter when I change the value. Here are the details:

The main form has a record source of a temp table with no data (tblResponseQryTmp) and combo boxes to select the filter criteria (i.e. cboSelIndustry). The combo box row source is the table used to store the list of values.

The subform has a record source of the table that has all of the data and foreign keys (tblResponse) and combo boxes to display the results of the query (cboResIndustry). The subform is view only so all of the controls are "enabled = No".

Here is the code on the cboSelIndustry combo box After Update event:

Private Sub cboSelIndustry_AfterUpdate()
    Dim strLinkCriteria As String
    Dim strData As String
    If Me.Dirty Then Me.Dirty = False
    
    strData = Nz(DLookup("SelIndustry", "tblResponseQryTmp", "ID=1"), "")
    
    If strData <> "" Then
            strLinkCriteria = "cboResIndustry.Value = " & Replace(strData, ",", " AND cboResIndustry.Value = ") & " AND "
    End If
    
    If strLinkCriteria <> "" Then
        strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 5)
        
        Me.frmQuerySubForm.Form.Filter = strLinkCriteria
        Me.frmQuerySubForm.Form.FilterOn = True
    End If
End Sub

After putting a few debug statements in, the result of strLinkCriteria at the end is "cboResIndustry.Value = 6" and 6 is the ID that corresponds to the Industry value I selected.

I've tried changing the code to use cboSelIndustry.Value as well as the column name from the record source table but nothing seems to work. And now when I change the value of cboSelIndustry on the form, I get a prompt to enter a value for cboResIndustry.Value.

Where did I go wrong?

 

[Ranman256]

you cant use: cboResIndustry in a query.
if you use form items, you must use the full path : forms!fMyForm!cboResIndustry

you can use the control in sql if you let it resolve outside of the quotes:

sSql = "select * from table where [city]='" & me.cboCity & "'"

 

[theDBguy]

Hi. It might help if you could post a demo version of your db.

 

[Ranman256]

the query will prompt you if it doesn't understand the field. (which it cant without the full path)

 

[B99]

you cant use: cboResIndustry in a query.
if you use form items, you must use the full path : forms!fMyForm!cboResIndustry

you can use the control in sql if you let it resolve outside of the quotes:

sSql = "select * from table where [city]='" & me.cboCity & "'"

I don't think this is it. Using the demo database from the UA site, I added debug statements to the same code and it resolves to "Regions.Value = 1" and it works as expected there. ("Regions" is the name of the subform control, which corresponds to my cboResIndustry in this case.)

 

[B99]

Hi. It might help if you could post a demo version of your db.

I'll do that shortly; just need to clean up a few things.

the query will prompt you if it doesn't understand the field. (which it cant without the full path)

I'm sure this is it, I just don't understand why it doesn't understand in my case but it worked in the other demo.

 

[B99]

Here's a demo version of the database. The form in question is frmResponseQuery. There are only two records in the notes table at the moment and only the Industry combo box has any code. Changing the value of the Industry combo box to/from Energy or Government should filter/unfilter the 2 records.

 

[theDBguy]

Here's a demo version of the database. The form in question is frmResponseQuery. There are only two records in the notes table at the moment and only the Industry combo box has any code. Changing the value of the Industry combo box to/from Energy or Government should filter/unfilter the 2 records.

Give this a try.

 

[B99]

You did it again; thank you! This gets me over the hurdle. I'm still confused how it worked in Pere's demo DB when the criteria variable used the combo box and the version you have the variable uses the table column, but I will try to build off of this rather than recreate what he did.

Thanks theDBguy!

 

[theDBguy]

You did it again; thank you! This gets me over the hurdle. I'm still confused how it worked in Pere's demo DB when the criteria variable used the combo box and the version you have the variable uses the table column, but I will try to build off of this rather than recreate what he did.

Thanks theDBguy!

Oh, sorry about that. I'll check out his demo when I get a chance and let you know if I see anything.

 

[B99]

My mistake - in the demo DB, Pere named his subform combo box the same name as the table column and I incorrectly assumed that "Regions.Value" referred to the Regions combo box, not the Regions column in the record source table. I got confused because he used "States" for the column name in the Regions table (row source) and I figured he also used "States" in the record source table. So he did NOT use the combo box to set the variable.

Ranman256 - you were absolutely correct.

This is slowly making more sense! Thanks guys!