Passing form data to a query thru a macro

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

Guest

I have a form with 3 data entry fields and a SUBMIT button. When the submit
button is clicked, there's a vB procedure that validates and processes the
fields. Then that procedure does a "DoCmd.RunMacro 'whatever'"

Now, the query called from the macro is supposed to query based on the data
in the form-fields. However, the query refuses to recognize the fields'
existence, and I get prompted for parms instead. In thw query itself, I have
the following (example) coded in for a criteria:

Between [Forms]![Site Selection]![StartMMYYYY] And [Forms]![Site
Selection]![EndMMYYYY]

But again, the query refuses to recognize the fields. I created this code
with the expression builder, so *it* sees the fields properly.

Can someone please help with this?

Thanks!
 
Dennis

For a forms-based criterion to work in a query, the form has to be open.
The typical reason you get prompted for a parameter in the circumstance you
described is that the form is closed. Your post implies that you are doing
this from an open form -- but can you confirm this?

What happens if you open the form, set the values, then, leaving the form
open, open the query and attempt to run it? I don't mean via the macro, I
mean going directly to the query and trying that. If you do this and the
prompts still come up, the parameter prompts are mis-spelled (i.e., they
don't match the expression needed for Access to "see" the form's controls).
 
Dennis

Another thought...

Your parameter -- [Forms]![Site Selection]![StartMMYYYY] -- implies a text
control. Are you setting criteria for a Date/Time field? If so, you need
to compare to a valid date/time value, and MMYYYY is not a date.
 
Okay, I checked. The field in the table *is* date/time, and my text fiend is
standard text (not date/time). Now, in the TABLE, even though the field is
date/time, the VALUES are MM/YYYY only. When I attempt to create a similar
field, the DD information is kept as well (short date format). I have no idea
how to make this text entry field into a date/time field that matches the
MM/YYYY format in the table.

Any ideas?

Oh, and opening the query directly (with the form open) doesn't work either...

Thanks!

Dennis


Jeff Boyce said:
Dennis

Another thought...

Your parameter -- [Forms]![Site Selection]![StartMMYYYY] -- implies a text
control. Are you setting criteria for a Date/Time field? If so, you need
to compare to a valid date/time value, and MMYYYY is not a date.

--
Good luck

Jeff Boyce
<Access MVP>

Dennis said:
I have a form with 3 data entry fields and a SUBMIT button. When the submit
button is clicked, there's a vB procedure that validates and processes the
fields. Then that procedure does a "DoCmd.RunMacro 'whatever'"

Now, the query called from the macro is supposed to query based on the data
in the form-fields. However, the query refuses to recognize the fields'
existence, and I get prompted for parms instead. In thw query itself, I have
the following (example) coded in for a criteria:

Between [Forms]![Site Selection]![StartMMYYYY] And [Forms]![Site
Selection]![EndMMYYYY]

But again, the query refuses to recognize the fields. I created this code
with the expression builder, so *it* sees the fields properly.

Can someone please help with this?

Thanks!
Click to expand...
Click to expand...
 
I got it to work. There was a column in the query that I'd added WAY at the
end that requested parms that I don't pass to the query. I removed that
column and it's working fine.

Thanks for your assistance!

Dennis

Dennis said:
Okay, I checked. The field in the table *is* date/time, and my text fiend is
standard text (not date/time). Now, in the TABLE, even though the field is
date/time, the VALUES are MM/YYYY only. When I attempt to create a similar
field, the DD information is kept as well (short date format). I have no idea
how to make this text entry field into a date/time field that matches the
MM/YYYY format in the table.

Any ideas?

Oh, and opening the query directly (with the form open) doesn't work either...

Thanks!

Dennis


Jeff Boyce said:
Dennis

Another thought...

Your parameter -- [Forms]![Site Selection]![StartMMYYYY] -- implies a text
control. Are you setting criteria for a Date/Time field? If so, you need
to compare to a valid date/time value, and MMYYYY is not a date.

--
Good luck

Jeff Boyce
<Access MVP>

Dennis said:
I have a form with 3 data entry fields and a SUBMIT button. When the submit
button is clicked, there's a vB procedure that validates and processes the
fields. Then that procedure does a "DoCmd.RunMacro 'whatever'"

Now, the query called from the macro is supposed to query based on the data
in the form-fields. However, the query refuses to recognize the fields'
existence, and I get prompted for parms instead. In thw query itself, I have
the following (example) coded in for a criteria:

Between [Forms]![Site Selection]![StartMMYYYY] And [Forms]![Site
Selection]![EndMMYYYY]

But again, the query refuses to recognize the fields. I created this code
with the expression builder, so *it* sees the fields properly.

Can someone please help with this?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I'll politely disagree. Your table's field may have a FORMAT that displays
only MMYYYY, but if the field is typed as a Date/Time field, it is storing a
number representing the number of days since some time late in the 1800's,
and, as the decimal places, the decimal proportion of 24 hours (i.e., the
"time" portion, where .500 = noon).

Jeff Boyce
<Access MVP>

Dennis said:
Okay, I checked. The field in the table *is* date/time, and my text fiend is
standard text (not date/time). Now, in the TABLE, even though the field is
date/time, the VALUES are MM/YYYY only. When I attempt to create a similar
field, the DD information is kept as well (short date format). I have no idea
how to make this text entry field into a date/time field that matches the
MM/YYYY format in the table.

Any ideas?

Oh, and opening the query directly (with the form open) doesn't work either...

Thanks!

Dennis


Jeff Boyce said:
Dennis

Another thought...

Your parameter -- [Forms]![Site Selection]![StartMMYYYY] -- implies a text
control. Are you setting criteria for a Date/Time field? If so, you need
to compare to a valid date/time value, and MMYYYY is not a date.

--
Good luck

Jeff Boyce
<Access MVP>

Dennis said:
I have a form with 3 data entry fields and a SUBMIT button. When the submit
button is clicked, there's a vB procedure that validates and processes the
fields. Then that procedure does a "DoCmd.RunMacro 'whatever'"

Now, the query called from the macro is supposed to query based on the data
in the form-fields. However, the query refuses to recognize the fields'
existence, and I get prompted for parms instead. In thw query itself,
Click to expand...
I
have
the following (example) coded in for a criteria:

Between [Forms]![Site Selection]![StartMMYYYY] And [Forms]![Site
Selection]![EndMMYYYY]

But again, the query refuses to recognize the fields. I created this code
with the expression builder, so *it* sees the fields properly.

Can someone please help with this?

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