Pause macro to enter parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diggy - 29 Nov 2006 17:48 GMT

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.

Reply to this Message

Steve Schapel - 29 Nov 2006 18:26 GMT

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]

Signature

Steve Schapel, Microsoft Access MVP

> Hello to all.
>
[quoted text clipped - 7 lines]
>
> Many thanks.

Reply to this Message

Diggy - 29 Nov 2006 19:05 GMT

> Diggy,
>
[quoted text clipped - 5 lines]
> 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)?

Reply to this Message

Steve Schapel - 29 Nov 2006 20:04 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> Point well taken on opening query in design view via query.
>
[quoted text clipped - 3 lines]
> macro after I've entered the data into the form and pressed "OK" (or
> whatever)?

Reply to this Message

Diggy - 29 Nov 2006 21:08 GMT

> Diggy,
>
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Steve Schapel - 29 Nov 2006 21:36 GMT

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]

Signature

Steve Schapel, Microsoft Access MVP

> 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?

Reply to this Message

Diggy - 29 Nov 2006 22:10 GMT

> Diggy,
>
[quoted text clipped - 5 lines]
> 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

Reply to this Message

Steve Schapel - 29 Nov 2006 22:31 GMT

> ... 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.

Signature

Steve Schapel, Microsoft Access MVP

Reply to this Message