Why does the code execute this way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm setting up inline error handling for inserting new
records into a table to catch duplicates on a SSN field.

For some reason, on execution of my sqlError Sub, it does
not execute the code after the Select Statement. Why is
that happeneing? Instead, it jumps back to after the call
and then exits from the orignial Sub.

--- code snipped ----

On Error Resume Next
thisRS.Open "tblPatient", Main.myCnxn, adOpenKeyset, _
adLockOptimistic, adCmdTable
thisRS.AddNew
thisRS!ssn = txtSSN.Value
thisRS!f_name = txtFirst.Value
thisRS!m_name = txtMiddle.Value
thisRS!l_name = txtLast.Value
thisRS.Update

'Check for previous patient entry and exit if exists
If Err.Number = -2147217887 Then
Call sqlError(Main.errINSERT, "SSN", txtSSN.Value)
Err.Clear 'Clear out Error
Exit Sub
Else
'Get new Patient ID to be able to add case
Main.patientID = thisRS!id
End If

--- code snipped ----

---------------------

Private Sub sqlError(errType As Integer, errField As
String, Optional errFieldVal As Variant)
Dim thisError As String

Select Case errType
Case Main.errINSERT
thisError = "There is an existing record for the" _
& "field " & errField & " with value " _
& CStr(errFieldVal) & "!"
lblStatus.Caption = "Record was not added to the" _
& "database."
cmdSave.Enabled = False
Case Main.errDELETE

Case Main.errUPDATE
End Select

thisError = thisError & vbCrLf & "Can not add record!"
MsgBox thisError, vbCritical, "ERROR"

End Sub
 
There is no select statement in the code you posted. Try to comment out the
On Error Resume Next line and you will see which line causes the error. I
believe it may be the Open statement.
You are trying to open table type recordset as a keyset. Change the
parameter to adopentable or use a valid Select statement ("select * from
tblPatient")

Alex.
 
-----Original Message-----
There is no select statement in the code you posted. Try to comment out the
On Error Resume Next line and you will see which line causes the error. I
believe it may be the Open statement.
You are trying to open table type recordset as a keyset. Change the
parameter to adopentable or use a valid Select statement ("select * from
tblPatient")
Click to expand...

The Open Statement is correct.

BTW these are ADO Recordsets, not DAO, if that clarifies
anything.

Not Select Statement as in SQL, but the Select Case
Statement that appears in my Sub "sqlError".

The error occurs on the Update line as it attempts to add
a new record to the table that already has a record with
that SSN (no duplicates allowed).

Once the error occurs it follows the Call to sqlError,
notices it's an Insert Error, from the parameters I give
it. Builds the correct String, but then, instead of
executing the code after the Select Statement (and
displaying a Message Box to the user), returns program
flow back to the call, which then clears the error and
exits the sub.

This is what is confusing me. Why doesn't it continue
program execution in sqlError??
 
where are you seting the value for Main.errINSERT
It looks like it is calling the sqlError but when it gets to the select case
and uses Main.errINSERT as the first comparison. It just exits out becaue
sqlError doesn't know what Main.errINSERT is. Since sqlError doesn't have
any error checking it just returns the flow back to the caller.
Try adding On Error Resume Next on the sqlError and see what msg pups up. It
it shows up with "Can not add record" only, then it means thisMsg was never
parsed into anything.

If this is the case, you can either pass a constant (instead of the
Main.errINSERT, or pass the value or the err.number and the use a constant
(or the actual number) on your select case.

Something like this:

const errInsert as long=3
const errDelete as long=2
const errUpdate as long =1

Private Sub sqlError(errType As Integer, errField As
String, Optional errFieldVal As Variant)
on error resume next
Dim thisError As String

Select Case errType
Case errInsert
thisError = "There is an existing record for the" _
& "field " & errField & " with value " _
& CStr(errFieldVal) & "!"
lblStatus.Caption = "Record was not added to the" _
& "database."
cmdSave.Enabled = False
Case errDelete

Case errUpdate

case else
'do something else like a different error
msgbox "Don't know what happened"
exit sub
End Select

thisError = thisError & vbCrLf & "Can not add record!"
MsgBox thisError, vbCritical, "ERROR"

End Sub


Rodrigo.
 
I've figured it out, although I'm not sure why it does it.

BTW, Main.errINSERT is a Public Constant I keep in a
Module named Main (a holdover from my C/C++ programming
days).

In anycase, it was being recognized just fine. The problem
was the line:

cmdSave.Enabled = False

I commented that out and the program flow worked as
expected.

Why would setting the Enabled property to False cause the
code execution to jump out of the Sub?

Setting the Caption on my Status Label (lblStatus) doesn't
cause that.

Any ideas?
 
it's hard to tell without the error. You can just loop trough it until it
reaches the cmdsave.enabled = false and exits out. then just go to the debug
and key ?err.description and it'll give you a description for the last
error. Some controls don't have the enabled property, so it could be one of
those.

Rodrigo.
 
Arg. Okay, I finally put in Error Trapping for sqlError,
and it's because I can't disable the command button since
it has the focus.

:\

Talk about a lot of wasted time.

Thanks for replying and making me realize what an idiot I
was being :)
 
Look at it this way. Now you know that in order to save you debug time. You
have to spend some time adding some good error checking.

Btw, you are not an idiot, you were able to fix it yourself- that is what
programers do. Find a problem, ask some questions and most of the time find
the answer on your own. Others just sit there waiting for an answer.

Rodrigo.
 
Back
Top