MS Access database support

Author

archive macro

Comcast Newsgroups

2005-07-30, 3:24 am

I'm trying to put together a macro that queries my cell phone call expense
DB the selects records as of a specific Date that are not marked "Archive",
prints a report, then marks the records "Archive".

I've worked out the Queries and the report and even got them into the macro
but I can't figure out how to set up the Macro to prompt for ONE Date.
Instead I get a prompt for the Select Query date and then again for the
Update Query date. It's the same date. Surely there is a simple one to
input the date once.

Thanks

Robert

Mark

2005-07-30, 3:24 am

I'm no expert but what I would do is create a temp table for the date. When
you want to run your macro, I would run a query that deletes the temp date,
open up a form similar in shape to an input box which is bound to the temp
table. Then once the date is entered, the form would close starting your
macro which you already have. Change both your select and update queries to
they lookup the date in your temp table. This way, you would only have to
enter the date once!!!

Mark

"Comcast Newsgroups" <robb1@email.com> wrote in message
news:M9CdnbuzmYachHb
fRVn-oA@comcast.com...
I'm trying to put together a macro that queries my cell phone call expense
DB the selects records as of a specific Date that are not marked "Archive",
prints a report, then marks the records "Archive".

I've worked out the Queries and the report and even got them into the macro
but I can't figure out how to set up the Macro to prompt for ONE Date.
Instead I get a prompt for the Select Query date and then again for the
Update Query date. It's the same date. Surely there is a simple one to
input the date once.

Thanks

Robert

pietlinden@hotmail.com

2005-07-30, 3:24 am

One way is to point your query at a field on a form (no recordsource
behind it - it's just used to clllect query parameter values). So you
have a text field on your form that is formatted as a date, and then
pass that to the query.

SELECT ...
FROM MyTable
WHERE SomeDate = Forms![MyForm]![txtDate]
AND RecordStatus<>"Archive";

then if you turn that into an update query...
UPDATE MyTable
SET SomeDate = Forms![MyForm]![txtDate]
WHERE RecordStatus<>"Archive";

the filter stuff isn't quite right, I'm sure, but it should give you
some ideas.

if you point your queries at the form, then you can drop your macro
onto the form (in design view) and it'll add a button to execute it.
Once it's all working right, you' might want to shut off the warnings
and turn them back on when you're done.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappArchiveRecords"
DoCmd.OpenQuery "qupdMarkRecords"
DoCmd.SetWarnings True

Robert

2005-07-30, 9:24 am

I was playing around with that because it does make sense to me. But I'm
stumped as to how to get the CutOffDate from my Form to the Queries??? Do
I have to create a temporary underlying, Table as Mark suggested, just to
hold that CutOffDate while I run my Queries? Or can I somehow Select the
CutOffDate right from the Form and pass it through to the Queries that I'm
using in my Macro?

Or can I just use the Conditions feature of Macros to get my Date? I've
been playing around with something like this, but Access complains that it
doesn't know what [Tables] is. I created my Phone Log Cutoff Date with only
a TextBox to hold a date then I put the following into the Condtion line of
my Macro:
[Tables]![Phone Log Minutes].[Date & Time]<[Forms]![Phone Log Cutoff
Date]![CutOffDate]
Even if it did work, I'm still puzzled as to how to pass the CutOffDate to
my Query.

Thanks
Robert

<pietlinden@hotmail.com> wrote in message
news:1122707702.868017.296310@g47g2000cwa.googlegroups.com...
> One way is to point your query at a field on a form (no recordsource
> behind it - it's just used to clllect query parameter values). So you
> have a text field on your form that is formatted as a date, and then
> pass that to the query.
>
> SELECT ...
> FROM MyTable
> WHERE SomeDate = Forms![MyForm]![txtDate]
> AND RecordStatus<>"Archive";
>
> then if you turn that into an update query...
> UPDATE MyTable
> SET SomeDate = Forms![MyForm]![txtDate]
> WHERE RecordStatus<>"Archive";
>
> the filter stuff isn't quite right, I'm sure, but it should give you
> some ideas.
>
> if you point your queries at the form, then you can drop your macro
> onto the form (in design view) and it'll add a button to execute it.
> Once it's all working right, you' might want to shut off the warnings
> and turn them back on when you're done.
>
> DoCmd.SetWarnings False
> DoCmd.OpenQuery "qappArchiveRecords"
> DoCmd.OpenQuery "qupdMarkRecords"
> DoCmd.SetWarnings True
>

pietlinden@hotmail.com

2005-07-30, 11:24 am

Robert,
The easiest way to implement what I suggested is to use a blank form
and drop a textbox control on it and format the contents as a date.
Then once that form saved, you can use it to collect your criteria.
Say the form is called "frmCollectDate" On that form, say the textbox
control is called txtDate. (If you want, go ahead and slap one
together - that's
probably the easiest way to understand it.) Save the form and open it
in normal view, so you can enter data into the date field.

then create a query based on your table. Then click on the criteria
line of your query. Then click on the wand and you should be able to
navigate the objects in your database. Choose the forms folder, and
the open form should show up there. Select the txtDate field. That
should give you what you want. If you're still stuck I can send you a
quick example. Once you understand how to do it, this is a walk.

Robert

2005-07-30, 8:24 pm

Most excellent! Works like a charm. I was very close to getting it but it
just wasn't quite there.

Thanks

<pietlinden@hotmail.com> wrote in message
news:1122741992.963778.37050@z14g2000cwz.googlegroups.com...
> Robert,
> The easiest way to implement what I suggested is to use a blank form
> and drop a textbox control on it and format the contents as a date.
> Then once that form saved, you can use it to collect your criteria.
> Say the form is called "frmCollectDate" On that form, say the textbox
> control is called txtDate. (If you want, go ahead and slap one
> together - that's
> probably the easiest way to understand it.) Save the form and open it
> in normal view, so you can enter data into the date field.
>
> then create a query based on your table. Then click on the criteria
> line of your query. Then click on the wand and you should be able to
> navigate the objects in your database. Choose the forms folder, and
> the open form should show up there. Select the txtDate field. That
> should give you what you want. If you're still stuck I can send you a
> quick example. Once you understand how to do it, this is a walk.
>

Sponsored Links