Pause macro to enter parameter

[Diggy]

Hello to all.

I need to run a query weekly with results based on beginning and end dates
as criteria in one field. I then need to export the resultant dataset to
Excel. Creating the macro to export the dataset was easy. However, what
I'd like to do is create a macro that will open the query in design view,
or perhaps open a parameter window, so I can change dates, then do the
export. Is this possible? If so, how (with the understanding that I'm a
macro noobee and no programmer)?

Many thanks.

 

[Steve Schapel]

Diggy,

It would not be a good idea to open a query in design view via your macro.

Would this serve your purpose?... Put unbound textboxes on a form,
where you enter the query criteria as required prior to running the
macro/export. Then, in the query, replace your existing hard-coded
criteria with a reference to the controls on the form, ising syntax such
as [Forms]![NameOfForm]![TextboxName]

 

[Diggy]

Diggy,

It would not be a good idea to open a query in design view via your macro.

Would this serve your purpose?... Put unbound textboxes on a form,
where you enter the query criteria as required prior to running the
macro/export. Then, in the query, replace your existing hard-coded
criteria with a reference to the controls on the form, ising syntax such
as [Forms]![NameOfForm]![TextboxName]

Steve,

Point well taken on opening query in design view via query.

Your suggestion sounds very appealing. Actually, I've created a
switchboard already; can I assume that I can add something to that? If
so, Might I be so bold as to ask how? Is it possible to fire off the
macro after I've entered the data into the form and pressed "OK" (or
whatever)?

 

[Steve Schapel]

Diggy,

Yes, you would assign the macro on the On Click event property of the
button, which you click after entering the criteria, and away you go.

I have never used the built-in switchboard manager. It has always
seemed to be a complex approach to a simple process, and I think that
making your own forms to control navigation etc is much preferable. So
I can't advise specifically. But I'm sure you can add new options to an
existing switchboard.

 

[Diggy]

Diggy,

Yes, you would assign the macro on the On Click event property of the
button, which you click after entering the criteria, and away you go.

I have never used the built-in switchboard manager. It has always seemed
to be a complex approach to a simple process, and I think that making your
own forms to control navigation etc is much preferable. So I can't advise
specifically. But I'm sure you can add new options to an existing
switchboard.

Steve,

I'm real close here. I followed your recipe. If I enter a date such as
11/17/06 or 11/17/2006, and press my "OK' button, the macro runs, but no
data appear in my spreadsheet. Also, I have to use date ranges, such as
">=11/17/06 and <11/27/06". You've been kind enough to get me this far.
Any ideas?

Diggy

 

[Steve Schapel]

Diggy,

You mean you have a couple of unbound textboxes on the form, for the
beginning and ending dates? Please set the Format property of these
textboxes to a date format (for example mm/dd/yyyy) to make it clear to
Access that it should treat it as a date. And then, in the criteria of
your date field in the query, the equivalent of this...
Between [Forms]![NameOfForm]![FromTextbox] And
[Forms]![NameOfForm]![ToTextbox]

 

[Diggy]

Diggy,

You mean you have a couple of unbound textboxes on the form, for the
beginning and ending dates? Please set the Format property of these
textboxes to a date format (for example mm/dd/yyyy) to make it clear to
Access that it should treat it as a date. And then, in the criteria of
your date field in the query, the equivalent of this...
Between [Forms]![NameOfForm]![FromTextbox] And
[Forms]![NameOfForm]![ToTextbox]

Steve,

Bingo! Worked great! I think the key to the empty dataset was the Format
property of the text boxes needing to be set to (in my case)
ShortDate (stoopid me). One last question? Can I have the form also close
after I press the "OK" button? How?

Many, many thanks.

Diggy

 

[Steve Schapel]

... One last question?

Yeah, right. I've heard that one before ;-)

Can I have the form also close
after I press the "OK" button? How?

Yep. Just add a Close action at the end of the macro.

Many, many thanks.

You're very welcome. Best wishes for the rest of the project.