ADO Error Handling

Z

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
 
D

Douglas J. Steele

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

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.



Douglas J. Steele said:
Try checking for the error immediately after the Update statement, before
closing the recordset.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Zanstemic said:
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
 
J

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.

--
 
Z

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..........
 
D

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!)


Zanstemic said:
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..........

Jamie Collins said:
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.
 

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


Top