Searching two forms

[Petemush]

This is a problem which I've been trying to solve in parts but think I might be going about the whole thing in the wrong way. Therfore am going to explain the entire scenario.

Actualy I already have done in the 4th reply to thread705-312332 . The actual problem is this part of the quoted thread.

When they click a button I wanted frmCourses to be displayed showing the records which satisfy the criteria for the fields in tblCourses. But these record's related records in tblCourseVariables also have to satisfy the search criteria for any field in tblCourseVariables.

I'm close to crying! What's the best way to search through a table for certain criteria and then make sure that the records produced have related records in another table which also satisfy some other criteria.

Thankyou thankyou thankyou to anyone who helps in the slightest!

Cheers,

Pete

 

[Petemush]

Okay, I think I've found a way to do this but it won't work:

I have the table of CCodes, and have created a query which gives the records of tblCourses where the CCode is equal to a CCode in the table of CCodes. Then, after I've opened frmCourses in VBA I reassign the recordsource to equal this query, then I do requery. But it doesn't work! The record source is correctly assigned(I've checked) and if I manually base the form on the query it also works fine. I realise changing the recordsource is only valid during runtime but I've made sure to change it after the form is open see code below:

DoCmd.SetWarnings False
If Not (strWhere = "()&quot Then
DoCmd.RunSQL "SELECT CCode INTO [tblTempSearch] FROM tblCourseVariables " & _
"WHERE " & strWhere
End If

DoCmd.OpenForm "frmCourses"
Form_frmCourses.RecordSource = "Query1"
Form_frmCourses.Requery

Can anyone help?

PLEASE?

Cheers,

Pete