Results 1 to 14 of 14
  1. #1
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35

    Using 2 search fields in a form

    I currently have a form with a multiselect field to select criteria for a report. I got a lot of help from some great folks in this forum to create the code below. How do I insert code to add an additional field to select by. For instance, the user needs to be able to select data by 2 criteria : Manufacturer and Model

    Option Compare Database
    Private Sub ClearList_Click()


    Dim varItm As Variant


    With MasterCustomerSelect




    For Each varItm In .ItemsSelected
    .Selected(varItm) = False
    Next varItm


    End With
    End Sub




    Private Sub MasterCustomerReport_Click()
    Dim criteria As String
    Dim i As Integer

    'loop through listbox and build a filter string
    With Me.MasterCustomerSelect
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    criteria = criteria & """" & .ItemData(i) & """, "
    End If
    Next i
    End With

    'check the criteria we built
    If criteria <> "" Then
    'remove the trailing ', ' from the filter string
    criteria = Left(criteria, Len(criteria) - 2)
    Else
    'it appears nothing was selected
    MsgBox "Please select one or more customers"
    Exit Sub
    End If

    'Debug.Print criteria
    DoCmd.OpenReport "Master Customer Report", acViewPreview, , "[MASTER CUSTOMER] IN (" & criteria & ")"

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Build two string variables so you end up with:

    "[MASTER CUSTOMER] IN (" & criteria & ") AND [SecondField] IN (" & SecondVariable & ")"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    I am not a great coder (yet). So I have a couple of errors. They are highlighted in the attached file. I need to be able to clear both drop-down lists on the form and be able to search on both. I had to zip the MS Word file for some odd reason
    Attached Files Attached Files

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What I'm saying is to duplicate the bit building the criteria variable for the existing listbox with a second variable for the second listbox. Then put them together as I suggested.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    That's the thing. I'm not good at this. I have it set for Manufacturer, but I need to add Model in there (ModelSearch). Need to also clear both search lists when I click the Clear button

    Option Compare Database
    Private Sub ClearList_Click()


    Dim varItm As Variant


    With ManufacturerSearch


    For Each varItm In .ItemsSelected
    .Selected(varItm) = False
    Next varItm


    End With
    End Sub




    Private Sub Manufacturer_Model_Click()
    Dim criteria As String
    Dim i As Integer

    'loop through listbox and build a filter string
    With Me.ManufacturerSearch
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    criteria = criteria & """" & .ItemData(i) & """, "
    End If
    Next i
    End With

    'check the criteria we built
    If criteria <> "" Then
    'remove the trailing ', ' from the filter string
    criteria = Left(criteria, Len(criteria) - 2)
    Else
    'it appears nothing was selected
    MsgBox "Please select one or more States"
    Exit Sub
    End If

    'Debug.Print criteria
    DoCmd.OpenReport "Manufacturer_Model", acViewPreview, , "[Manufacturer] IN (" & criteria & ")"

    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I have to leave shortly for the afternoon, so hopefully someone else can write the code for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's what Paul expected you to use, I hope:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub ClearList_Click()
    Dim varItm As Variant
    
    
    With ManufacturerSearch
        For Each varItm In .ItemsSelected
        .Selected(varItm) = False
        Next varItm
    End With
    'do same for this listbox
    With Me.ModelSearch
        For Each varItm In .ItemsSelected
        .Selected(varItm) = False
        Next varItm
    End With
    End Sub
    
    
    Private Sub Manufacturer_Model_Click()
    Dim Mfgcriteria As String
    Dim ModCriteria As String
    Dim i As Integer
    
    
    'loop through listbox and build a filter string
    With Me.ManufacturerSearch
        For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Mfgcriteria = Mfgcriteria & """" & .ItemData(i) & """, "
        End If
    Next i
    End With
    'do same for other listbox
    With Me.ModelSearch
        For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Modcriteria = Modcriteria & """" & .ItemData(i) & """, "
        End If
    Next i
    End With
    'check the Mfgcriteria we built
    If Mfgcriteria <> "" Then
        'remove the trailing ', ' from the filter string
        Mfgcriteria = Left(Mfgcriteria, Len(Mfgcriteria) - 2)
    Else
        'it appears no Manufacturer was selected
        MsgBox "Please select one or more States"
        Exit Sub
    End If
    'check the Modcriteria we built
    If Modcriteria <> "" Then
        'remove the trailing ', ' from the filter string
        Modcriteria = Left(Modcriteria, Len(Modgcriteria) - 2)
    Else
        'it appears no Model was selected
        MsgBox "Please select one or more Models"
        Exit Sub
    End If
    'Debug.Print MfgCriterai
    'Debug.Print Modcriteria
    DoCmd.OpenReport "Manufacturer_Model", acViewPreview, , "[Manufacturer] IN (" & Mfgcriteria & ") AND Model_Name IN (" & Modcriteria & ")"
    End Sub
    I had to guess at Model_Name above. Change to whatever name you have.
    Last edited by davegri; 11-29-2021 at 01:52 PM. Reason: Model_Name note

  8. #8
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    This works for clearing the 2 lists, etc. However, when I click the Run Report button, the report doesn't run. If I put a macro On Click to open report, it doesn't use the filters per the form. If I put Code On Click, it doesn't do anything when I click the Run Report button

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Do you have Option Explicit as second line of code?
    I suspect not, as there are some spelling errors in the posted air code that Option Explicit would have caught.
    For example,

    Code:
    If Modcriteria <> "" Then
    	'remove the trailing ', ' from the filter string
    	Modcriteria = Left(Modcriteria, Len(Modgcriteria) - 2)
    Troubleshooting code at long range can be time consuming and frustrating for all involved.
    For all we know at this point, the basic problem might be in the report!
    If you can post the DB, I think we can clear up the problem in quick fashion.

  10. #10
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35

    Db copy

    Attached is a copy of the database with dummy data. The form is question is Manufacturer_Model and Report name is Manufacturer_Model
    Attached Files Attached Files

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    dawn-davegri-v01-Customer Turbine Database.zip

    Lookup table Turbine Models had spaces in the turbine names. I removed them so as to match the turbine names in Master Customer Report Query, the recordsource of the report.
    The command button, with caption Run Report had its button name property wrong, not matching the click event name in the VBA.
    Got the data names corrected in the VBA to match the actual table (query) names.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I used the dB Dave posted (Post #11)... I didn't want to have to re-invent anything.

    I couldn't stand all of what I consider issues (no PK field in tables, using spaces, punctuation and special characters in object names, no relationship window), so I made a lot of changes.

    I think I have everything working.

    There is a button on two of the forms "Add New Master Customer" named "Command66" - not a very good name but I realized just now that I didn't change the name.

    Note the "ss" at the end of the dB name.
    Attached Files Attached Files

  13. #13
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    Thanks Dave ! I always miss the little things. As a note, the actual turbine model names in my databases do have spaces, etc. That is what their correct identification is. Huge help as usual !

  14. #14
    DAWNY2007 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    35
    Thanks Steve. I have always been bad with the naming conventions. However, my previous Access experience has been more with queries / macros. Have not done a lot of coding, so this is all pretty new to me. I wrote a lot of SQL in my past life (Oracle SQL, MS SQL, etc), but have never dug into VB, so this is definitely a great learning experience for me!

    Thanks for the help and pointers !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 07-22-2017, 05:15 PM
  2. Search Form drop down fields not working
    By wnicole in forum Access
    Replies: 1
    Last Post: 09-19-2013, 07:43 AM
  3. Search form fields in alpha order
    By snowboarder234 in forum Forms
    Replies: 1
    Last Post: 11-02-2012, 12:03 PM
  4. How to Search Fields from Multi-Tables Form?
    By Yeisha2008 in forum Queries
    Replies: 9
    Last Post: 07-19-2012, 11:41 AM
  5. Display Fields in Search Form
    By j2curtis64 in forum Forms
    Replies: 1
    Last Post: 07-18-2011, 01:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums