use error handling to assign value

[newuser44]

Hi -

I am having some trouble with some VBA code on a form. Basically the form
brings in patient abnormality data and in order to link these data to a
biopsy of the abnormality I run a SQL append query to create a new row in a
table that contains abnormality id and biopsy id. On the form users can
checkbox a variety of abnormalities and then I run a series of SQL statements
to update the table accordingly. What I would like to do is assign a value
of 1 to the variable MadeError if the error handler is used during the
process, so that my append query is not run in the event of an error.

Here is the relevant code:

The code that runs the append query:

If Me!Abn5.Value = True Then
Debug.Print MadeError
Call GoTo5
RunBx1 = "INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) " & _
"VALUES (" & [multmatch_qry_allabn_sub1].Form!PENRAD_ABNORMALITY_ID &
"," & [multmatch_qry_bx subform1].Form!MedicalID & ");"
Debug.Print RunBx1
Debug.Print MadeError
If MadeError = 0 Then DoCmd.RunSQL RunBx1
End If

The code the identifies the location on the viewable subform:

Public Sub GoTo5() 'for the 5th row

Dim MadeError As Long

'error handling
On Error GoTo Err_Click

'select second record on current subform list
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].SetFocu
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].Form.Controls!PENRAD_ABNORMALITY_ID.SetFocus
DoCmd.GoToRecord , , acFirst
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext

Exit_Click:
Debug.Print "made error after goto"
MadeError = 1
Debug.Print MadeError
Exit Sub


Err_Click:
MsgBox Err.Description
Resume Exit_Click

End Sub


When I run this when a error should occur I get the following in the
immediate window:

0
made error after goto
1
INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) VALUES (63372,3440);
0


As you can see, MadeError gets set to 0 after my docmd.run sql, and I'm not
sure why....

any help would be appreciated!

 

[Dirk Goldgar]

Hi -

I am having some trouble with some VBA code on a form. Basically the form
brings in patient abnormality data and in order to link these data to a
biopsy of the abnormality I run a SQL append query to create a new row in
a
table that contains abnormality id and biopsy id. On the form users can
checkbox a variety of abnormalities and then I run a series of SQL
statements
to update the table accordingly. What I would like to do is assign a
value
of 1 to the variable MadeError if the error handler is used during the
process, so that my append query is not run in the event of an error.

Here is the relevant code:

The code that runs the append query:

If Me!Abn5.Value = True Then
Debug.Print MadeError
Call GoTo5
RunBx1 = "INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) " &
_
"VALUES (" & [multmatch_qry_allabn_sub1].Form!PENRAD_ABNORMALITY_ID &
"," & [multmatch_qry_bx subform1].Form!MedicalID & ");"
Debug.Print RunBx1
Debug.Print MadeError
If MadeError = 0 Then DoCmd.RunSQL RunBx1
End If

The code the identifies the location on the viewable subform:

Public Sub GoTo5() 'for the 5th row

Dim MadeError As Long

'error handling
On Error GoTo Err_Click

'select second record on current subform list
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].SetFocus
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].Form.Controls!PENRAD_ABNORMALITY_ID.SetFocus
DoCmd.GoToRecord , , acFirst
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext

Exit_Click:
Debug.Print "made error after goto"
MadeError = 1
Debug.Print MadeError
Exit Sub


Err_Click:
MsgBox Err.Description
Resume Exit_Click

End Sub


When I run this when a error should occur I get the following in the
immediate window:

0
made error after goto
1
INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) VALUES
(63372,3440);
0


As you can see, MadeError gets set to 0 after my docmd.run sql, and I'm
not
sure why....

MadeError is declared in GoTo5() , and so that particular variable is local
to that procedure. Nothing that happens to MadeError in the GoTo5
procedure is going to have any effect on anything outside that procedure,
not even any other variable named MadeError. The variable named "MadeError"
that is referred to in your calling code cannot be the same variable --
either you declared a separate variable of the same name in that code (or at
the module level), or it declared implicitly the first time you used it in
the calling procedure (in which case you must not have Option Explicit
turned on, to force variables to be explicitly declared).

 

[newuser44]

Thanks Dirk....very instructive

I declared the variable MadeError at the top of the module with this:

Public MadeError As Integer

So, this begs the question, how can I define a variable in a particular sub
so that it can be made available globally to the other subs in the module
(and I could use it in my RunBx1 sub? Is there a simple way to do this?

thanks!

Hi -

I am having some trouble with some VBA code on a form. Basically the form
brings in patient abnormality data and in order to link these data to a
biopsy of the abnormality I run a SQL append query to create a new row in
a
table that contains abnormality id and biopsy id. On the form users can
checkbox a variety of abnormalities and then I run a series of SQL
statements
to update the table accordingly. What I would like to do is assign a
value
of 1 to the variable MadeError if the error handler is used during the
process, so that my append query is not run in the event of an error.

Here is the relevant code:

The code that runs the append query:

If Me!Abn5.Value = True Then
Debug.Print MadeError
Call GoTo5
RunBx1 = "INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) " &
_
"VALUES (" & [multmatch_qry_allabn_sub1].Form!PENRAD_ABNORMALITY_ID &
"," & [multmatch_qry_bx subform1].Form!MedicalID & ");"
Debug.Print RunBx1
Debug.Print MadeError
If MadeError = 0 Then DoCmd.RunSQL RunBx1
End If

The code the identifies the location on the viewable subform:

Public Sub GoTo5() 'for the 5th row

Dim MadeError As Long

'error handling
On Error GoTo Err_Click

'select second record on current subform list
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].SetFocus
Forms![MultipleMatcher]![multmatch_qry_allabn_sub1].Form.Controls!PENRAD_ABNORMALITY_ID.SetFocus
DoCmd.GoToRecord , , acFirst
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acNext

Exit_Click:
Debug.Print "made error after goto"
MadeError = 1
Debug.Print MadeError
Exit Sub


Err_Click:
MsgBox Err.Description
Resume Exit_Click

End Sub


When I run this when a error should occur I get the following in the
immediate window:

0
made error after goto
1
INSERT INTO AbnBiopsy (PENRAD_ABNORMALITY_ID, MedicalID) VALUES
(63372,3440);
0


As you can see, MadeError gets set to 0 after my docmd.run sql, and I'm
not
sure why....

MadeError is declared in GoTo5() , and so that particular variable is local
to that procedure. Nothing that happens to MadeError in the GoTo5
procedure is going to have any effect on anything outside that procedure,
not even any other variable named MadeError. The variable named "MadeError"
that is referred to in your calling code cannot be the same variable --
either you declared a separate variable of the same name in that code (or at
the module level), or it declared implicitly the first time you used it in
the calling procedure (in which case you must not have Option Explicit
turned on, to force variables to be explicitly declared).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk....very instructive

I declared the variable MadeError at the top of the module with this:

Public MadeError As Integer

So, this begs the question, how can I define a variable in a particular
sub
so that it can be made available globally to the other subs in the module
(and I could use it in my RunBx1 sub?

You can't declare a variable in the sub and have it be available to other
subs in the module. But if you declare it at the top of the module (in the
Declarations section), even without the Public attribute, it will be
available to all the procedures in the module.

So if you declared MadeError at the top of the module, as you say, then just
remove the declaration of it from your RunBx11 sub, and any other procedures
in that module. So long as you don't declare a local variable of the same
name in any of those procedures, any reference to MadeError will refer to
the module-level variable.

Incidentally, I wonder if you really want to declare MadeError as Public, as
you have. It does not have to be Public to be shared among the procedures
in the module. Declaring it Public makes it available to procedures in all
other modules in the database, as well. That may not be what you intended.

 

[newuser44]

Thanks Dirk for all your help... everything appears to be working well, and
I've learned something!