Move cursor in query design view with macro or vb action

[Jim]

I am creating a macro which has a command to open a query in design
view. This command results in the cursor being in the first 'Field'
field. I would like to add an action or command to this macro to move
the cursor down to the first 'Criteria' field so I can paste into it.
I do not want to substitue any action which would necessitate even the
slightest manual entry such as using [Enter Criteria] or the like. If
you can help I would appreciate it.

Jim

 

[John Vinson]

I am creating a macro which has a command to open a query in design
view.

Ummmm... WHY?

You should VERY rarely need to open query design view; and most users
should *never* see the query design window.

If you're just trying to set a parameter for a query, Don't Do It This
Way - instead, use a Parameter Query. Put either

[Enter search term:]

on the criteria line, or - better - give the user a Form with a
control whereupon to enter the criteria, and use a criterion of

[Forms]![NameOfForm]![NameOfControl]

Then base a Form (for onscreen display) or Report (for printing) on
the query, and open THAT rather than the query datasheet.

John W. Vinson[MVP]

 

[Jim]

Hi John.

The purpose for this is not for use of the form but rather to create an
instantaneous report derived from the query with entries from ONLY the
CURRENT record, without any user having to modify the query design
criteria. My idea for the macro is to copy the autonumber of the
form's current record, paste it into the autonumber field criteria line
in the query, save and close the query, and then be able to display,
print or email the one-record report (the form itself cannot be
presented for reasons I cannot explain). This may not be the best way
to accomplish this but is the only way I can think of whereas other
users can acheive this end by clicking only one button. And by
experimentation it works perfectly except for the disconnect between
where the cursor is located when the query opens and where I need it to
be, which is in the field's criteria line. So if you or anyone can
help me with getting this macro to move the cursor down four lines in
the query design I'd really appreciate it.

Jim

 

[John Vinson]

The purpose for this is not for use of the form but rather to create an
instantaneous report derived from the query with entries from ONLY the
CURRENT record, without any user having to modify the query design
criteria.

I am not suggesting that the user modify anything.

If you're running this query from a Form, you can use the name of the
textbox *in the current record on that form* as your criterion. That
is, if the form frmMyform is open to the current record, and there is
a unique field MyID in a textbox on frmMyform named txtMyID (the
textbox can be invisible), a stored query with a criterion of

=Forms!frmMyForm!txtMyID

will work without using any code at all.

It is NOT necessary to open query design, period. And I cannot think
of any good way to do what you describe.

John W. Vinson[MVP]

 

[Jim]

Thanks John. I'll give this a try.

Jim

 

[Jim]

Yeah, this worked real well, John. Again, Thanks.

Jim