Filter a Form with a form

[kathi2005]

I have a form that sets the filter parameters for a query. That works great.



I want the form to filter the form that is based on that query.

the form I want for the results to show in is HDStock.

The form that ask for the criteria is HDSearch.

Right not I have HDStock set with the OnOpen FilterMe!Filter = filterString
Me!Filter = "cboSize = " & Forms![HDSearch]![cboSize] & "And cboPCD=" & "'"
Me!Filter = "cboPCD = " & Forms![HDSearch]![cboPCD] & "And cboOffset=" & "'"
Me!Filter = "cboOffset = " & Forms![HDSearch]![cboOffset] & "And cboColor=" & "'"
Me!Filter = "cboColor = " & Forms![HDSearch]![cboColor]

I get a run-time error '2465': HDSearch can't find the field 'Filter' referred to in you expression and the query pops up

Please help.

[pbaldy]

Try

Me.Filter

You might also need to put a space before the word "And..."

[kathi2005]

Did that
Now I get Run-Time '3075':
Syntax error in string query expression 'cboSize = 18 And cboPCD=".

[pbaldy]

You realize that each of those lines replaces the previous, so you're never providing a complete string? It appears you want this type of thing (presuming size is numeric and PCD is text):

Me!Filter = "cboSize = " & Forms![HDSearch]![cboSize] & " And cboPCD='" & Forms![HDSearch]![cboPCD] & "'"

[kathi2005]

Okay that makes sense I did not think about that.

So I have changed it to one line of code, but still getting a syntax error

Me!Filter = "cboSize = " & Forms![HDSearch]![cboSize] & " And cboPCD='" & Forms![HDSearch]![cboPCD] & "And cboOffset='" & Forms![HDSearch]![cboOffset] & "And cboColor='" & Forms![HDSearch]![cboColor]

Run-Time 2465
Size is Numeric
The other 3 are text

[pbaldy]

You're not closing off the single quotes around the values, and you're not putting a space in front of the "And". Try

Me.Filter = "cboSize = " & Forms![HDSearch]![cboSize] & " And cboPCD='" & Forms![HDSearch]![cboPCD] & "' And cboOffset='" & Forms![HDSearch]![cboOffset] & "' And cboColor='" & Forms![HDSearch]![cboColor] & "'"

[kathi2005]

Well at least the form opened this time, but nothing was filtered. All records showed up

[kathi2005]

Is it possible to be a mistake on the HDSearch form.

Here is the code for that

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "HDSearch"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenQuery "HDComplete", acViewNormal, acEdit
DoCmd.OpenForm "HDStock"
End Sub

[pbaldy]

Add this line right after the above:

Debug.Print Me.Filter

which will print out the finished filter to the VBA Immediate window. Does it look correct? Or can you post the db here?

[kathi2005]

Did that and nothing happened

[pbaldy]

Nothing will "happen", other than the text being sent to the Immediate window. See here if you're unfamiliar with where that is:

http://www.baldyweb.com/ImmediateWindow.htm

[kathi2005]

Seriously. I did not get another window. I tried again an no other window popped up.

db is to big for on here, currently I am able to use the database and running my inventory from it, so it is very large.

Owner wants me to dumb it down so everyone can use it, and not sure I can do that, but trying

[pbaldy]

No window will pop up. It should populate the Immediate window within the VBA editor, as pictured. Just minimize the VBA editor when you run the code, and then go check that window. If that window isn't already appearing in the VBA editor, click on View/Immediate Window.

[kathi2005]

ok Got it:
cboSize = 18 And cboPCD='5:114.3' And cboOffset='Mid' And cboColor='Black'


cboSize = 18 And cboPCD='5:114.3' And cboOffset='Mid' And cboColor='Black'

cboSize = 18 And cboPCD='5:114.3' And cboOffset='Mid' And cboColor='Black'

cboSize = 18 And cboPCD='5:114.3' And cboOffset='Mid' And cboColor='Black'

[pbaldy]

Syntax-wise that looks correct for the data types you mentioned. Are those the field names in the table? They look more like combo box names.

Wait a minute, I've had a "duh" moment. After the line setting the filter you need:

Me.FilterOn = True

to actually apply the filter.