Results 1 to 3 of 3
  1. #1
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402

    Question filter subform based on option selection

    Hi All, I have a form that I would like to filter by the selection in an option group. This works ok if I select one of the two companies from the option group, form query uses this to filter records. I would also like to select ALL from the option group and see all records listed, How can I do this?



    Private Sub Frame1296_AfterUpdate()
    Select Case Frame1296
    Case 1
    Me.Frame1296.Tag = DLookup("[company Title]", "[application data]", " [id] = 4") ' show records from company 1
    Case 2
    Me.Frame1296.Tag = DLookup("[company Title]", "[application data]", " [id] = 5") ' show records from company 2
    Case 3
    Me.Frame1296.Tag = "" ' show all records
    End Select
    Me.Consignment_Note_Tracking___Incoming.Requery
    Me.Consignment_Note_Tracking___Outgoing.Requery
    End Sub


    SELECT [Consignment Note Tracking - Outgoing].*, [Consignment Note Tracking - Outgoing].[from company]
    FROM [Consignment Note Tracking - Outgoing]
    WHERE ((([Consignment Note Tracking - Outgoing].[from company])=[Forms]![Main Menu]![Frame1296].[tag]));

    How can I include both companies if I select option 3, at the moment it comes up empty.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try LIKE operator and wildcard.

    WHERE [Consignment Note Tracking - Outgoing].[from company] LIKE [Forms]![Main Menu]![Frame1296].[tag] & "*";


    Instead of parameterized query and using Tag property, could set form Filter property.
    Last edited by June7; 03-06-2014 at 07:21 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    I have tried many combinations of using like and also using the iif command to no good, so I just added unfiltered queries and changed between the recordsource's to get the result I need. See Sig Below!

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

Similar Threads

  1. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  2. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  3. Replies: 5
    Last Post: 10-06-2010, 07:28 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

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