Updating query at runtime

[Someone]

Hello

My main form has a datasheet subform that is based on a query to a table. I
have unbound text boxes on the main form, so that users can search the
subform by entering some data and filter out like records (the query uses
'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
simply enters some text into the relevant box and the data in the subform
requeries itself with matching records.

I have been asked if I could add a search on a particular field between two
dates (the dates do not appear in the subform as it's not required
information in that view). The field in question has 213 records and is
referenced elsewhere in the database using a listbox, so that users can
easily pick the correct entry they require. I would therefore like to have
a search form with an unbound list box with all 213 records and a date
picker for the 'From' and 'To' date. I would then like the existing query
in the subform (that I described above) to update with the information
entered. This is easy to get to work with just the listbox, but not with
the dates too.

I know that within a query, I can enter 'Between #dd/mm/yy# And #dd/mm/yy#'
to search between two dates, but how could I get the information I capture
from the form to update in the query? I tried referencing the relevant
field names on the form within the query (i.e. Between
#[Forms]![FormName]![FieldName]# etc), but this doesn't work because, when
opening the main form, I'm asked for the parameter values.

I'm very much hoping that this is possible. If so, can someone help me out?

Many thanks
M

 

[Jeff Boyce]

?Have you also tried

Between [Forms]![FormName]![FieldName] And ...

 

[Guest]

I did something similiar, and ran into a lot of problems. I was trying to
get 2 dates to be used as criteria as well for a query. My work around was
this:

I ran a query, that stored the date values into a table, then ran the dates
into the criteria of the query using the Dlookup function. Access did not
allow me to use the criteria off the form straight into the query, or at
least I tried for a week unsuccessfully. But now I have two queries instead
of one, but at least it works. I just used a macro to run all the queries
and table updates. I'm pretty sure you can use a macro to run the queries
and requery if neccessary. Good Luck!

 

[Someone]

What do you mean?

If you're suggesting that I enter that in the main form's query, this won't
work, as explained, because when opening the main form, it asks for the
parameter values for DateFrom and DateTo. This is because the values are
yet to be filled in unless the user opens the search form and enters the
values.

If I've misunderstood, could you clarify?

Many Thanks
M

?Have you also tried

Between [Forms]![FormName]![FieldName] And ...

--
Regards

Jeff Boyce
<Office/Access MVP>

Hello

My main form has a datasheet subform that is based on a query to a table. I
have unbound text boxes on the main form, so that users can search the
subform by entering some data and filter out like records (the query uses
'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
simply enters some text into the relevant box and the data in the subform
requeries itself with matching records.

I have been asked if I could add a search on a particular field between two
dates (the dates do not appear in the subform as it's not required
information in that view). The field in question has 213 records and is
referenced elsewhere in the database using a listbox, so that users can
easily pick the correct entry they require. I would therefore like to have
a search form with an unbound list box with all 213 records and a date
picker for the 'From' and 'To' date. I would then like the existing
query
in the subform (that I described above) to update with the information
entered. This is easy to get to work with just the listbox, but not with
the dates too.

I know that within a query, I can enter 'Between #dd/mm/yy# And #dd/mm/yy#'
to search between two dates, but how could I get the information I
capture
from the form to update in the query? I tried referencing the relevant
field names on the form within the query (i.e. Between
#[Forms]![FormName]![FieldName]# etc), but this doesn't work because,
when
opening the main form, I'm asked for the parameter values.

I'm very much hoping that this is possible. If so, can someone help me out?

Many thanks
M

 

[Someone]

All

Thank you for your assistance.

I have come up with the answer.

I decided to duplicate the query used on the subform and included an
additional paramater for the date field, which referenced the date pickers
on the search form in the manner I described in my original post.

In the code for the search form, I included a RecordSource change for the
subform whose query I wanted to change
(Forms!MainForm!SubForm.Form.RecordSource = "QueryName"). I also edited the
code on my 'reset search' button so that the original query is restored
thereafter.

Hope that helps anyone else who finds themselves in a similar scenario!

Thanks
M

 

[German Saer]

You can use in the form for the subform:

me.subformname.form.filterOn=true
me.subformname.form.filter="where anycondition"

and all you need to do, it is query with all the records. The Filter
property will do the rest

All

Thank you for your assistance.

I have come up with the answer.

I decided to duplicate the query used on the subform and included an
additional paramater for the date field, which referenced the date pickers
on the search form in the manner I described in my original post.

In the code for the search form, I included a RecordSource change for the
subform whose query I wanted to change
(Forms!MainForm!SubForm.Form.RecordSource = "QueryName"). I also edited the
code on my 'reset search' button so that the original query is restored
thereafter.

Hope that helps anyone else who finds themselves in a similar scenario!

Thanks
M

Hello

My main form has a datasheet subform that is based on a query to a table.
I
have unbound text boxes on the main form, so that users can search the
subform by entering some data and filter out like records (the query uses
'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
simply enters some text into the relevant box and the data in the subform
requeries itself with matching records.

I have been asked if I could add a search on a particular field between
two
dates (the dates do not appear in the subform as it's not required
information in that view). The field in question has 213 records and is
referenced elsewhere in the database using a listbox, so that users can
easily pick the correct entry they require. I would therefore like to
have
a search form with an unbound list box with all 213 records and a date
picker for the 'From' and 'To' date. I would then like the existing query
in the subform (that I described above) to update with the information
entered. This is easy to get to work with just the listbox, but not with
the dates too.

I know that within a query, I can enter 'Between #dd/mm/yy# And
#dd/mm/yy#'
to search between two dates, but how could I get the information I capture
from the form to update in the query? I tried referencing the relevant
field names on the form within the query (i.e. Between
#[Forms]![FormName]![FieldName]# etc), but this doesn't work because, when
opening the main form, I'm asked for the parameter values.

I'm very much hoping that this is possible. If so, can someone help me
out?

Many thanks
M

 

[Jeff Boyce]

Your post had
Between #[Forms...
mine had
Between [Forms...

Also, if the form isn't open when you run the query, Access will have to
prompt for the parameters.

--
Regards

Jeff Boyce
<Office/Access MVP>

What do you mean?

If you're suggesting that I enter that in the main form's query, this won't
work, as explained, because when opening the main form, it asks for the
parameter values for DateFrom and DateTo. This is because the values are
yet to be filled in unless the user opens the search form and enters the
values.

If I've misunderstood, could you clarify?

Many Thanks
M

?Have you also tried

Between [Forms]![FormName]![FieldName] And ...

--
Regards

Jeff Boyce
<Office/Access MVP>

Hello

My main form has a datasheet subform that is based on a query to a

table.
I

have unbound text boxes on the main form, so that users can search the
subform by entering some data and filter out like records (the query uses
'Like "*" & [Forms]![FormName]![FieldName] & "*" Or Is Null'. The user
simply enters some text into the relevant box and the data in the subform
requeries itself with matching records.

I have been asked if I could add a search on a particular field between two
dates (the dates do not appear in the subform as it's not required
information in that view). The field in question has 213 records and is
referenced elsewhere in the database using a listbox, so that users can
easily pick the correct entry they require. I would therefore like to have
a search form with an unbound list box with all 213 records and a date
picker for the 'From' and 'To' date. I would then like the existing
query
in the subform (that I described above) to update with the information
entered. This is easy to get to work with just the listbox, but not with
the dates too.

I know that within a query, I can enter 'Between #dd/mm/yy# And #dd/mm/yy#'
to search between two dates, but how could I get the information I
capture
from the form to update in the query? I tried referencing the relevant
field names on the form within the query (i.e. Between
#[Forms]![FormName]![FieldName]# etc), but this doesn't work because,
when
opening the main form, I'm asked for the parameter values.

I'm very much hoping that this is possible. If so, can someone help me out?

Many thanks
M