Cancel Error Message and resume

[Wes]

Hello,

Hope you are doing well.

I have code written that outputs a table and then opens the table that
was output, selects all records, and deletes the records. The problem
is, if there are no records in the table, the code errors and stops.
Is there script I can add that will ignore the error and resume the
code?

Thanks.

 

[Douglas J. Steele]

What's your current code?

 

[Wes]

It is pretty long. Sorry.

The error occurs after the file is output, and the table is open, all
records selected, and I try to deleteallrecords.

In some cases, there are no records to delete, so I want to
automatically ignore the error and resume. I can get it to resume, but
I don't know how to ignore the error.

Thanks.

Function Output_Term_Open_Items()
On Error GoTo Output_Term_Open_Items_Err

DoCmd.Minimize
' Deloitte Banner
DoCmd.OpenForm "Deloitte Banner", acNormal, "", "", , acNormal
DoCmd.Echo True, ""
DoCmd.SetWarnings False
' TermLoanOpenItemsClear
DoCmd.OpenQuery "TermLoanOpenItemsClear", acViewNormal, acEdit
' C:\Documents and Settings\TermLoansClear.xls
DoCmd.OutputTo acTable, "TermLoanOpenItems",
"MicrosoftExcelBiff8(*.xls)", "C:\Documents and
Settings\TermLoansClear.xls", True, "", 0
' TermLoanOpenItems
DoCmd.OpenTable "TermLoanOpenItems", acViewNormal, acEdit
' SelectAllRecords
DoCmd.RunCommand acCmdSelectAllRecords
' DeleteRecord
' Delete Output Pricing
DoCmd.RunCommand acCmdDeleteRecord
' TermLoanOpenItems
DoCmd.Close acTable, "TermLoanOpenItems"
' TermLoanOpenItemsOpen
DoCmd.OpenQuery "TermLoanOpenItemsOpen", acViewNormal, acEdit
' C:\Documents and Settings\TermLoansOpen.xls
DoCmd.OutputTo acTable, "TermLoanOpenItems",
"MicrosoftExcelBiff8(*.xls)", "C:\Documents and
Settings\TermLoansOpen.xls", True, "", 0
' TermLoanOpenItems
DoCmd.OpenTable "TermLoanOpenItems", acViewNormal, acEdit
' SelectAllRecords
DoCmd.RunCommand acCmdSelectAllRecords
' DeleteRecord
' Delete Output Pricing
DoCmd.RunCommand acCmdDeleteRecord
' TermLoanOpenItems
DoCmd.Close acTable, "TermLoanOpenItems"
' TermLoanOpenItemsYS
DoCmd.OpenQuery "TermLoanOpenItemsYS", acViewNormal, acEdit
' C:\Documents and Settings\TermLoansYS.xls
DoCmd.OutputTo acTable, "TermLoanOpenItems",
"MicrosoftExcelBiff8(*.xls)", "C:\Documents and
Settings\TermLoansYS.xls", True, "", 0
' TermsOpenItemsLB
DoCmd.Close acForm, "TermsOpenItemsLB"
' TermLoanOpenItems
DoCmd.OpenTable "TermLoanOpenItems", acViewNormal, acEdit
' SelectAllRecords
DoCmd.RunCommand acCmdSelectAllRecords
' DeleteRecord
' Delete Output Pricing
DoCmd.RunCommand acCmdDeleteRecord
' TermLoanOpenItems
DoCmd.Close acTable, "TermLoanOpenItems"
' Back to Tabstrip
DoCmd.RunMacro "Back to Tabstrip", , ""


Output_Term_Open_Items_Exit:
Exit Function

Output_Term_Open_Items_Err:
MsgBox Error$
Resume Output_Term_Open_Items_Exit

End Function

 

[Douglas J. Steele]

SQL is your friend!

Rather than

DoCmd.OpenTable "TermLoanOpenItems", acViewNormal, acEdit
' SelectAllRecords
DoCmd.RunCommand acCmdSelectAllRecords
' DeleteRecord
' Delete Output Pricing
DoCmd.RunCommand acCmdDeleteRecord
' TermLoanOpenItems
DoCmd.Close acTable, "TermLoanOpenItems"

use

CurrentDb.Execute "DELETE * FROM TermLoanOpenItems", dbFailOnError

 

[Wes]

Works perfect.

Thank you very much.

SQL is your friend!

Rather than

DoCmd.OpenTable "TermLoanOpenItems", acViewNormal, acEdit
' SelectAllRecords
DoCmd.RunCommand acCmdSelectAllRecords
' DeleteRecord
' Delete Output Pricing
DoCmd.RunCommand acCmdDeleteRecord
' TermLoanOpenItems
DoCmd.Close acTable, "TermLoanOpenItems"

use

CurrentDb.Execute "DELETE * FROM TermLoanOpenItems", dbFailOnError

 

[Steve Schapel]

Wes,

Just a hint for the future... If you really think it is important for
your question to appear in more than one newsgroup (in practice this is
seldom necessary), please cross-post (i.e. address the same message
simultaneously to both groups), rather than multi-post (i.e. post a
separate copy of the message to each group). Thanks.

 

[Steve Schapel]

Mscertified,

I can't speak for other people. The reason I got bent out of shape was
because at the time there was a beautiful, sexy woman in my bed (I'm
sure my wife won't mind me referring to her in this way). I had spent
10 minutes of my time answering Wes's question in another newsgroup.
And then, when I discovered the effort had been duplicated here by Mr
Steele, I couldn't help but think of what else I could have done with
that 10 minutes. See what I mean?

Not that this is apparently directly relevant to you... but to quote
from the aforementioned Mr Steele, "If you're using Microsoft's web
interface to post, you should see an 'Advanced Options' link at the
bottom of the page. You can type the names of the various groups into
the Newsgroup box, separating each newsgroup name with a semicolon."

 

[Steve Schapel]

mscertified,

It is certainly aggravating when you invest time and effort into helping
someone, only to find that the same answer has already been provided
elsewhere by someone else.

 

[Steve Schapel]

Mscertified,

I can't speak for other people. The reason I got bent out of shape was
because at the time there was a beautiful, sexy woman in my bed (I'm
sure my wife won't mind me referring to her in this way). I had spent
10 minutes of my time answering Wes's question in another newsgroup. And
then, when I discovered the effort had been duplicated here by Mr
Steele, I couldn't help but think of what else I could have done with
that 10 minutes. See what I mean?

Not that this is apparently directly relevant to you... but to quote
from the aforementioned Mr Steele, "If you're using Microsoft's web
interface to post, you should see an 'Advanced Options' link at the
bottom of the page. You can type the names of the various groups into
the Newsgroup box, separating each newsgroup name with a semicolon."