Run-Time error '91'

G

Guest

I am trying to get code to work that identifies error messages so I can
suppress certain messages. Using the following code, I get a Run-Time error
'91' Object variable or With block veriable not set. The offending line of
code is:

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

and entire 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_Handler

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

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

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

' begin transaction
wrk.BeginTrans

' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
strQuery = "1Append Rx to RX1 Query"
dbs.Execute strQuery ', dbFailOnError
' Append Patient table to Patients table "Adds new records"
strQuery = "2Append Patient to PatientsQuery"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from Patients that are "GONE" over 1 year
strQuery = "3Delete >1 years From Patients qry"
dbs.Execute strQuery ', dbFailOnError
' Update Housing Units from Patient table to Patients table
strQuery = "4Update Housing from Patient to Patients"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from RX1 that are "GONE" over 1 year
strQuery = "5RX1 Delete >1 years Query"
dbs.Execute strQuery ', dbFailOnError

' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL ', dbFailOnError

' no error so commit transaction
wrk.CommitTrans

End If
End If

Exit_Here:
Exit Sub

Err_Handler:
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
Resume Exit_Here



End Sub


I think I should Set strQuery to something but not sure what?

Thanks,

Dennis
 
D

Douglas J. Steele

Has dbs been instantiated when the error occurs? Does strQuery have a value?

It may be that your error is occurring before either of those is true. If
so, try nesting the two If statements, rather than Anding them together:

If Err.Number = 3022 Then
If dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
End If
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
 
G

Guest

Douglas,

I made the nesting change you suggested and it corrected the 91 error. But
it looks like I have an additional issue.

I now get a Access error ("The database has been placed in a state by user
'Admin' on machine 'Computer' that prevents it from being opened or locked.
(3734)

(Error in)
Transaction rolled back and not tables updated.)

I then get a (Run-Time error '3034'

You tried to commit or rollback a transaction without first beginning a
transaction.)

Debug gives this line of code:
wrk.Rollback

In my original post I did begin a transaction:

' begin transaction
wrk.BeginTrans

This is confusing can you take a look at the original code?

Thanks,
Dennis
 
G

Guest

Douglas,

I think my last post:

I now get a Access error ("The database has been placed in a state by user
'Admin' on machine 'Computer' that prevents it from being opened or locked.
(3734) (Error in) Transaction rolled back and not tables updated.)

is what I was trying to get with the code to identify errors so I could add
code to ignore some errors and maybe as a result, I get the Run-Time error
'3034'? Example of code I would use to ignore errors below:

If Err.Number = 3022 Then
If dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
End If

If Err.Number = 3734 Then
If dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
End If

But now when I run the code with these changes, it does not run or give an
error message. The following is the code with the changes: Note that I have
commented out some code to save it but not run as I am just trying to
identify error codes at this time.


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_Handler

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

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

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


' begin transaction
wrk.BeginTrans

' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
strQuery = "1Append Rx to RX1 Query"
dbs.Execute strQuery ', dbFailOnError
' Append Patient table to Patients table "Adds new records"
strQuery = "2Append Patient to PatientsQuery"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from Patients that are "GONE" over 1 year
strQuery = "3Delete >1 years From Patients qry"
dbs.Execute strQuery ', dbFailOnError
' Update Housing Units from Patient table to Patients table
strQuery = "4Update Housing from Patient to Patients"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from RX1 that are "GONE" over 1 year
strQuery = "5RX1 Delete >1 years Query"
dbs.Execute strQuery ', dbFailOnError

' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL ', dbFailOnError

' no error so commit transaction
wrk.CommitTrans

End If
End If

Exit_Here:
Exit Sub

Err_Handler:




If Err.Number = 3022 Then
If dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
End If

If Err.Number = 3734 Then
If dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
End If

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


' The below code is
'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
Resume Exit_Here



End Sub

What do you think?

Thanks,
Dennis
 
D

Douglas J. Steele

Can you determine what line of code is causing the error? Try
single-stepping through the code.
 
G

Guest

Douglas,
I am not sure what you mean. I have tried the Debug step into and Run to
Cursor and found that I get no action after doing both on each line except a
message when Run to Cursor, is run, on lines without an exacutable the
message "Line is not an exacutable statement" is reported. Are you referring
to something else? I know you can single step a macro but I do not know how
to single step code? How do you do that?

Thanks,
Dennis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suppressing Access error messages 1
Run-Time Error 3734 3
Date Time Format 6
Update Query Crashes Access -- why? 2
Error when I run Append queries 3
Allen Browne's Audit Trail code, Run-time error '3825' 6
Setting a Form's recordset at design time 4
Run-Time Error 3022 4

Top