Where Argument in OpenForm Method

[Petemush]

I have some code that generates a table(tblTempSearch) of existing values for a field(CCode). Now I want to open a form using the OpenForm method and in the where argument I want to specify only open records where CCode equals one of the generated values in tblTempSearch.

I tried it this way

Dim db As DAO.Database, rs As DAO.Recordset, fld As field

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTempSearch&quot
Set fld = rs.Fields("CCode&quot
'Open the form frmCourses.
DoCmd.OpenForm "frmCourses", , , "CCode = fld"

but it doesn't work. I just get asked for the parameter fld.

Any ideas? Bearing in mind I REALLY can't use the filter instead in this case.

Cheers,

Pete

 

[Petemush]

Okay solved that using the following:

DoCmd.OpenForm "frmCourses", , , "CCode = '" & fld & "'"

The problem is now that tblTempSearch has more than one record and this Where argument only checks the first one. How can I get it so it opens the form with all the records where the CCode equals any of the CCodes in tblTempSearch.

At the moment is just opens one record.

Cheers,

Pete

 

[AlexMidd]

Not sure what you are trying to do here. Is there a specific value of CCode that you want to match with, or are you, as your post seems to suggest, wanting ALL values of CCode to be shown? In whihc case, why do you need a filter?

Normally, to apply a filter, you would have a particular value that is obtained from somewhere and you want to show all records with the same value in one of the fields, e.g. the CCode field. Can you explain further? Have fun! )

Alex Middleton

 

[Petemush]

I've generated a table of CCodes.

I want to open a form and display all the records which correspond to each CCode.

So if I have 3 CCodes in the table tblTempSearch, then 3 records should be shown in the form. One for each CCode.

Will I have to generate a Where string using "Or" for each CCode in the table?

Cheers,

Pete

 

[Petemush]

Right, thinking about it, I should probaly explain what I'm trying to acheieve.

I have two tables. tblCourses and tblCourseVariables.

They are related and a record in tblCourses may have many related records in tblCourseVariables but a record in tblCourseVariables will have only one related record in tblCourses.

These records are related using the field CCode.

Rather than have a form frmCourses and a subForm frmCourseVariables, I opted to use two separate forms and have a button on frmCourses which opened frmCourseVariables and displayed the records relating to the record currently displayed in frmCourses.

I also have another form, frmFindCourses, where a user can select any field appearing in tblCourses or tblCourseVariables and enter some search criteria. 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 records related records in tblCourseVariables also have to satisfy the search criteria for any field in tblCourseVariables.

Sorry, I know this is very confusing!

I'm currently doing this by building two separate search strings, one for fields in tblCourses and one for fields in tblCourseVariables. What I thought I could do is create a table holding the appropriate records CCode generated by using the search string for tblCourseVariables.

This I have done.

The I thought I could open frmCourses showing the records related to the CCodes, and then apply the other search string for tblCourses as a filter.

I've got quite far but can handle it if someone says give up this impossible/there is a much better way.

Cheers,

Pete