ADO Error Handling

[Zanstemic]

I'm running the following and having trouble getting the error handling
worked out. The casenumber is an indexed field with no duplicates. I'm
testing the error that would occur is a duplicate exists.

The error message is on .update and the window comes to the screen saying
that a duplicate casenumber exists as expected. I've tried a number of
different approaches but can't seem to get error handling to work. The
debugger comes up and takes me to the .update

Do While True
Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from Attendees; " '
.AddNew
!CPSAutoNumber = vbIncrementNumber
!CaseNumber = vbCaseNumber
![Date Received] = Date
.Update

End With
rst.Close
Set rst = Nothing

If Err.Number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
vbIncrementNumber = vbIncrementNumber + 1

Else
' okay it worked, this number now belongs to us
Exit Do
End If
Loop

 

[Douglas J. Steele]

Try checking for the error immediately after the Update statement, before
closing the recordset.

 

[Zanstemic]

If I put it after "If Err.Number...." and before "End With" I get an error
that End With without With. If I place it after End With, I get the error on
..Udate and the number is a duplicate and breaks the validation rule.

Try checking for the error immediately after the Update statement, before
closing the recordset.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm running the following and having trouble getting the error handling
worked out. The casenumber is an indexed field with no duplicates. I'm
testing the error that would occur is a duplicate exists.

The error message is on .update and the window comes to the screen saying
that a duplicate casenumber exists as expected. I've tried a number of
different approaches but can't seem to get error handling to work. The
debugger comes up and takes me to the .update

Do While True
Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from Attendees; " '
.AddNew
!CPSAutoNumber = vbIncrementNumber
!CaseNumber = vbCaseNumber
![Date Received] = Date
.Update

End With
rst.Close
Set rst = Nothing

If Err.Number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
vbIncrementNumber = vbIncrementNumber + 1

Else
' okay it worked, this number now belongs to us
Exit Do
End If
Loop

 

[Jamie Collins]

On Jan 31, 2:16 am, Zanstemic <[email protected]> >
If I put it after "If Err.Number...." and before "End With" I get an
error

that End With without With.

You'd get that error if you forgot to also add and End If i.e. the
error message is spurious because although it says the End With is
missing it's actually a missing End If that's the real problem. Demo:

Sub main()

Dim col As Collection
Set col = New Collection
With col

On Error Resume Next
col.Add "One", 1

If Err.Number <> 0 Then
MsgBox "{{ERROR}}"
' End If

On Error GoTo 0

End With ' <<< compile error here

End Sub

In conclusion, try it again

PS do you really need to fetch the entire contents of the table merely
to be able to add a new row e.g. why not simply use an INSERT INTO
statement?

Jamie.

--

 

[Zanstemic]

I'm so glad you asked about the Insert Into

This was the other approach that I tried and I get an error on the
db.Execute. I declare a variable Dim db but not really sure what the db is
for.........

jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber, CaseNumber)
VALUES (Date(), vbIncrementNumber, vbCaseNumber);"
db.Execute jetSQL, dbFailOnError

Thanks so much for the help..........

 

[Douglas J. Steele]

The variable names need to be outside of the quotes:

jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber, CaseNumber)
" & _
"VALUES (Date(), " & vbIncrementNumber & ", " & vbCaseNumber & ");"

That assumes that both CPSAutoNumber and CaseNumber are numeric fields. If
they're text, you need quotes around the values. For instance, if CaseNumber
is text, you'd need

jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber, CaseNumber)
" & _
"VALUES (Date(), " & vbIncrementNumber & ", '" & vbCaseNumber & "');"

where, exagerated for clarity, that second line is

"VALUES (Date(), " & vbIncrementNumber & ", ' " & vbCaseNumber & " ' );"

If you were trying to pass a date value from a variable, you'd need to
ensure that it was delimited with # symbols, and was in a format Access will
recognize (eg. it will not recognize dd/mm/yyyy, regardless of what your
regional settings may be):

jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber, CaseNumber)
" & _
"VALUES (" & Format(vbDateValue, "\#yyyy\-mm\-dd\#") & ", " & _
vbIncrementNumber & ", " & vbCaseNumber & ");"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm so glad you asked about the Insert Into

This was the other approach that I tried and I get an error on the
db.Execute. I declare a variable Dim db but not really sure what the db is
for.........

jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber,
CaseNumber)
VALUES (Date(), vbIncrementNumber, vbCaseNumber);"
db.Execute jetSQL, dbFailOnError

Thanks so much for the help..........

On Jan 31, 2:16 am, Zanstemic <[email protected]> >
If I put it after "If Err.Number...." and before "End With" I get an
error

You'd get that error if you forgot to also add and End If i.e. the
error message is spurious because although it says the End With is
missing it's actually a missing End If that's the real problem. Demo:

Sub main()

Dim col As Collection
Set col = New Collection
With col

On Error Resume Next
col.Add "One", 1

If Err.Number <> 0 Then
MsgBox "{{ERROR}}"
' End If

On Error GoTo 0

End With ' <<< compile error here

End Sub

In conclusion, try it again

PS do you really need to fetch the entire contents of the table merely
to be able to add a new row e.g. why not simply use an INSERT INTO
statement?

Jamie.