How do I use OpenForm's WHERE clause to find data in a subform. 1

[Albion]

I have a form frmComputers within that form is subform sfrmSoftware which contains all the software installed on each computer. I would like to be able to open frmComputer with the WHERE clause in DoCmd.OpenForm to show all computers which have a certain software title installed. Is it possible to do something like this:

stDocName = "frmComputers"

stLinkCriteria = "[Forms]![frmComputers]!
[sfrmSoftware].Form![SoftwareTitle] Like " & chr(34)
& "*Windows 2000*" & chr(34)

Do.Cmd OpenForm stDocName, , , stLinkCriteria

and have it search through all the records in sfrmSoftware under each computer for the title "Windows 2000"?

Any help would be appreciated.

-Al

 

[FancyPrairie]

The only way you would be able to do what you want is to have the RecordSource of the form referencing a query or SQL statement that joins the 2 tables.

 

[WayneRyan]

Albion,

I think that something like the following
might work for you. It would definitely work if
your recordsource was a query.


stDocName = "frmComputers"

stLinkCriteria = ' darn text wrapping ...
"[ParentTable].[Key] in " & _
"(Select Distinct([ChildTable].[Key]) " & _
" From [ChildTable] " & _
" Where [ChildTable].[SWTitle] like '*Windows 2000*');"

Do.Cmd OpenForm stDocName, , , stLinkCriteria

HTH,
Wayne

 

[FancyPrairie]

I like your idea WayneRyan. Hadn't thought about doing it that way. I'm going to have to go back and retro-fit my last database I created. When the user requested a specific set of data, I was changing the RecordSource property of the form (the new RecordSource consisted of a join operation). However, with your way I can just set the filter property of the form.

I'll give you a star.