Results 1 to 6 of 6
  1. #1
    JanGray is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    2

    Form using combo boxes to populate table meeting criteria from those combo boxes

    I hope this makes sense. I'd like to know the best method for creating a form with combo boxes for multiple criteria which then populates a table (in a subform? split form?) showing records that meet any or all of the criteria chosen in the combo boxes. I've attached a couple of images of what I've attempted thus far. The single criteria selection works but I'd like to do up to 4 criteria. Is this possible? Do I need to use SQL or VBA to do this?
    Attached Thumbnails Attached Thumbnails single criterion.png   multiple criteria.png  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it would be easier to put the controls in a continuous form, in the header. no subform.
    the form shows all records.
    then user fills out the search items, clicks SINGLE FIND button.
    then the records filter:

    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looking at the right hand form, you only need one "Run Search" button. the search button would have code similar to the Search Criteria code by Allen Browne

    But it looks like the fields in the left hand form "ReferencedProducts" and "Components" are "Multi-Value Fields". MVFs violate the rules of normalization. You are going to have many problems because of the MVFs.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    some tips/code here might be useful
    http://allenbrowne.com/ser-62.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You could use Instr([Components] & ",",[cboComponents] & ",")>0 to identify the right records for the fields that hold concatenated data. I added the comma to avoid false positives if some strings are substrings of others (like in XPES and XPES+).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The single criteria selection works but I'd like to do up to 4 criteria.
    I took that to mean 4 different fields, not 4 values for criteria for one field. In the latter case, wouldn't LIKE work? Or perhaps IN(criteria,criteria...)?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-27-2018, 07:00 PM
  2. Replies: 1
    Last Post: 09-25-2018, 04:07 PM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  5. Replies: 0
    Last Post: 12-02-2010, 11:59 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
  •  
Other Forums: Microsoft Office Forums