Pause macro to enter parameter

  • Thread starter Thread starter Diggy
  • Start date Start date
D

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

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]
Click to expand...

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

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.
Click to expand...

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

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]
Click to expand...

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
 
Back
Top