Delete Contents of Open

[gmatriix]

Hello All,

Is there a way to delete contents from a table on open of a db for the day. I have this table "tblOnHandInventory"....Each day I need to delete the contents of this table. Is there a way to have a box to pop up on open of the db and ask whether to delete the contents of tblOnHandInventory?

Is this possible??

Thanks

 

[pbaldy]

You could use the load event of a form that opens when the application starts. Use a Yes/No message box and empty the table if the user selects Yes.

 

[Malcy]

If you use a logon screen or a main control panel you could build in a message box in the form's OnOpen and ask if table contents to be deleted - and if answer is yes then delete. Your problem would be finding a neat way of ensuring someone does not log out to go to lunch and then when they log back in they are asked the same question and say yes mid way through the day.
Same thing could happen if networked.
So the answer is yes you can do it, but you need to think very hard if this is really the best way to do it. I cannot, off the top of my head, think of a "foolproof" method.
HTH

 

[cfbcoach]

Hello All,

Is there a way to delete contents from a table on open of a db for the day. I have this table "tblOnHandInventory"....Each day I need to delete the contents of this table. Is there a way to have a box to pop up on open of the db and ask whether to delete the contents of tblOnHandInventory?

Is this possible??

Thanks

One way to do it would be as follows:
1. create a delete query to delete all the records in the table
2. Create a table to keep a log of deletion dates
3. before executing the delete query, save the current date in the log table
4. don't execute the query unless the date of the last delete is different from the current date.

This could be done using a "RunCode" statement in an autoexec macro if you want to avoid user interaction. Pseudocode for the function called from the macro would be something like:

'if the latest date in the delete log is different from the current date, then
'add the current date to the delete log, and execute the delete query

function ClearTable()
dim curDate$, lastDel$

lastDel = Dmax("[DelDt]", "ztblDelLog")
curDate = format(Now(), "yyyy-mmm-dd")
if curDate <> LastDel then
call AppendDelLog 'subroutine that adds current date to log table
execute delete query
end if

end function

 

[gmatriix]

Hello,

Tried to follow instructions but I am missing something???
I created a delete query
I created a table called ztblDelLog
I created a macro with the run code
I created the module with the funtion

??? missing something

 

[cfbcoach]

Hello,

Tried to follow instructions but I am missing something???
I created a delete query
I created a table called ztblDelLog
I created a macro with the run code
I created the module with the funtion

??? missing something

Sorry, that wasn't meant to be detailed instructions, just meant to be a guideline, I didn't take the time to explicitly define all the tables queries and code. What you're missing are all the blanks I didn't fill in. The point was to give an idea of a method that will allow you to delete the table once a day without a user having to decide whether or not to do it. You do that by saving the date of the last time the delete query was run in the table I named "ztblDelLog." You could name the table "Fred" or "Zorax" if you wanted to, the point's just for it to have a date field in it so that you can lookup when the last time you did the delete was, and if its already been done once that day, your code can know not to run the query again.

If you post what you've got so far, I'd be happy to take a look and help you get it finished.