Creating a filter box for fields in a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

on my unbound mainform i would like to have a box that controls the query
that the subform is based on. is there a way through code to tell fields in
the subform to be filtered on what is typed in text boxes on the mainform?

do i need to place any special stuff in the query? i know how to create
parameter queries, but this would seem to be more complicated than that.

any help is appreciated! :)
 
Hi -

I do just that, and my approach is to just change the subform's
recordsource using SQL, like this:

SubFormContainer.Form.RecordSource = SQL

where SQL selects all the required fields, with the WHERE clause doing
the filtering as required. Here's an example SQL from my application:

SELECT distinct [film], [frame], [picture number formatted], [topic],
[location], [loc2], [storagecde],[storage1] FROM [PHOTO LIST] WHERE
[word] like 'burton*' AND [location code] = '603'

The most important part is getting the where clause right - the selected
fields will be the same.

John
 
Hello

thanks for the reply. i think i see what you are getting at, the problem is
that i want the criteria to be based on the unbound text boxes that i have on
the main unbound form. from what you posted it looks like you are suggesting
that i do a qry that cant be changed by what people are typing in the unbound
text boxes on the unbound main form.

where do i put the SQL that you suggest?

where do i put the "SubFormContainer.Form.RecordSource = SQL"?

J. Goddard said:
Hi -

I do just that, and my approach is to just change the subform's
recordsource using SQL, like this:

SubFormContainer.Form.RecordSource = SQL

where SQL selects all the required fields, with the WHERE clause doing
the filtering as required. Here's an example SQL from my application:

SELECT distinct [film], [frame], [picture number formatted], [topic],
[location], [loc2], [storagecde],[storage1] FROM [PHOTO LIST] WHERE
[word] like 'burton*' AND [location code] = '603'

The most important part is getting the where clause right - the selected
fields will be the same.

John

Hello

on my unbound mainform i would like to have a box that controls the query
that the subform is based on. is there a way through code to tell fields in
the subform to be filtered on what is typed in text boxes on the mainform?

do i need to place any special stuff in the query? i know how to create
parameter queries, but this would seem to be more complicated than that.

any help is appreciated! :)
Click to expand...
Click to expand...
 
Hi -

Sorry, you are right - my example looks like I have hardcoded the values
in the where clause. Actually what I showed you was the SQL that I
created, based on (in this case) 2 form fields.

Here is what I did. Suppose I have two fields in the main form,
[Keyword] and [SearchLocation], each containing text data I want to use
in the WHERE clause of the SQL string. My code would be this:

SQL = "SELECT distinct [film], [frame], [picture number formatted],
[topic], [location], [loc2], [storagecde],[storage1] FROM [PHOTO LIST]
WHERE [word] like '" & me![Keyword] & "*' AND [location code] = '" &
me![SearchLocation] & "'"

me!SubFormContainer.Form.RecordSource = SQL

The two main fields [KeyWord] and [SearchLocation] don't have to be
bound to a table or query field.

A place to put the code would be in the On Click event of a command
button labelled "Search". That's what I do.

Hope this makes it a bit clearer for you.

John

Hello

thanks for the reply. i think i see what you are getting at, the problem is
that i want the criteria to be based on the unbound text boxes that i have on
the main unbound form. from what you posted it looks like you are suggesting
that i do a qry that cant be changed by what people are typing in the unbound
text boxes on the unbound main form.

where do i put the SQL that you suggest?

where do i put the "SubFormContainer.Form.RecordSource = SQL"?

:

Hi -

I do just that, and my approach is to just change the subform's
recordsource using SQL, like this:

SubFormContainer.Form.RecordSource = SQL

where SQL selects all the required fields, with the WHERE clause doing
the filtering as required. Here's an example SQL from my application:

SELECT distinct [film], [frame], [picture number formatted], [topic],
[location], [loc2], [storagecde],[storage1] FROM [PHOTO LIST] WHERE
[word] like 'burton*' AND [location code] = '603'

The most important part is getting the where clause right - the selected
fields will be the same.

John


DawnTreader wrote:

Hello

on my unbound mainform i would like to have a box that controls the query
that the subform is based on. is there a way through code to tell fields in
the subform to be filtered on what is typed in text boxes on the mainform?

do i need to place any special stuff in the query? i know how to create
parameter queries, but this would seem to be more complicated than that.

any help is appreciated! :)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top