Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2015
    Posts
    6

    Unanswered: Help Adding Combo box to Multi Search

    Hello All,

    It has been awhile since I have worked in Access and Visual Basic and even then was a novice. I am putting together a simple contact directory for my organization that uses forms to search and view detailed records for each contact. While I have been able to adapt some code for a Dynamic Multi Search to view applicable records in a list box on the same form, I am having trouble adding a combo box to the search parameters. Where I think I am running into issues is that the combo box is to separate based on which of 34 groups the individuals belong to (ex. QC Contact, Programming and Support, etc.) . These fields are Yes/No.

    Any help greatly appreciated. Thanks!




    Sample Contactsdb.zip

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The query you use to search will have the combo in the criteria. It must have the full path to the combo,so use the BUILDER to get the correct path...like,
    Forms!frmFind!cboClient.

    But if you search form uses MANY boxes, then you will need vb code to inspect each control to see if it is needed in the where clause....
    Code:
    If not isnull(cboClient) then sWhere = "[clientid]= " & cboClient & " and "
    If not isnull(cboCity) then sWhere = sWhere & "[city]='" & cboCity & "' and "
    '....

  3. #3
    Join Date
    Jun 2015
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    The query you use to search will have the combo in the criteria. It must have the full path to the combo,so use the BUILDER to get the correct path...like,
    Forms!frmFind!cboClient.

    But if you search form uses MANY boxes, then you will need vb code to inspect each control to see if it is needed in the where clause....
    Code:
    If not isnull(cboClient) then sWhere = "[clientid]= " & cboClient & " and "
    If not isnull(cboCity) then sWhere = sWhere & "[city]='" & cboCity & "' and "
    '....
    The query I am using is:

    [Forms].[FRM_SearchMulti].[Groupbox]

    and points to each of the fields with a Yes/No checkbox. To clarify, there is only 1 combo box, but the content in there will search back to 34 fields of check boxes.

    In the vb, I have the following:

    Code:
    Private Sub Groupbox_Change()
        
        Me.SearchResults.Requery
        Me.SearchResults = Me.SearchResults.ItemData(1)
        Me.SearchFor.SetFocus
        
    End Sub

    The textbox search works perfectly, however, any search through the combo box yields zero results.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see the query you are running
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2015
    Posts
    6
    Quote Originally Posted by healdem View Post
    so can we see the query you are running
    From my end, it shows that I attached a sanitized zip version of the file in my initial post. (Sample Contactsdb.zip)

    If it is not visible, let me know and I can try to attach the file again.

    Thanks!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aaargh
    Im not even going to try and work out what that query is doing


    ...if it were me I'd probably re consider the tabel design.
    push the boolean values into a child table hanging off the snap-contact_master table
    use another table to define the boolean values. the chiold tabel tabel woudl then become an intersection table between those two.

    mind you I'd also probably want to rename my tabels and columnns to make certain that
    I didn't use reserved words or symbols
    I was consistent in my naming (either CamelCase OR all_lower_case_with_underscores)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2015
    Posts
    6
    Quote Originally Posted by healdem View Post
    aaargh
    Im not even going to try and work out what that query is doing
    Limited knowledge base and a fair bit of rust, I am lucky I got to this point.

    The initial parameters of the query are specifically searching for anything entered in the textbox.

    Code:
    Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
    The second group on criteria were my feeble attempt at triggering the combobox part of the query. In this, all fields with a checkbox answer type are given:

    Code:
    [Forms]![FRM_SearchMulti]![Groupbox]
    The textbox search is working without issue, but the combobox section is yielding zero results.

    Thanks for the reminder about naming conventions. I will probably go back and clean up when finished.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the query is:-
    Code:
    SELECT SNAP_Contact_master.ContactID, SNAP_Contact_master.First, SNAP_Contact_master.Last, SNAP_Contact_master.[STATE Office], SNAP_Contact_master.Title, SNAP_Contact_master.Office, SNAP_Contact_master.[Sub Office], SNAP_Contact_master.Phone, SNAP_Contact_master.[E-mail link]
    FROM SNAP_Contact_master
    WHERE (((SNAP_Contact_master.ContactID) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.First) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.Last) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.[STATE Office]) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.Title) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.Office) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.[Sub Office]) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.Phone) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.[E-mail link]) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((SNAP_Contact_master.[Urban 14])=[Forms]![FRM_SearchMulti]![Groupbox])) OR (((SNAP_Contact_master.[State SNAP Directors])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Midwest Partners Board of Directors])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Midwest Partners Steering Committee])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[CAP Contacts])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[State Connectivity])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[QC Contacts])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[QC State Directors])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[QC Statisticians])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Programming and Data Support])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Research and Statistic Directors])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Cert Policy Contacts])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Client Complaints])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Commissioners/ Directors/ Secretaries])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Client Integrity])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Whistleblower Complaints])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[SNAP-Ed State Contacts])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[SNAP-Ed Implementing Agency Coordinators])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[OIG Investigations])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.EBT)=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[E&T])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[FNS-583])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[D-SNAP  FNS-292 Report])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Civil Rights])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Outreach Contacts])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.Claims)=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[ADP/MIS])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Fair Hearing])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[SNAP System /IT])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[Internal Audits])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[State ME])=[Forms].[FRM_SearchMulti].[Groupbox])) OR (((SNAP_Contact_master.[FNS-834])=[Forms].[FRM_SearchMulti].[Groupbox]));
    I think the problem is how you are doing the comaprison between the boolean value in the table and the value fromt he list/combo groupbox

    groupbox returns a string so its never EVER going to be a match with a boolean value in the table. given your current design the only way I can see going forward is to test if the groupbox value equals a string match AND the column = true

    eg
    Code:
    where (groupbox = 'State SNAP Directors' AND SNAP_Contact_master.[State SNAP Directors] = vbtrue) or
    (groupbox = 'Midwest Partners Board of Directors' AND SNAP_Contact_master.[Midwest Partners Board of Directors] = vbtrue) or...
    ...for each and every one of your 32, yes 32! columns

    you could refine the design (slightly by having a table for the grousp (or simply addign an index to the rowsource
    so
    rowsource for groupbox becomes "1;State SNAP Directors;2;Midwest Partners Board Members;3;Midwest Partners Steering ......

    Code:
    where (groupbox.value = 1 AND SNAP_Contact_master.[State SNAP Directors] = vbtrue) or
    (groupbox.value = 2 AND SNAP_Contact_master.[Midwest Partners Board of Directors] = vbtrue) or...
    ...for each and every one of your 32, yes 32! columns

    frankly is a mess no matter which way you do it. a pig to maintain


    as a comment you are alwasy better off with this sort of thing proving the design works before copying it to umpteen different columns.
    another advanatge of a redesign, aside from maintenance, debugging and so on is that you can offer users the choice of more than one item

    so the users woudl put say 3 filters 'on' ie has to be true for those 3 elements,
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just thinking out aloud...
    assuming that
    1) you only wanted to select a siungle category colum,n
    2) the catergory column names in the tabel are the same as in the combo box
    ..then
    ..you could simplify the query by substituting the name of the column with the combo box value
    you'd probably have to write the query on the fly, but soemthing like:-

    strWhereClause =" where SNAP_Contact_master." & groupbox.text & " = " vbtrue

    ...or refining it a bit store the column name AND the description in the cimbo and pull the column anme as required from the combo
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2015
    Posts
    6
    Quote Originally Posted by healdem View Post
    just thinking out aloud...
    assuming that
    1) you only wanted to select a siungle category colum,n
    2) the catergory column names in the tabel are the same as in the combo box
    ..then
    ..you could simplify the query by substituting the name of the column with the combo box value
    you'd probably have to write the query on the fly, but soemthing like:-

    strWhereClause =" where SNAP_Contact_master." & groupbox.text & " = " vbtrue

    ...or refining it a bit store the column name AND the description in the cimbo and pull the column anme as required from the combo
    First, thank you very much for digging into this, I really appreciate it.

    1) Users will likely either search through the text box or select only one group from the combo box by which to identify contacts in the list box below.
    2) I plan on the combo names matching the field names

    Forgive me for being an idiot, but where should I be looking to add this?
    Code:
    strWhereClause  =" where SNAP_Contact_master." & groupbox.text & " = " vbtrue
    Thanks again!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you write the query in the fly, then assign that query to teh sub forms rowsource

    you will need to build the rest of the query includign the selct my, colum,n, list bit
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2015
    Posts
    6
    Are either of these anywhere near the right track?

    Code:
    Private Sub Groupbox_Change()
    
     Dim db As Database
        Dim qdf As QueryDef
        Dim strSQL As String
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryGroup")
        
        strSQL = "Select Snap_Contact_master.* FROM SNAP_Contact_master"
        Select Case Me.Groupbox
            Case "cboUsed"
                strSQL = "where SNAP_Contact_master.groupbox.Text & " = vbTrue
        End Select
          Me.SearchResults = Me.SearchResults.ItemData(1)
          Me.SearchResults.SetFocus
    
        DoCmd.Requery
    
    End Sub
    Code:
    Private Sub Groupbox_Change()
    
     Dim db As Database
        Dim qdf As QueryDef
        Dim strSQL As String
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryGroup")
        
        sqlQry = "Select SNAP_Contact_Master.*"
            Select Case Me.Groupbox
                Case "Urban 14"
                    sqlQry = Me.Groupbox.SelText & " WHERE Urban14 = True"
                Case "Claims"
                    sqlQry = sqlQry & " WHERE Claims = vbtrue"
                Case "EBT"
                    sqlQry = sqlQry & " WHERE EBT = vbtrue"
                Case Else
                    sqlQry = sqlQry & " WHERE Urban14 = vbtrue Or Claims = vbtrue Or EBT = vbtrue"
            End Select
      
         Me.SearchResults = Me.SearchResults.ItemData(1)
         Me.SearchResults.SetFocus
    
        DoCmd.Requery
    
    End Sub
    I have added an option box that allows users to select whether they want to search by individual (using textbox) or by group (combo box). Once that selection is made, the rowsource for the list box changes queries. In each of the cases above, no filtering of the results occurs.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •