Search two forms using subform

  • Thread starter Thread starter ollyculverhouse
  • Start date Start date
O

ollyculverhouse

Hi,

I have a courses form which displays information on different courses.
Within this I have a subform which contains the information about the
master details of each course.
(You can have many courses under one set of master details, like a
global details for that type of course)

I have created search forms for my other forms by just querying one
form and applying the query as the forms filter.
However as this is two forms and i need to be able to search using the
information in the subform, how would I do it?

I dont mind if i have to use SQL directly, however my vba coding
ability is the weakness. I know some VBA but havent used SQL directly
with VBA before.
If someone could point me on the right track i would be grateful.

Thanks
 
I like starting out in the query grid, creating the query I want. Then I use
View SQL and copy and paste that into VBA.

Then it's a matter of adding quoates, line break symbols, spaces, and
replacing double quotes with single quotes on strings, and adding & signs to
things:

Here's the SQL I copied from the grid:

SELECT tblSQL.TextField, tblSQL.NumberField
FROM tblSQL
WHERE (((tblSQL.TextField)="me") AND ((tblSQL.NumberField)=1));

Here's how I would make a statement out of it:

mySQL = "SELECT tblSQL.TextField, tblSQL.NumberField" & _
" FROM tblSQL" & _
" WHERE (((tblSQL.TextField)='me') AND ((tblSQL.NumberField)=1));"

Does this help?

If the WHERE statement was using a field from a form, you'd be using the &
sign I was talking about:

" WHERE (((tblSQL.TextField)=" & [Forms]![frmMyForm]![MyField] &") AND
((tblSQL.NumberField)=1));"

Hope this gets you on your way.
 
Thank you for the reply. It was very useful :)

Would i then just assign the sql to the record source of the form i
want (which includes the subform) and would the subform automatically
fill with the correct data?
Access101 said:
I like starting out in the query grid, creating the query I want. Then I use
View SQL and copy and paste that into VBA.

Then it's a matter of adding quoates, line break symbols, spaces, and
replacing double quotes with single quotes on strings, and adding & signs to
things:

Here's the SQL I copied from the grid:

SELECT tblSQL.TextField, tblSQL.NumberField
FROM tblSQL
WHERE (((tblSQL.TextField)="me") AND ((tblSQL.NumberField)=1));

Here's how I would make a statement out of it:

mySQL = "SELECT tblSQL.TextField, tblSQL.NumberField" & _
" FROM tblSQL" & _
" WHERE (((tblSQL.TextField)='me') AND ((tblSQL.NumberField)=1));"

Does this help?

If the WHERE statement was using a field from a form, you'd be using the &
sign I was talking about:

" WHERE (((tblSQL.TextField)=" & [Forms]![frmMyForm]![MyField] &") AND
((tblSQL.NumberField)=1));"

Hope this gets you on your way.

[email protected] said:
Hi,

I have a courses form which displays information on different courses.
Within this I have a subform which contains the information about the
master details of each course.
(You can have many courses under one set of master details, like a
global details for that type of course)

I have created search forms for my other forms by just querying one
form and applying the query as the forms filter.
However as this is two forms and i need to be able to search using the
information in the subform, how would I do it?

I dont mind if i have to use SQL directly, however my vba coding
ability is the weakness. I know some VBA but havent used SQL directly
with VBA before.
If someone could point me on the right track i would be grateful.

Thanks
Click to expand...
Click to expand...
 
Back
Top