Results 1 to 10 of 10
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Need help with combo filter.

    I currently have a continuous form showing all records. I would like to add a combo box to this form to filter out records, but I keep getting 'type mismatch' when I use the following code:
    [Private Sub cboFilter_AfterUpdate()
    Dim ComboContents As Variant
    ComboContents = Me![cboFilter]
    If IsNull(ComboContents) Then
    Me.FilterOn = False
    Else
    Me.Filter = "[Raw Material] Like " * " & cboFilter & " * ""
    Me.FilterOn = True
    End If


    End Sub]

    If you look at my database. Select Where Used in the main menu and you'll see my combo box there.Project 6 (2).zip

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Like needs quotes:
    Like '*" & cboFilter & "*'"

    Add Me.Requery after filtering.

    Remove the use of ComboContents, it is making it needlessly complex.

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Great. It started working, but it's not filtering 'properly'. It's showing more than what is in the combo box. Thanks for the help Aytee111. Any idea why it's showing more than what is being searched for? Since it's a combo box, should I remove the like portion from the VBA? I tried Me.Filter = "[Raw Material] = '" & cboFilter & "'" but when I select an option, the continuous form goes blank. Meaning no records are found for any search I use.
    Last edited by lccrews; 09-18-2017 at 11:37 AM. Reason: Tried to change the VBA.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you mean by "showing more" - are there records being displayed which do not match the combobox entry?

    Note, the data type must match - if the Raw Material field on the table is text, the single quotes are required; if it is a number then do not use quotes. Like is used when you enter a partial entry (such as "Smith") and you want the filter to return all fields that have that name, regardless of the first name. If you want only exact matches, then use = )it is much faster too).

    Some people don't use form references, I always do - Me!cboFilter

    A filter is the same as a criteria in a query. You can test the exact format of this line by creating a new query and putting something into the criteria, you will see exactly how it should look.

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    There are records being displayed that have nothing in common with the combo box entry. I actually want to filter exactly what's in the combo box. I just can't get the combo box to function correctly. Sorry I don't know what a form reference is. Have you taken a look at my database? I also tried changing the record source of the form to add a critteria 'forms!Myform!combo' to the raw material field. That zero'd out the continuous form and rendered the combo box inoperable.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There are two columns on your row source, you are trying to compare Raw Material to ID, which is the first column. Either change the filter to filter on ID or change it to look at column(1).

    (Not sure if I can test your data, this database has a missing reference)

  7. #7
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Okay, by changing the filter to look at column 1, the button became a heck of a lot more accurate. I've attached a corrected database for you to see that if you search 227079 (letting it autofill the rest), nothing shows up. If you look at tbl_Formulas though there are definitely records with RM 227079.Project 6.zip

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The reason is that is does not like the "#" in the string (don't know why). I changed it to "*" and it worked fine. You can do that in VBA by:
    Replace(Me!cboFilter,"#","*")

  9. #9
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I fixed it by changing the vba to this: Me.Filter = "[Raw Material] ='" & cboFilter.Column(1) & "'"

    Thanks for teaching me a new trick aytee111

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Glad it is working.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2016, 04:14 AM
  2. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  3. Replies: 4
    Last Post: 06-18-2014, 08:31 PM
  4. Replies: 1
    Last Post: 10-01-2013, 09:25 PM
  5. Combo Box Filter
    By Richie27 in forum Programming
    Replies: 6
    Last Post: 04-24-2012, 02:53 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