Results 1 to 6 of 6
  1. #1
    roger-trigg
    Guest

    Filtering with a combo box (97)

    Excuse a non-programmer daring to enter but I'm trying to filter a set of records using a combo box. Followng an article in Inside Access (vol3, 7, page 5) I've got the code below as the after update event for the combo box
    :
    DoCmd ShowAllRecords
    DoCmd ApplyFilter , "[Supplier] = Forms!SupplierItems!Search"

    where Supplier is the field I want to filter on the form 'SupplierItems' and Search is the name of the combo box.

    When I complie this code I get an error 'Variable not defined' with 'ShowAllRecords' highlighted. I guess my model was written in Access 2 days. is this my problem? Does anyone have the fix please?

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering with a combo box (97)

    Your code is incomplete in that your are doing 2 commands at the same time, your either want to display all records, or you want to filter the records. Try


    ' Return record(s) that match value selected in Search combo box.
    If Len(Me!Search) = 0 Then
    DoCmd.ShowAllRecords
    Else
    DoCmd.ApplyFilter , "[Supplier] = Forms!SupplierItems!Search"
    End If

    NB: Assumes search is the combo box field on the current form.

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

    Re: Filtering with a combo box (97)

    you have to add dot after the domcmd and split the where condition as follow

    <pre>DoCmd.ShowAllRecords
    DoCmd.ApplyFilter , "[Supplier] = '" & Forms!SupplierItems!Search & "'" </pre>


    If supplierItem is the name of your form and search is the name of the combo, even simpler:
    <pre>DoCmd.ShowAllRecords
    DoCmd.ApplyFilter , "[Supplier] = '" & Me!Search & "'" </pre>


    Even the line DoCmd .ShowAllRecords is not necessary
    Francois

  4. #4
    roger-trigg
    Guest

    Re: Filtering with a combo box (97)

    Bang on target, Francois. Many thanks indeed.
    Roger

  5. #5
    roger-trigg
    Guest

    Re: Filtering with a combo box (97)

    Thanks for the repsonse, Caz, but I followed Francois on this occasion and got a result.
    Roger

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering with a combo box (97)

    Actually it'd be "Dot on target.". Ba dum dum!

    (A little VBA humor there, very little: !'s are referred to as Bangs, .'s are referred to as Dots.)
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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