Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple combo boxes on a search form (Access 2003)

    Hello,

    I am trying to use the criteria in multiple combo boxes (unbound) on a form to open up another form based on that criteria. Can get it to work on one criteria but not using multiple criteria. Can anyone help?

    Thanks as always.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple combo boxes on a search form (Access 2003)

    You haven't really provided enough information to get any help. How are you opening the form, from the AfterUpdate of one of the comboboxes, from a button, or what? When you say "multiple criteria", do you mean that the form or its underlying query is using the controls to filter its recordset?
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    The database is of companies. I have a number of combo boxes on a form in which users can select criteria e.g. business category and city. Once they have selected this criteria they click a command button to open another form which display information about the relevant companies e.g. they may have selected ARCHITECTS in LONDON.
    The code i have attached to the command button is as follows:

    Dim strmainform As String
    Dim strmaincriteria As String
    Dim Strsubcriteria As String
    ' Name of the main form
    strmainform = "Businesses by locations FRM"
    ' Criteria for main form
    strmaincriteria = "[BCATBL Business Category] = " & Chr(34) & Combo0 & Chr(34)
    ' Criteria for subform
    Strsubcriteria = "[BLTBL City / town] = " & Chr(34) & Combo6 & Chr(34)
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strmainform, , , strmaincriteria
    ' Filter the main form
    With Forms(strmainform)
    .Form.Filter = Strsubcriteria
    .Form.FilterOn = True
    End With

    Hope that's a bit clearer.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple combo boxes on a search form (Access 2003)

    OK, I'll ignore the business of filtering the subform and assume you have a reason for doing it that way instead of using Master and Child links. You build the multiple expression where condition roughly like this:

    <fieldname1> = condition1 <OPERATOR> <fieldname2> = condition2.

    The operator could be AND or OR, depending on whether you want to require both conditions to be met or merely one of them at a time.

    <code>strmaincriteria = "[BCATBL Business Category] = " & Chr(34) & Combo0 & Chr(34) & " OR "[BCATBL Business Category] = " & Chr(34) & Combo1 & Chr(34) </code>


    gives you a filter where the single field specified matches either of two comboboxes on your criteria form. Does that help?

    If by "multiple criteria" you were referring to trying to filter the subform, then I would suggest you use the master/child links instead.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    You can put the two conditions in the main critreria :

    Dim strmainform As String
    Dim strmaincriteria As String
    ' Name of the main form
    strmainform = "Businesses by locations FRM"
    ' Criteria for main form
    strmaincriteria = "[BCATBL Business Category] = " & Chr(34) & Combo0 & Chr(34)
    strmaincriteria = strmaincriteria & " AND [BLTBL City / town] = " & Chr(34) & Combo6 & Chr(34)
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strmainform, , , strmaincriteria
    Francois

  6. #6
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    Thanks that works now.

    I may also add other criteria boxes as well as the existing two - do i just add another line with strmaincriteria = strmaincriteria & " AND etc. for each one.

    I would also like this to work if only one set of criteria is selected i.e. not requiring both to be selected. At the moment if one box is left blank, it looks for a blank in the records and therefore doesn't find anything. How would i amend the code to do this?

    Thank you.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    Use this :

    Dim strmainform As String
    Dim strmaincriteria As String
    ' Name of the main form
    strmainform = "Businesses by locations FRM"
    ' Criteria for main form
    If Len(Me.Combo0)>0 Then
    strmaincriteria = "[BCATBL Business Category] = " & Chr(34) & Combo0 & Chr(34)
    End If
    If Len(Me.Combo6)>0 Then
    strmaincriteria = strmaincriteria & " AND [BLTBL City / town] = " & Chr(34) & Combo6 & Chr(34)
    End If
    'Remove the AND if the first criteria was left blank.
    If Left(strmaincriteria,5) = " AND " Then
    strmaincriteria = Mid(strmaincriteria,6)
    End If
    ' Open the main form with WhereCondition
    DoCmd.OpenForm strmainform, , , strmaincriteria
    Francois

  8. #8
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    Great - that works fine.

    If i add more criteria boxes do i just add another line for 'IF LEN...... 'etc. etc and then the criteria??

    Would also like to add a box where users can enter the criteria for a postcode e.g. LS* to show all postcodes beginning with LS etc. just as you would in a query criteria. Would be great if this could include multiples too - e.g. LS* AND M*. Is this possible?

    How do you set up boxes like this in general? i.e. so users can enter criteria using AND / OR / NOT etc as in a query rather than selecting from a combo box as i have now.

    Thanks again.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple combo boxes on a search form (Access 2003)

    You can change the criteria line to something like this :
    strmaincriteria = strmaincriteria & " AND [BLTBL City / town] Like " & Chr(34) & Combo6 & "*" & Chr(34)

    For And, Or and Not you can use a combobox with those values and build the string with this combobox. Let say that you name this combobox cmbAndOrNot.

    If Len(Me.Combo0)>0 Then
    strmaincriteria = "[BCATBL Business Category] = " & Chr(34) & Combo0 & Chr(34)
    End If
    strmaincriteria = strmaincriteria & cmbAndOrNot
    If Len(Me.Combo6)>0 Then
    strmaincriteria = strmaincriteria & " [BLTBL City / town] = " & Chr(34) & Combo6 & Chr(34)
    End If
    Francois

Posting Permissions

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