asked on

Open form based on multiple filters.

I have a form that contains three drop down boxs [strPlantName], [strSupplierName], and [strManager]. I want to use these as filters to open another form that is tied to a table. If a box is blank then I want it to give me everything for that filter. Does anybody have any code that will do this.

Smilesxl

ASKER

I tried this, but it is not working.
Private Sub Command5_Click()
'On Error GoTo Err_Command5_Click
Dim strFilter As String

strFilter = IIf(Nz(Select1) = "", "strPlant like '*'", "strPlant=" & Select1)
strFilter = strFilter & IIf(Nz(select2) = "", " and strSupplierName like '*'", " and strSupplierName=" & select2)
strFilter = strFilter & IIf(Nz(select3) = "", " and strManager like '*'", " and strManager=" & select3)

DoCmd.OpenForm "draft_Update", acNormal, , strFilter
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

Smilesxl

ASKER

Getting run time error 3075
syntax error.

Smilesxl

ASKER

Never mind.... I needed to add quotes around the value and it worked fine.

Markus Fischer

You also need to look out for the " and " if the first box is Null. I would write it like this:

Function QuoteSQL(pvarText) As String

If IsNull(pvarText) _
Then QuoteSQL = "Null" _
Else QuoteSQL = "'" & Replace(pvarText, "'", "''") & "'"

End Function

...

Dim varFilter As Variant

varFilter = Null
If Not IsNull(Select1) Then _
varFilter = varFilter + " and " & "strPlant=" & QuoteSQL(Select1)
If Not IsNull(select2) Then _
varFilter = varFilter + " and " & "strSupplierName=" & QuoteSQL(select2)
If Not IsNull(select3) Then _
varFilter = varFilter + " and " & "strManager=" & QuoteSQL(select3)

...

You should also know that Like "*" does not return all records. It returns all non-Null records, much like "Is Not Null"

Cheers!
(°v°)

Smilesxl

ASKER

You are right it does not pick the blanks. I tried the code but I get an error "Run time error 13 - Type Mismatch on line:
varFilter = varFilter + " and " & "strPlant=" & QuoteSQL(PickPlant)

Here is the code:
Function FilterUpdate(pvarText) As String
If IsNull(pvarText) _
Then QuoteSQL = "Null" _
Dim varFilter As Variant
varFilter = Null

If Not IsNull(PickPlant) Then _
varFilter = varFilter + " and " & "strPlant=" & QuoteSQL(PickPlant)
If Not IsNull(PickSupplier) Then _
varFilter = varFilter + " and " & "strSupplierName=" & QuoteSQL(PickSupplier)
If Not IsNull(PickManager) Then _
varFilter = varFilter + " and " & "strManager=" & QuoteSQL(PickManager)

DoCmd.OpenForm "Update", acNormal, , varFilter

Exit_FilterUpdate:
Exit Function

End Function

Markus Fischer

When you get the error, choose [Debug]. While the offending line is still highlighted, open your immediate pane (Ctrl+G) and try out different parts of the line to find the problem. For example:

? QuoteSQL(PickPlant)
? PickPlant
? TypeName(PickPlant)
? varFilter

and finally:

? varFilter + " and " & "strPlant=" & QuoteSQL(PickPlant)

You can also try to change the declaration of QuoteSQL to:

Function QuoteSQL(ByVal pvarText) As String

although it shouldn't do a difference in the particular case, provided PickPlant is a valid control of the form.

(°v°)

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Smilesxl

ASKER

I tried two of the three filters and it seems to give me what I want when I check
? varFilter
strPlant='CSM'and strSupplierName='A RAYMOND INC'

but it still asks me for the Parameter value in a popup box instead of taking the string provided?

Smilesxl

ASKER

Never mind. I found the problem. Something was spelled wrong. Thank you so much!

Markus Fischer

Great! Success with your project! -- (°v°)