Access 2002 - Resetting/Deleting queries

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

Hi Guys,

I have 5 different queries being exported to excel on a
weekly basis.

These queries export all records which have been
checked "yes" in the different user forms. What I need
to do is at the end of each week when the administrator
hits the export to excel buttons that all records
with "yes" are deleted after they are exported...

is this possible?

I need this so when next weeks export comes along the
same records aren't resent.

ps I'm not to up on VBA
 
Several Options:
I am assuming the ExportFlag is implemented as a Boolean (Yes/No)
field

1. delete the records as you asked
Delete * from tbl where ExportLflag = true

2. Change the "Yes" to a "No"
UPDATE tbl SET tbl.ExportLflag = false;


3. Add a DateExported field (My preference)

Set that to the current date when you export
UPDATE tbl SET tbl.DateExported = Date();
and select records with DateEported = Null for export

This allows you to
a. resend everything exported on a particular date
b. Know when a particular record was sent

If you want to get real picky, you can use a full timeStamp
SET DateExported = Now()
and know to the second when a particular record was sent.

Set up your current Export function so it returns True if successful

Function ExportData () as boolean
On Error goto ProcError
....
YOurCode Goes Here
....
ExportData = true
ProcExit:
Exit Function
ProcError
MsgBox "I'm sorry Dave, I can't do that"
ExportData = false
resume ProcExit
end Function


To implement, simply chain the commands
If ExportData() = true then
ExecuteSQL "qupExportedFlag"
endif
 
Back
Top