Macro Open Form Where Clause

[JJF]

A Macro opens a Form which is based on a Crosstab Query which shows the
occupied seats in my theater. If I type #06/01/2007# into the Where box of
the Macro it works perfect but if I use another Form with a Calendar to
generate the date the "Jet engine doesn't find a valid field". I only get
this message when the Form is based on this Crosstab Query. My calendar Form
works fine opening all my other Queries, Forms and Macros. Any suggestions?

 

[Steve Schapel]

JJF,

I have no experience of being able to just type a value, without the
field it qualifies, into the Where Condition argument of a macro. I am
surprised to learn that the form opens at the desired record with just
the date entered. Normally I would expect to see something like this
for the Where argument:
[NameOfYourDateField]=#06/01/2007#

 

[JJF]

You are right. I did type "[ShowDate]=#06/01/2007#" to test the macro. And it
works fine. But why can it pick up the date value from
"[ShowDate]=[forms]![MyCalendarForm]![ShowDate]" ? The calendar form works
fine with my other macros. Could the crosstab query act differently when I
used the form wizard to create the "SeatingChart Form"?

JJF,

I have no experience of being able to just type a value, without the
field it qualifies, into the Where Condition argument of a macro. I am
surprised to learn that the form opens at the desired record with just
the date entered. Normally I would expect to see something like this
for the Where argument:
[NameOfYourDateField]=#06/01/2007#

--
Steve Schapel, Microsoft Access MVP

A Macro opens a Form which is based on a Crosstab Query which shows the
occupied seats in my theater. If I type #06/01/2007# into the Where box of
the Macro it works perfect but if I use another Form with a Calendar to
generate the date the "Jet engine doesn't find a valid field". I only get
this message when the Form is based on this Crosstab Query. My calendar Form
works fine opening all my other Queries, Forms and Macros. Any suggestions?

 

[Steve Schapel]

JJF,

Sorry, I am a little lost here. It is not clear to me which form you
are opening, from where, and which form is supplying the where criteria.

[ShowDate]=[Forms]![MyCalendarForm]![ShowDate]
.... the meaning of this expression is:

- ShowDate is the name of a field in the record source of the form that
you are opening. (SeatingChart?)
- You are opening this form, *from* the MyCalendarForm form.
- The MyCalendarForm form is not a subform.
- The MyCalendarForm form has a control named ShowDate.
- The ShowDate field in both cases is a Date/Time data type.
- You want the second form to open, only showing records where the value
of the ShowDate field is the same as the value of the ShowDate control
on the current record on the open MyCalendarForm form.

Are all of these assumptions correct?

 

[JJF]

We match on all your points except your point 5:

"- The ShowDate field in both cases is a Date/Time data type."

MyCalendarForm has only one field on it "ShowDate". It's 'unbound' and I
type a date into it like '06/01/2007'. If I can get the typing date it to
work I'd like to add a "Calendar Control" to generate the date value.

JJF,

Sorry, I am a little lost here. It is not clear to me which form you
are opening, from where, and which form is supplying the where criteria.

[ShowDate]=[Forms]![MyCalendarForm]![ShowDate]
.... the meaning of this expression is:

- ShowDate is the name of a field in the record source of the form that
you are opening. (SeatingChart?)
- You are opening this form, *from* the MyCalendarForm form.
- The MyCalendarForm form is not a subform.
- The MyCalendarForm form has a control named ShowDate.
- The ShowDate field in both cases is a Date/Time data type.
- You want the second form to open, only showing records where the value
of the ShowDate field is the same as the value of the ShowDate control
on the current record on the open MyCalendarForm form.

Are all of these assumptions correct?

--
Steve Schapel, Microsoft Access MVP

You are right. I did type "[ShowDate]=#06/01/2007#" to test the macro. And it
works fine. But why can it pick up the date value from
"[ShowDate]=[forms]![MyCalendarForm]![ShowDate]" ? The calendar form works
fine with my other macros. Could the crosstab query act differently when I
used the form wizard to create the "SeatingChart Form"?

 

[Steve Schapel]

JJF,

In the design of the MyCalendarForm form, set the Format property of the
ShowDate control to a valid date format. For example: mm/dd/yyyy
See how that goes.

The other thing to check is that the data in the ShowDate field in the
table does not contain a Time component.