Results 1 to 3 of 3
  1. #1
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76

    How to code different search boxes on same form


    Hi everyone

    So here's the run down, I have a search box on a split form that filters a datasheet based on whatever the user types in. The vba for the reset button is as follows:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdReset_Click()
    Me.txtSearch = ""
        Me.SrchText = ""
        DoCmd.Requery
        Me.txtSearch.SetFocus
    End Sub
    
    
    
    
    Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    End Sub
    
    
    
    
    Private Sub SrchText_AfterUpdate()
    Me.SrchText.Requery
    End Sub
    
    
    Private Sub txtSearch_Change()
    'Create a string (text) variable
    
    
        Dim vSearchString As String
        vSearchString = txtSearch.Text
        SrchText.Value = vSearchString
        If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
            Exit Sub
            End If
        'Me.SearchResults = Me.SearchResults.ItemData(1)
        
        'Me.SearchResults.SetFocus
        DoCmd.Requery
        Me.txtSearch.SetFocus
        If Not IsNull(Len(Me.txtSearch)) Then
            Me.txtSearch.SelStart = Len(Me.txtSearch)
            End If
    End Sub
    The form is based on a query and the search box looks specifically for agency name
    Click image for larger version. 

Name:	query.PNG 
Views:	12 
Size:	7.7 KB 
ID:	30414
    and the SQL is
    Code:
    SELECT [AgencyINFO-main].Agency, [AgencyINFO-main].Subsidary, [AgencyINFO-main].ProgramCodes, [AgencyINFO-main].ProgramNotes, [AgencyINFO-main].Address, [AgencyINFO-main].City, [AgencyINFO-main].Prov, [AgencyINFO-main].PostalCode, [AgencyINFO-main].FirstName, [AgencyINFO-main].LastName, [AgencyINFO-main].Position, [AgencyINFO-main].ContactNotes, [AgencyINFO-main].OriginalEmail, [AgencyINFO-main].PhoneNumber, [AgencyINFO-main].PhoneNotes, [AgencyINFO-main].AAExpiryDate, [AgencyINFO-main].InsuranceExpiry, [AgencyINFO-main].InsuranceExpiryComments, [AgencyINFO-main].WSIBYesNo, [AgencyINFO-main].WSIBNotes
    FROM [AgencyINFO-main]
    WHERE ((([AgencyINFO-main].Agency) Like "*" & [Forms]![frmAgencyLookup]![SrchText] & "*"));
    My question is, if I wanted to include one or more search box(es) where users type in a program code (e.g. 1312), what would the vba/sql look? I tried doing this in the SQL by simply adding Like "*" & [Forms]![frmAgencyLookup]![SrchText] & "*" under ProgramCodes' criteria. This did work however, I wasn't extracting all records and thus figured it's better to have separate searchboxes. Any thoughts and suggestions are always much appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to allow selection of multiple values as filter criteria on a single field, review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Thank you!

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

Similar Threads

  1. Replies: 2
    Last Post: 01-28-2016, 08:23 AM
  2. Allen Browne search code using check boxes
    By baronqueefington in forum Programming
    Replies: 2
    Last Post: 03-14-2015, 06:13 PM
  3. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  4. Search Form - Cascading Combo Boxes
    By WeeTerrier in forum Forms
    Replies: 7
    Last Post: 12-05-2011, 08:26 PM
  5. Search form with list boxes
    By scottay in forum Programming
    Replies: 15
    Last Post: 07-27-2010, 09:28 AM

Tags for this Thread

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