Form property as query criteria

J

Jerry

Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format). In
my original 2000 database, I would create properties in VBA (using Property
Let and Property Get statements) for my forms and use them as criteria in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would not
function as it did in 2000?

Thank you,
Jerry
 
J

Jeff C

Jerry said:
Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format). In
my original 2000 database, I would create properties in VBA (using Property
Let and Property Get statements) for my forms and use them as criteria in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and Forms![frmReport]![ReportStartDate]
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would not
Forms![frmReport]![ReportEndDate]


Forms and controls are separated by an apostophe ! and the properties
are separated from the form or the control by a period .

Hope this helps,

Jeff C.
 
W

Wayne Morgan

I haven't tried this using properties, but if you have textboxes on the form
for the user to fill the dates in, why not just have the query refer to
those textboxes instead?

The "enter parameter value" window will pop-up when the query doesn't see
the parameter you've entered in something open, such as a form, so it
assumes it is one that should be prompted for.
 
G

Guest

Wayne,

The example I gave was a simple one that could be solved with textboxes on
the form. However, a more complicated example might be one in which a series
of responses in controls might lead to specific criteria. Of course, that
could be solved by dynamically creating the SQL for the recordsource of a
report. However, I always found using properties as a clean way to
accomplish this. Admittedly, I also don't have any experience with
dynamically changing the SQL for a report in an Access database that uses
security - and that's another reason I've chosen to use form properties.
I guess the main point is that what I've usually done has always worked,
and I've tested the same database in Access XP with no problems, so I don't
understand why it wouldn't work under Access 2003.

Thanks,
Jerry

Wayne Morgan said:
I haven't tried this using properties, but if you have textboxes on the form
for the user to fill the dates in, why not just have the query refer to
those textboxes instead?

The "enter parameter value" window will pop-up when the query doesn't see
the parameter you've entered in something open, such as a form, so it
assumes it is one that should be prompted for.

--
Wayne Morgan
MS Access MVP


Jerry said:
Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format). In
my original 2000 database, I would create properties in VBA (using
Property
Let and Property Get statements) for my forms and use them as criteria in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This
worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would
not
function as it did in 2000?

Thank you,
Jerry
 
V

Van T. Dinh

I think the Expression Service sometimes does not handle Properties,
especially custom Properties correctly. For example, in the Query Design
View, you can use the expression:

Forms!Form1!Combo1

to refer to the selected value of the ComboBox but you cannot use:

Forms!Form1!Combo1.Column(2)

to refer to the value in the 3rd Column of the selected row of the ComboBox.

To refer to the Column Property in the Query Design view, I use Eval()
function. I am not sure this will work for the custom Form Properties but
try:
= Eval("[Forms]![frmReport].[ReportStartDate]") And
<= Eval("[Forms]![frmReport].[ReportEndDate]")
 
D

david epsom dot com dot au

You are correct: this is in the class of things that work
differently in A2K3 than in previous versions.

The bottom line is that form and report properties are less
useful than they used to be.


There has never been a list of these differences, or any
public justification.

Personally, I've always used controls, not properties or
public variables. I can make the controls visible for
debug purposes

(david)



Jerry said:
Wayne,

The example I gave was a simple one that could be solved with textboxes
on
the form. However, a more complicated example might be one in which a
series
of responses in controls might lead to specific criteria. Of course, that
could be solved by dynamically creating the SQL for the recordsource of a
report. However, I always found using properties as a clean way to
accomplish this. Admittedly, I also don't have any experience with
dynamically changing the SQL for a report in an Access database that uses
security - and that's another reason I've chosen to use form properties.
I guess the main point is that what I've usually done has always worked,
and I've tested the same database in Access XP with no problems, so I
don't
understand why it wouldn't work under Access 2003.

Thanks,
Jerry

Wayne Morgan said:
I haven't tried this using properties, but if you have textboxes on the
form
for the user to fill the dates in, why not just have the query refer to
those textboxes instead?

The "enter parameter value" window will pop-up when the query doesn't see
the parameter you've entered in something open, such as a form, so it
assumes it is one that should be prompted for.

--
Wayne Morgan
MS Access MVP


