Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    How to filter by filtered subform???? (Access 2003

    frmJobs has datasource tblJobs
    frmJobs subform sbfItems has datasource tblItems and is linked to frmJobs by JobNum

    I need to search for items that we have previously done

    I need to filter tblItems for specific words and then open frmJobs so that it shows only the jobs that have the searched text in the subform field.

    I used to have a description box on frmJobs and I would use the following syntax from a small form

    <font color=blue>If txtsearch1.Text = "" Then
    DoCmd.OpenForm "frmJobs", , , "((DESCRIPTION Like ""*" & txtSearch & "*""))"
    Else:
    DoCmd.OpenForm "frmJobs", , , "((DESCRIPTION Like ""*" & txtSearch & "*"")) and ((DESCRIPTION Like ""*" & txtsearch1 & "*""))"
    End If</font color=blue>

    but now the items are on separate lines in a different table via the subform.

    <font color=red>EDIT:</font color=red>
    Thought: if I use this sql

    <font color=blue>SELECT tblJobs.*, tblItems.ItemDesc
    FROM tblJobs INNER JOIN tblItems ON tblJobs.JobNum = tblItems.JobNum
    WHERE (((tblItems.ItemDesc) Like ""*" & txtSearch & "*""));</font color=blue>

    it gives me the filtered records that I need.

    Now, how do I open frmJobs with the above sql as the recordsource??

    Or am I going about it the wrong way?
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to filter by filtered subform???? (Access 2003

    See <post:=381,240>post 381,240</post:> for an explanation and example of how to do this.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to filter by filtered subform???? (Access

    Hans, your post above doesn't seem do what I want and I'm not sure how to modify it, So

    sticking with my original idea I have set up:

    <font color=blue> DoCmd.OpenForm "frmQuickCard"
    Forms!frmQuickCard.RecordSource = </font color=blue>

    which gives me what I want.

    Thanks anyway.
    "Heading for the deep end"

Posting Permissions

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