Filter my subform using search results.

Not sure if this is a simple one or a complicated one, but here goes:

I have a subform whose recordsource is a simple SQL statement. The data in this subform is therefore already filtered. This is the SQL of that subforms recordsource.

SELECT tblJob.jobid, tblJob.jobraiseddate, tblJob.jobdatecompleted, tblClient.name, tblJob.clientref, tblJob.jobshortdescription, tblJob.jobstatus
FROM tblEmployee INNER JOIN (tblClient INNER JOIN tblJob ON tblClient.clientid = tblJob.client) ON tblEmployee.employeeid = tblJob.jobraisedby
WHERE (((tblJob.jobstatus)="Complete"))
ORDER BY tblJob.jobid DESC;

On my main form, I have a search box. I'm not too hot on Access but I have managed to write a query that searches multiple fields and returns a list of records which have my search term in. This is the SQL that gives me the results based on that search box:

SELECT tblJob.jobid, tblJob.jobshortdescription, tblJob.clientref, tblJob.contactsurname, tblJob.joblongdescription, tblJob.contactpostcode, *
FROM tblJob
WHERE (((tblJob.jobshortdescription) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*"))) OR (((tblJob.clientref) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*"))) OR (((tblJob.contactsurname) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*"))) OR (((tblJob.joblongdescription) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*"))) OR (((tblJob.contactpostcode) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")));


There is no child / parent relationship between form and subform.

The question is, how do I go about filtering my subform's contents to include only those records that my query returns?


Hope that makes sense!


LVL 1
AbacusInfoTechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jefftwilleyCommented:
in the after update of your search box, create the SELECT in code then its simple to say

dim sSQL as string
sSQL = "SELECT tblJob.jobid, tblJob.jobshortdescription, tblJob.clientref, tblJob.contactsurname, tblJob.joblongdescription, tblJob.contactpostcode, *
FROM tblJob
WHERE (((tblJob.jobshortdescription) Like ('*" & [Forms]![frmMainJobs]![txtSearchBox] & "*'"))) OR (((tblJob.clientref) Like ("*'" & [Forms]![frmMainJobs]![txtSearchBox] & "*'"))) OR (((tblJob.contactsurname) Like ("'*" & [Forms]![frmMainJobs]![txtSearchBox] & "*'"))) OR (((tblJob.joblongdescription) Like ("'*" & [Forms]![frmMainJobs]![txtSearchBox] & "*'"))) OR (((tblJob.contactpostcode) Like ("'*" & [Forms]![frmMainJobs]![txtSearchBox] & "*'")))"

me.subformName.recordsource = sSQL

J
AbacusInfoTechAuthor Commented:
Thank you.... I have stripped out the single quotes from my code, and I think I am a bit further but now get

Run Time Error '13' Type Mismatch.

I guess this is something to do with my SQL not matching the fields in the subform exactly...?
Richard DanekeTrainerCommented:
I think that Run Time Error '13' Type Mismatch is more often a field type issue than a naming issue.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AbacusInfoTechAuthor Commented:
Getting there. I think I'll crack it. I have changed my query a bit and now changing the subforms recordsource to the query. Will keep you posted.
AbacusInfoTechAuthor Commented:
Okay, this is probably not the way to do it, but it has progressed things a bit. I have written an Access query that includes all the required fields in the subform and references the search facility. My code is now this:

DoCmd.OpenQuery "qryJobSearchInProgress"
DoCmd.Minimize
Forms!frmMainJobs!fsubJobsInProgress.Form.RecordSource = "qryJobSearchInProgress"

This actually works and filters my subform but it is not graceful or pretty! It opens the query, minimises it and changes the subforms recordsource to this data set. See Code PART 1.

However if I try and use that SQL on a button I still get a  Run Time Error '13' Type Mismatch, See Code PART 2.

Should I stick with my use of an Access Query and making it prettier, or should I persevere with the code on button approach do you think?

PART 1 (SQL FROM THE ACCESS GENERATED QUERY "qryJobSearchInProgress")

SELECT tblJob.jobid, tblClient.name, tblJob.jobraiseddate, tblJob.jobshortdescription, tblJob.clientref, tblJob.contactsurname, tblJob.joblongdescription, tblJob.contactpostcode, tblJob.client, tblJob.jobstatus, *
FROM tblClient INNER JOIN tblJob ON tblClient.clientid = tblJob.client
WHERE (((tblJob.jobshortdescription) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")) AND ((tblJob.jobstatus)="In Progress")) OR (((tblJob.clientref) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")) AND ((tblJob.jobstatus)="In Progress")) OR (((tblJob.contactsurname) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")) AND ((tblJob.jobstatus)="In Progress")) OR (((tblJob.joblongdescription) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")) AND ((tblJob.jobstatus)="In Progress")) OR (((tblJob.contactpostcode) Like ("*" & [Forms]![frmMainJobs]![txtSearchBox] & "*")) AND ((tblJob.jobstatus)="In Progress"));


PART 2 (WHEN I USE ABOVE SQL ON A BUTTON I GET Run Time Error '13' Type Mismatch)

Dim strsql As String
strsql = "SELECT tblJob.jobid, tblClient.name, tblJob.jobraiseddate, tblJob.jobshortdescription, tblJob.clientref, tblJob.contactsurname, tblJob.joblongdescription, tblJob.contactpostcode, tblJob.client, tblJob.jobstatus, * FROM tblClient INNER JOIN tblJob ON tblClient.clientid = tblJob.client WHERE (((tblJob.jobshortdescription) Like (" * " & [Forms]![frmMainJobs]![txtSearchBox] & " * ")) AND ((tblJob.jobstatus)= " & "In Progress" & " )) OR (((tblJob.clientref) Like (" * " & [Forms]![frmMainJobs]![txtSearchBox] & " * ")) AND ((tblJob.jobstatus)=" & "In Progress" & ")) OR (((tblJob.contactsurname) Like (" * " & [Forms]![frmMainJobs]![txtSearchBox] & " * ")) AND ((tblJob.jobstatus)=" & "In Progress" & ")) OR (((tblJob.joblongdescription) Like (" * " & [Forms]![frmMainJobs]![txtSearchBox] & " * ")) AND ((tblJob.jobstatus)=" & "In Progress" & ")) OR (((tblJob.contactpostcode) Like (" * " & [Forms]![frmMainJobs]![txtSearchBox] & " * ")) AND ((tblJob.jobstatus)=" & "In Progress" & "));"
Forms!frmMainJobs!fsubJobsInProgress.Form.RecordSource = strsql

Open in new window

ldunscombeCommented:
If you have a saved query (Which it appears you have "qryJobSearchInProgress") then you can simply use that for the recordsource of your subform.

You don't need to open the query and then minimize it so the following two lines are not required.
DoCmd.OpenQuery "qryJobSearchInProgress"
DoCmd.Minimize


simply put this line in the click event of your button

Forms!frmMainJobs!fsubJobsInProgress.Form.RecordSource = "qryJobSearchInProgress"

You may also want to add to requery the subform data after you change your search.
Forms!frmMainJobs!fsubJobsInProgress.Form.requery

Leigh

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Try this:
Your search process results of a number of records in main form.
Create a recordset of the selected records.
Generate a string of the master field of the main form.

Select the subform records with child id in the selected master ids

Following example for tables a(aID, aDesc), b(aID, bID, bDesc)
Forms used ab_Main , recordsource =a, ab_subForm, recordsource=b

The following code can be used in after update of the search button, here it is tested in a button click event.

Private Sub cmdFilter_Click()
    Dim sql As String
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    Dim strMain As String
    rs.MoveFirst
    strMain = "''"
    Do While Not (rs.EOF)
        strMain = strMain & ",'" & rs("aID") & "'"
        rs.MoveNext
    Loop
    sql = "Select * from b where aID in (" & strMain & ")"
    ab_subForm.Form.RecordSource = sql
   
End Sub
AbacusInfoTechAuthor Commented:
Thanks, will look at this tomorrow.
AbacusInfoTechAuthor Commented:
Haven't forgotten you...! May get to it this week.
AbacusInfoTechAuthor Commented:
Hi, thanks all and sorry for the delay but yes Leigh had it spot on with the change to my code which I completely missed. Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.