Trouble passing query criteria using VBA

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

Guest

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
 
If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.
 
Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


KARL DEWEY said:
If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


Rone said:
I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
 
You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


Rone said:
Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


KARL DEWEY said:
If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


Rone said:
I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
If I make a duplicate of the report and a duplicate of the query, then modify
the query to hard code the 30 days past & 90 days past, I can eliminate the
prompts and it will work as I expect, but I was hoping to used the existing
report without making duplicates, which will confuse some of my users as to
what the extra report is for. Is it even possible to do what I'm trying to
do?

Here's my SQL:

SELECT tbl_properties.property_name, tbl_clients.company,
tbl_clients.reminder_date, tbl_reminder_type.reminder_type,
tbl_lease_status.id
FROM tbl_reminder_type INNER JOIN (tbl_properties INNER JOIN
(tbl_lease_status INNER JOIN tbl_clients ON tbl_lease_status.id =
tbl_clients.current_status_id) ON tbl_properties.property_id =
tbl_clients.property_id) ON tbl_reminder_type.reminder_type_id =
tbl_clients.reminder_type_id
WHERE (((tbl_clients.reminder_date) Between
Now()-[Forms]![frm_date_criteria_reminders]![beg_date] And
Now()+[Forms]![frm_date_criteria_reminders]![end_date]) AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

Thanks
--
Rone


KARL DEWEY said:
You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


Rone said:
Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


KARL DEWEY said:
If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


:

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
As I said try not refering to the form at all and use this criteria ---
WHERE (((tbl_clients.reminder_date) Between Now()-30 and Now()+90 AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

--
KARL DEWEY
Build a little - Test a little


Rone said:
If I make a duplicate of the report and a duplicate of the query, then modify
the query to hard code the 30 days past & 90 days past, I can eliminate the
prompts and it will work as I expect, but I was hoping to used the existing
report without making duplicates, which will confuse some of my users as to
what the extra report is for. Is it even possible to do what I'm trying to
do?

Here's my SQL:

SELECT tbl_properties.property_name, tbl_clients.company,
tbl_clients.reminder_date, tbl_reminder_type.reminder_type,
tbl_lease_status.id
FROM tbl_reminder_type INNER JOIN (tbl_properties INNER JOIN
(tbl_lease_status INNER JOIN tbl_clients ON tbl_lease_status.id =
tbl_clients.current_status_id) ON tbl_properties.property_id =
tbl_clients.property_id) ON tbl_reminder_type.reminder_type_id =
tbl_clients.reminder_type_id
WHERE (((tbl_clients.reminder_date) Between
Now()-[Forms]![frm_date_criteria_reminders]![beg_date] And
Now()+[Forms]![frm_date_criteria_reminders]![end_date]) AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

Thanks
--
Rone


KARL DEWEY said:
You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


Rone said:
Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


:

If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


:

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
Yes, that does work great if I put it into the query.

I have about 20 management reports I need to do this with. My users need to
be able to input the data manually to extract out the information as they
need it, which is why I can't hard code the data into the query. All these
reports have the criteria input box placed inside the query to be flexible
for all the users. I'd rather not change these queries to hard code the
criteria if I can find a way around this. I know I could create duplicates
of everything and change the names, but all those extra reports would confuse
the users.

Is it even possible in Access 2003 to call a report using VBA and pass it
the criteria so the query would run right without requiring manual input, or
am I just dreaming of something not technically possible?
--
Rone


KARL DEWEY said:
As I said try not refering to the form at all and use this criteria ---
WHERE (((tbl_clients.reminder_date) Between Now()-30 and Now()+90 AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

--
KARL DEWEY
Build a little - Test a little


Rone said:
If I make a duplicate of the report and a duplicate of the query, then modify
the query to hard code the 30 days past & 90 days past, I can eliminate the
prompts and it will work as I expect, but I was hoping to used the existing
report without making duplicates, which will confuse some of my users as to
what the extra report is for. Is it even possible to do what I'm trying to
do?

Here's my SQL:

SELECT tbl_properties.property_name, tbl_clients.company,
tbl_clients.reminder_date, tbl_reminder_type.reminder_type,
tbl_lease_status.id
FROM tbl_reminder_type INNER JOIN (tbl_properties INNER JOIN
(tbl_lease_status INNER JOIN tbl_clients ON tbl_lease_status.id =
tbl_clients.current_status_id) ON tbl_properties.property_id =
tbl_clients.property_id) ON tbl_reminder_type.reminder_type_id =
tbl_clients.reminder_type_id
WHERE (((tbl_clients.reminder_date) Between
Now()-[Forms]![frm_date_criteria_reminders]![beg_date] And
Now()+[Forms]![frm_date_criteria_reminders]![end_date]) AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

Thanks
--
Rone


KARL DEWEY said:
You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


:

If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


:

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
I know I could create duplicates of everything and change the names, but
all those extra reports would confuse the users.
This I do not understand.

You can have a form with unbound textboxes to enter your start and end date.
The query would use the textboxes for criteria. The query can pull records
from between the two dates. Or you can enter data in only one and have it
pull from that date backwards or from that date forwards.

Just how many variations on pulling selected records do you want? What are
they?
from date to date
from date backwards
from date forwards
from date backwards for 'x' number of days
from date forwards for 'x' number of days

--
KARL DEWEY
Build a little - Test a little


Rone said:
Yes, that does work great if I put it into the query.

I have about 20 management reports I need to do this with. My users need to
be able to input the data manually to extract out the information as they
need it, which is why I can't hard code the data into the query. All these
reports have the criteria input box placed inside the query to be flexible
for all the users. I'd rather not change these queries to hard code the
criteria if I can find a way around this. I know I could create duplicates
of everything and change the names, but all those extra reports would confuse
the users.

Is it even possible in Access 2003 to call a report using VBA and pass it
the criteria so the query would run right without requiring manual input, or
am I just dreaming of something not technically possible?
--
Rone


KARL DEWEY said:
As I said try not refering to the form at all and use this criteria ---
WHERE (((tbl_clients.reminder_date) Between Now()-30 and Now()+90 AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

--
KARL DEWEY
Build a little - Test a little


Rone said:
If I make a duplicate of the report and a duplicate of the query, then modify
the query to hard code the 30 days past & 90 days past, I can eliminate the
prompts and it will work as I expect, but I was hoping to used the existing
report without making duplicates, which will confuse some of my users as to
what the extra report is for. Is it even possible to do what I'm trying to
do?

Here's my SQL:

SELECT tbl_properties.property_name, tbl_clients.company,
tbl_clients.reminder_date, tbl_reminder_type.reminder_type,
tbl_lease_status.id
FROM tbl_reminder_type INNER JOIN (tbl_properties INNER JOIN
(tbl_lease_status INNER JOIN tbl_clients ON tbl_lease_status.id =
tbl_clients.current_status_id) ON tbl_properties.property_id =
tbl_clients.property_id) ON tbl_reminder_type.reminder_type_id =
tbl_clients.reminder_type_id
WHERE (((tbl_clients.reminder_date) Between
Now()-[Forms]![frm_date_criteria_reminders]![beg_date] And
Now()+[Forms]![frm_date_criteria_reminders]![end_date]) AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

Thanks
--
Rone


:

You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


:

If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


:

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help Karl. The only two scenarios I have in the database are

from date backwards for 'x' number of days
from date forwards for 'x' number of days

Additionally, the other reports use a string comparison such as "LIKE '*'
[Enter part of the name of the property]'*' "


--
Rone


KARL DEWEY said:
all those extra reports would confuse the users.
This I do not understand.

You can have a form with unbound textboxes to enter your start and end date.
The query would use the textboxes for criteria. The query can pull records
from between the two dates. Or you can enter data in only one and have it
pull from that date backwards or from that date forwards.

Just how many variations on pulling selected records do you want? What are
they?
from date to date
from date backwards
from date forwards
from date backwards for 'x' number of days
from date forwards for 'x' number of days

--
KARL DEWEY
Build a little - Test a little


Rone said:
Yes, that does work great if I put it into the query.

I have about 20 management reports I need to do this with. My users need to
be able to input the data manually to extract out the information as they
need it, which is why I can't hard code the data into the query. All these
reports have the criteria input box placed inside the query to be flexible
for all the users. I'd rather not change these queries to hard code the
criteria if I can find a way around this. I know I could create duplicates
of everything and change the names, but all those extra reports would confuse
the users.

Is it even possible in Access 2003 to call a report using VBA and pass it
the criteria so the query would run right without requiring manual input, or
am I just dreaming of something not technically possible?
--
Rone


KARL DEWEY said:
As I said try not refering to the form at all and use this criteria ---
WHERE (((tbl_clients.reminder_date) Between Now()-30 and Now()+90 AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

--
KARL DEWEY
Build a little - Test a little


:

If I make a duplicate of the report and a duplicate of the query, then modify
the query to hard code the 30 days past & 90 days past, I can eliminate the
prompts and it will work as I expect, but I was hoping to used the existing
report without making duplicates, which will confuse some of my users as to
what the extra report is for. Is it even possible to do what I'm trying to
do?

Here's my SQL:

SELECT tbl_properties.property_name, tbl_clients.company,
tbl_clients.reminder_date, tbl_reminder_type.reminder_type,
tbl_lease_status.id
FROM tbl_reminder_type INNER JOIN (tbl_properties INNER JOIN
(tbl_lease_status INNER JOIN tbl_clients ON tbl_lease_status.id =
tbl_clients.current_status_id) ON tbl_properties.property_id =
tbl_clients.property_id) ON tbl_reminder_type.reminder_type_id =
tbl_clients.reminder_type_id
WHERE (((tbl_clients.reminder_date) Between
Now()-[Forms]![frm_date_criteria_reminders]![beg_date] And
Now()+[Forms]![frm_date_criteria_reminders]![end_date]) AND
((tbl_lease_status.id)<>3 And (tbl_lease_status.id)<>7))
ORDER BY tbl_clients.reminder_date;

Thanks
--
Rone


:

You missed my point.
What is actually generating the prompt?
Are you using a query for your report?
Post your SQL statement so it can be edited for you to eliminate the prompts.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for the reply. I do see your point and honestly, I would rather not
bother with using the form, but I'm not sure how to handle this. Basically,
all I want to do is email a management report that has criteria to filter the
records. When I try to do this Access pops up an Input box to suply the
critera. For example, all data with a "begin date" that is 30 days past, or
40 days, or whatever the user puts in....then all data with an "End Date" 90
days, or 100 days, etc. into the future. The report is based off of the
query and I guess I'm a bit confused how I can run the report through VBA so
it will email the report using DoCmd.SendObject, when I have to manually
supply the criteria through a popup box.

Your advice is appreciated.
--
Rone


:

If you are going to reference a for for data then it must be open.
If you are going to set the value of the text boxes with Now()-30 and
Now()+90 why bother at all. Just use those values as criteria in the query.


--
KARL DEWEY
Build a little - Test a little


:

I need some help. I'm wanting to email management reports automatically on
monday of every week, without the need to manually run the reports. I have a
couple of functions that do this just fine and it works great. However, I
have some reports that I pass criteria information using a form input that
criteria, like begin date and end date. I want to pass this "begin/end"
critera using VBA, then email the results in the report format using VBA

For example:
My form that has two text fields one for "Begin Date" and one for "End Date"
When the user submits that form it calls a query and that query accepts the
inputs from the text boxes and is used to build the report. This is what I
want to accomplish using VBA, but without any user interaction, because the
reports will be ran in the middle of the night.

Here's my VBA code:

[Forms]![frm_date_criteria(reminders)]!beg_date = Now()-30
[Forms]![frm_date_criteria(reminders)]![end_date] = Now() + 90

When I run this I get an error saying this form can't be found.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top