Solved: Disable the Delete Confrimation

[FrymanTCU]

I have built a tool that users enter their time to on a daily basis through some very simple VB scripting and a backend mdb with a switchboard for managers to pull reports. The management reports run off make table queries and such that their are multiple confimation boxes that pop up and users click to proceed. But my macro has a delete table then it runs the queries I need to populate the reports. I have not seen a built in macro to disable this on other user machines? Or how could someone help with the macro to run the delete queries only if if the tables exist? Thanks in advance for the help. I have pasted the code for the AutoExc Macro below, Please let me know if you need any additional information.

Option Compare Database
Option Explicit
'------------------------------------------------------------
' AutoExec_CurPayPerRefresh
'
'------------------------------------------------------------
Function AutoExec_CurPayPerRefresh()
On Error GoTo AutoExec_CurPayPerRefresh_Err
DoCmd.DeleteObject acTable, "1a - Entry Table with Missing Days"
DoCmd.DeleteObject acTable, "1b - Pull last Data from Entry Table 1a"
DoCmd.DeleteObject acTable, "1c - TimeSerialValue from Last of EntryTable"
DoCmd.DeleteObject acTable, "2 - Valid Entries with Entered Time"
DoCmd.OpenQuery "1a - Add Missing Dates to EntryTable", acViewNormal, acReadOnly
DoCmd.OpenQuery "1b - Pull Last Data from Entry Table", acViewNormal, acReadOnly
DoCmd.OpenQuery "1c - Last of Entry TimeSerialValue", acViewNormal, acReadOnly
DoCmd.OpenQuery "2-Make Final Timekeeping Table", acViewNormal, acReadOnly
DoCmd.OpenForm "Switchboard", acNormal, "", "", , acNormal
DoCmd.Maximize

AutoExec_CurPayPerRefresh_Exit:
Exit Function
AutoExec_CurPayPerRefresh_Err:
MsgBox Error$
Resume AutoExec_CurPayPerRefresh_Exit
End Function

[orange]

Not sure I fully understand the issue, but here's my thinking on what I do understand:

In your error routine you could check for error 7874 ( Microsoft Access can't find object "XXX"), and then just not provide a "successful message".
Hope this is helpful

[FrymanTCU]

I am not sure exactly which message box they are clicking no to. I think I need to run some kind of loop to see if a particular table is there then delete if not check for next and so on but I have almost zero programing skills. Also there is a message box that pops up that says something about null values in a make table query, I always select no but I am not sure why I am getting this message.

[orange]

No, they are not clicking on a message box. Your code/program/form does things behind the scenes. Once someone fills in the last name, the program does things. If as you said, it tries to Delete tables that don't exist, it will give an error message. Also in your code is an error routine that handles errors.

My suggestion was to look specifically for the 7874 error and ignore it or do something else.

There are a lot of event programs on your forms. If you didn't create them, then you should first be talking to whoever did create them. Much of the logic you have is based on "updating" fields/boxes on a form.

orange

[asingh]

Simply use a DROP TABLE [table name]...query to delete the table..and and set the warnings to false..with an error handler+resume next.

Sub dl()

On Error GoTo error_handler:

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE [TBL_NEW]"
DoCmd.RunSQL "DROP TABLE [TBL_NEW2]"
DoCmd.SetWarnings True

Exit Sub

error_handler:
Resume Next


End Sub

[mattj]

I don't recommend you set the warning to false - it's fine as long as you remember to turn them back on, but... let's just say, sometimes it doesn't happen.

The recommended method is to execute action queries using the Execute method;

CurrentDb.Execute "query name or SQL string", dbFailOnError

This will execute the qeury without any of the prompts, but if the query fails for some reason (like a table to be deleted doesn't exist) it will still generate an error. If you don't even want a message if the query fails, remove the "dbFailOnError" option.

HTH
Matt

[asingh]

The recommended method is to execute action queries using the Execute method;

CurrentDb.Execute "query name or SQL string", dbFailOnError


HTH
Matt

I tried the above...and removed the dbFailOnError....

CurrentDb.Execute "DROP TABLE [TBL_NEW]"....

It is still giving an error prompt...had removed the error handler..to see if an error is generated...?

[mattj]

See here http://support.microsoft.com/kb/291539

[FrymanTCU]

Thanks everyone for your input the drop table function worked. I will probably use this on most my reporting macros.