Suppressing Access error messages

[Guest]

I have been working with an error messages issue and I have not actually
gotten it to work exactly as I hoped and now not at all. This is a Timer Form
and I have created a test button on the form to work with the code, that
somewhat works, without messing up the original timer code. The following
code does not stop the Access error windows as I thought they would for the
Append queries and does not create an error window giving me the error number
in the error window so I can figure out what they are to stop them by adding
them into my code.

Below is what I have, note that I have Commented out some of the action
queries and commands to prevent those actions just to test this issue. It may
be possible that I need one of these to make the other code work properly but
I don't think so. The beginning issue (beginning because I cannot get any
farther) is when run, I get the original message I was trying to get rid of
"Can't Append records... and Key Voilations..." then when I choose OK, I get
a visual basic error "Run-time error 3265" "Item not found in this
collection" The code line is as follows:

If err.Number = 3022 And dbs.QueryDefs (strQuery).Type = dbQAppend Then

The entire visual basic code is as follows:

Private Sub Command8_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strSQL As String, strQuery As String, strMessage As String

strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
Format(Date, "\#mm/dd/yyyy\#")

On Error GoTo Err_Command8_Click


If Time() > #4:30:00 AM# Then

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb

' begin transaction
wrk.BeginTrans

If DLookup("LastTimerDate", "tblTimerDate") < Date Then

' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
DoCmd.OpenQuery "1Append Rx to RX1 Query", acNormal, acAdd
dbs.Execute strQuery, dbFailOnError

' Append Patient table to Patients table "Adds new records"
'DoCmd.OpenQuery "2Append Patient to PatientsQuery", acNormal, acAdd
'dbs.Execute strQuery, dbFailOnError

' Deletes records from Patients that are "GONE" over 1 year
DoCmd.OpenQuery "3Delete >1 years From Patients qry", acNormal, acEdit
dbs.Execute strQuery, dbFailOnError

' Deletes records from RX1 that are "GONE" over 1 year
'DoCmd.OpenQuery "4RX1 Delete >1 years Query", acNormal, acEdit
'dbs.Execute strQuery, dbFailOnError

' Deletes Bus table to prepare for new day's import update
DoCmd.OpenQuery "6DeleteBusTableQry", acNormal, acEdit
dbs.Execute strQuery, dbFailOnError

If (Len(Dir("C:\Documents and Settings\D. Brown\My
Documents\Access\RC\BUS.txt"))) Then
DoCmd.TransferText acImportFixed, "BUS Import Specification", "BUS",
"C:\Documents and Settings\D. Brown\My Documents\Access\RC\BUS.txt", False, ""
End If

' Make Table "BUSUpdateTbl" Housing Units from Bus.txt
DoCmd.OpenQuery "5HousingPrepqry", acNormal, acAdd
dbs.Execute strQuery, dbFailOnError

' Update Housing from Bus.txt to Patients table
DoCmd.OpenQuery "6UpdateBusHousingQry", acNormal, acAdd
dbs.Execute strQuery, dbFailOnError

' Makes Table for the Pill Line list
'DoCmd.OpenQuery "7MakePillLineTable", acNormal, acAdd
'dbs.Execute strQuery, dbFailOnError


' no error so commit transaction
wrk.CommitTrans


CurrentDb.Execute _
"UPDATE tblTimerDate SET LastTimerDate = " & _
Format(Date, "\#mm/dd/yyyy\#")

'DoCmd.Quit

End If
Err_Command8_Click:
If Err.Number = 3022 And dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
Else
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine
& _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
End If


End If


End Sub


Can someone take a look and see what I did or did not do?

Thanks,

Dennis

 

[Guest]

You should start by adding to your code an error capture
Private Sub Command8_Click()
on error goto Command8_Err

Your code


Command8_Exit:
Exit Sub
Command8_Err:
msgbox error
resume Command8_Exit
End Sub

When You'll get and error it will go to the Command8_Err and display a
message of your error. press Ctrl+Break.
Now you can check the error number. using the err
Navigate using the F8 key.
writing only resume after the msgbox will move you to the line that failed.
After you find out what the err number, you can put a condition that not to
displey the error message on certain error number.
you can move also to the next line and skip the query with the problem and
move to the next on by writing - resume next.

I hope I undstood what you wanted.