Jerry said:
Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format).
In
my original 2000 database, I would create properties in VBA (using
Property
Let and Property Get statements) for my forms and use them as criteria
in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and
ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This
worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would
not
function as it did in 2000?

Thank you,
Jerry
 
W

Wayne Morgan

Jerry,

I believe understand what you're saying. You can manipulate the data before
you put it in the property to be used by the query. Could you do the same
thing with a hidden textbox (Visible = False or No) and place the value
there then refer to the textbox in the query?

--
Wayne Morgan
MS Access MVP


Jerry said:
Wayne,

The example I gave was a simple one that could be solved with textboxes
on
the form. However, a more complicated example might be one in which a
series
of responses in controls might lead to specific criteria. Of course, that
could be solved by dynamically creating the SQL for the recordsource of a
report. However, I always found using properties as a clean way to
accomplish this. Admittedly, I also don't have any experience with
dynamically changing the SQL for a report in an Access database that uses
security - and that's another reason I've chosen to use form properties.
I guess the main point is that what I've usually done has always worked,
and I've tested the same database in Access XP with no problems, so I
don't
understand why it wouldn't work under Access 2003.

Thanks,
Jerry

Wayne Morgan said:
I haven't tried this using properties, but if you have textboxes on the
form
for the user to fill the dates in, why not just have the query refer to
those textboxes instead?

The "enter parameter value" window will pop-up when the query doesn't see
the parameter you've entered in something open, such as a form, so it
assumes it is one that should be prompted for.

--
Wayne Morgan
MS Access MVP


Jerry said:
Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format).
In
my original 2000 database, I would create properties in VBA (using
Property
Let and Property Get statements) for my forms and use them as criteria
in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and
ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This
worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would
not
function as it did in 2000?

Thank you,
Jerry
 
J

Jerry

Thanks for the input guys. I wish I could be more surprised that the
functionality would've changed in 2003.

I had considered using the hidden textbox, and I'm sure that would work. I
have not tried the Eval function as Van suggested, but it is a good idea as
well.

Thanks again,
Jerry


Wayne Morgan said:
Jerry,

I believe understand what you're saying. You can manipulate the data before
you put it in the property to be used by the query. Could you do the same
thing with a hidden textbox (Visible = False or No) and place the value
there then refer to the textbox in the query?

--
Wayne Morgan
MS Access MVP


Jerry said:
Wayne,

The example I gave was a simple one that could be solved with textboxes
on
the form. However, a more complicated example might be one in which a
series
of responses in controls might lead to specific criteria. Of course, that
could be solved by dynamically creating the SQL for the recordsource of a
report. However, I always found using properties as a clean way to
accomplish this. Admittedly, I also don't have any experience with
dynamically changing the SQL for a report in an Access database that uses
security - and that's another reason I've chosen to use form properties.
I guess the main point is that what I've usually done has always worked,
and I've tested the same database in Access XP with no problems, so I
don't
understand why it wouldn't work under Access 2003.

Thanks,
Jerry

Wayne Morgan said:
I haven't tried this using properties, but if you have textboxes on the
form
for the user to fill the dates in, why not just have the query refer to
those textboxes instead?

The "enter parameter value" window will pop-up when the query doesn't see
the parameter you've entered in something open, such as a form, so it
assumes it is one that should be prompted for.

--
Wayne Morgan
MS Access MVP


Hi.
I am working on an Access 2000 db in Access 2003 (using 2000 format).
In
my original 2000 database, I would create properties in VBA (using
Property
Let and Property Get statements) for my forms and use them as criteria
in
queries. For example, if I had a report that should show data for a
specific date range, then I would create properties of a form (call it
"frmReport"), and name those properties ReportStartDate and
ReportEndDate.
Then, in the design of the query that the report is based on, under the
criteria for a date field, I would add ">=
[Forms]![frmReport].[ReportStartDate] and <=
[Forms]![frmReport].[ReportEndDate]" I would prompt the user for those
dates, set the values for the properties, then open the report. This
worked
without fail.
When I run this database under 2003, I get the "Enter Parameter Value"
window for both "[Forms]![frmReport].[ReportStartDate]" and
"[Forms]![frmReport].[ReportEndDate]". Does anyone know why this would
not
function as it did in 2000?

Thank you,
Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top