[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Open form based on multiple filters.

Posted on 2007-10-19
10
Medium Priority
?
567 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:Smilesxl
  • 6
  • 4
10 Comments
 
LVL 2

Author Comment

by:Smilesxl
ID: 20111478
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
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 20111724
Getting run time error 3075
syntax error.  
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 20111985

Never mind.... I needed to add quotes around the value and it worked fine.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:harfang
ID: 20113515
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°)
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 20115754

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


0
 
LVL 58

Expert Comment

by:harfang
ID: 20115807
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°)
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 20115826
No, wait. Your code is a bit wrong, isn't it? Does it compile at all? I was thinking along these lines:

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

Sub FilterUpdate()

    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

End Sub

... or something like that.
(°v°)
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 20115903
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?
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 20115917
Never mind.  I found the problem.  Something was spelled wrong.  Thank you so much!
0
 
LVL 58

Expert Comment

by:harfang
ID: 20116071
Great! Success with your project! -- (°v°)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month17 days, 23 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question