Results 1 to 8 of 8

Looking for a good search example using multiple comboboxs

  1. #1
    gint32 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    69

    Looking for a good search example using multiple comboboxs

    Hi,



    I was wondering if anyone out there can point me to a good example of a form that has search facilities, So as the from returns only what selected on the form via a few comboboxs &/or radio buttons or by some-other easier means

    Many thanks in advance

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,030
    You build your own Where clause depending on what they enter. Then update your RecordSource with the new sql line. In the AfterUpdate event of each combo box or search field, put = QueryDate()
    Below is an example with various types of search field and also fuzzy searching fields with *. I removed a few lines so might not work asis but gives you the idea.

    Public Function QueryData()

    Dim strSQL As String
    Dim strWhere As String
    Dim x As Variant
    Dim db As Database, rst1 As Recordset, rst2 As Recordset
    Dim strsqlproject As String, strsqlrisk As String, strsqlstrategy As String

    'Create "Where: statement from all the criteria boxes that they enter data into
    'on the form.

    If Not IsNull(Me!ProjectID) Then strWhere = strWhere & " AND ProjectID=" & Me!ProjectID
    If Not IsNull(Me!ProjectDescription) Then strWhere = strWhere & " AND ProjectDescription Like '*" & Me!ProjectDescription & "*'"

    If Not IsNull(Me!RiskStatusDateLT) Then strWhere = strWhere & " AND RiskStatusDate<=#" & Me!RiskStatusDateLT & "#"
    If Not IsNull(Me!RiskStatusDateGT) Then strWhere = strWhere & " AND RiskStatusDate>=#" & Me!RiskStatusDateeGT & "#"
    If Not IsNull(Me!RiskAvoidedDateLT) Then strWhere = strWhere & " AND RiskAvoidedDate<=#" & Me!RiskAvoidedDateLT & "#"
    If Not IsNull(Me!RiskAvoidedDateGT) Then strWhere = strWhere & " AND RiskAvoidedDate>=#" & Me!RiskAvoidedDateGT & "#"

    If Not IsNull(Me!Results) Then strWhere = strWhere & " AND Results Like '*" & Me!Results & "*'"

    If Not IsNull(Me!StatusDateLT) Then strWhere = strWhere & " AND StatusDate<=#" & Me!StatusDateLT & "#"
    If Not IsNull(Me!StatusDateGT) Then strWhere = strWhere & " AND StatusDate>=#" & Me!StatusDateGT & "#"

    'Create SQL statement
    strsqlproject = "Select ProjectID, ProjectName from qryAllData"

    ' 'Trim leading " AND " from the where clause
    If Not IsNull(strWhere) And strWhere <> "" Then
    strsqlproject = strsqlproject & " WHERE " & Mid$(strWhere, 6)
    End If

    strsqlproject = strsqlproject & " GROUP BY qryAllData.ProjectID, qryAllData.ProjectName"

    strsqlproject = strsqlproject & " ORDER BY qryAllData.ProjectName"

    Forms![frmMain]![frmQuery].Form![ProjectList].RowSource = strsqlproject
    Forms![frmMain]![frmQuery].Form![ProjectList].Requery


    End Function

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    This might be helpful:

    Search criteria (Click Here)

    Boyd Trimmell aka Hitechcoach
    Database Architect and Problem Solver
    Microsoft MVP - Access Expert
    25+ years specializing in Accounting, Inventory, and CRM systems
    "If technology doesn't work for people, then it doesn't work."

  4. #4
    ssanfu is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,630
    And here is an article by Allen Browne on searching.
    http://www.allenbrowne.com/ser-62.html

    The code is at
    http://www.allenbrowne.com/ser-62code.html


    I use this a lot.
    The code is easily modified....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    gint32 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    69

    good search example via form

    Quote Originally Posted by ssanfu View Post
    And here is an article by Allen Browne on searching.
    http://www.allenbrowne.com/ser-62.html

    The code is at
    http://www.allenbrowne.com/ser-62code.html


    I use this a lot.
    The code is easily modified....

    Thanks Everyone for your input, the suggestion will defo get me started!!

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Quote Originally Posted by ssanfu View Post
    And here is an article by Allen Browne on searching.
    http://www.allenbrowne.com/ser-62.html

    The code is at
    http://www.allenbrowne.com/ser-62code.html


    I use this a lot.
    The code is easily modified....
    Steve,

    I see we like the same example.
    Boyd Trimmell aka Hitechcoach
    Database Architect and Problem Solver
    Microsoft MVP - Access Expert
    25+ years specializing in Accounting, Inventory, and CRM systems
    "If technology doesn't work for people, then it doesn't work."

  7. #7
    ssanfu is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,630
    Looks like 3 minutes apart....


    Great minds think alike??????
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,030
    Bah, I beat you guys by like 11 hours, although by no means do I claim to have a Great mind

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

Similar Threads

  1. Replies: 1
    Last Post: 11-30-2016, 02:41 AM
  2. Replies: 3
    Last Post: 09-12-2016, 10:49 AM
  3. Replies: 8
    Last Post: 09-02-2015, 02:00 PM
  4. Replies: 6
    Last Post: 02-26-2014, 04:06 PM
  5. Replies: 12
    Last Post: 03-22-2012, 01:48 AM

Posting Permissions

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