Error "disappears" in Break Mode

[Bruce Maston]

In my A2K app, there is a place where I get a runtime error. But if I go
through that section in break mode, the error disappears.

More specifically, when I debug the line giving the error, Access says there
is a type mismatch because the variable in the line of code is Null. But the
variable is not and should not be Null. Somehow the program has wiped out
the variable.

In break mode, the variable doesn't lose it's value, and that's why there is
no error when I hit the offending line of code.

I've had a similar weird behaviour in Excel VBA, and I discovered that the
problem was caused by declaring one of my variable as Integer rather than
Long.

I've changed the Integer types to Long in my Access app, but the problem
persists. Have other encountered this problem, and what other sources of it
should I consider?

Thank you for any input.

 

[Allen Browne]

Bruce, what event is this? And what is the line of code that errors?

There are cases where break mode gives Access the chance to catch up, and so
the error disappears. For example, if you have a text box in the form
footer, and it's bound to:
=Sum([Amount])
and you have the code in Form_AfterUpdate, the total won't be calculated at
the time the code executes (so will be Null.) However, if you enter break
mode, Access does get the chance to calculate the value before you can do
anything, and so it seems that the value is there -- even though it was not
there when the code originally ran.

 

[Bruce Maston]

Allen, thank you for replying. My code doesn't involve any recalculations,
but I think your answer must point to the problem. As you say, there must be
something in my code where Access is "catching up" in break mode. Here is an
overview of my code (and this has to do with writing letters):

' One form1, there is a command button that you click to open form2

Private Sub Command_Click()
DoCmd.OpenForm "form2", , , , acFormAdd, acDialog, newdata
Response = acDataErrAdded
Sub AddToListOfRecentLetters
End Sub

'form2 has several listboxes of return addresses
'names of addresses, and
'a textbox where you give the letter a name for saving
'there is a command button on form2 Captioned "WRITE LETTER"
'clicking this button opens Word by Automation and fills in Bookmarks
'with information from the listboxes.
'As to the name you are giving the letter (to prevent duplicate names)
'there is a local table containing a counter. A sub routine picks up the
'number in the table, adds that number at the end of the name, and increments
'the table by one. (I use a Long variable type for this)
'Access "knows" there is a Word Document open because of a
"While ... Wend loops that keeks looping as long as the Document Exists
'when the Word Document is closed, the Document
'no longer exists and this kicks you out of the While ... Wend
'to close form2

On Error GoTo CloseForm2
While objWord.IsObjectValid(objWdDoc)
Wend
CloseForm2:
DoCmd.Close

'You are now back to form1, and the next line of form1
'calls a sub routine in the module
'(Sub AddToListOfRecentLetters) that inserts basic information
'about the letter (Addressee, File Name, Date of Letter)
'into a local table

Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset ' page 254,256 of Dev. Handbook
.LockType = adLockOptimistic ' page 256
.Open "RecentLetters", CurrentProject.Connection
.addnew
!clientID = lngclientID
!LetterFileName = strWdDocName & "_" & cstr(lngDocumentID)
.Update
End With
rst.Close

The bug occurs where you try to add strWdDocName to the table, and the error
message says there is a type mismatch because strWdDocName is Null. This
variable is not null. When the letter was saved by Word automation, it saved
properly under this name. Somehow, this variable has been "wiped out."

So thinking along the lines of your suggestion, is there a problem with the
DoCmd.Close command happening too fast, or is there some problem getting the
document ID number out of the local table, and why would that be? It is as
if there is some subtle error that has occurred in an earlier line of the
code that finally triggers the crash on what should be an innocuous line of
code.

Bruce, what event is this? And what is the line of code that errors?

There are cases where break mode gives Access the chance to catch up, and so
the error disappears. For example, if you have a text box in the form
footer, and it's bound to:
=Sum([Amount])
and you have the code in Form_AfterUpdate, the total won't be calculated at
the time the code executes (so will be Null.) However, if you enter break
mode, Access does get the chance to calculate the value before you can do
anything, and so it seems that the value is there -- even though it was not
there when the code originally ran.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In my A2K app, there is a place where I get a runtime error. But if I go
through that section in break mode, the error disappears.

More specifically, when I debug the line giving the error, Access says
there
is a type mismatch because the variable in the line of code is Null. But
the
variable is not and should not be Null. Somehow the program has wiped out
the variable.

In break mode, the variable doesn't lose it's value, and that's why there
is
no error when I hit the offending line of code.

I've had a similar weird behaviour in Excel VBA, and I discovered that the
problem was caused by declaring one of my variable as Integer rather than
Long.

I've changed the Integer types to Long in my Access app, but the problem
persists. Have other encountered this problem, and what other sources of
it
should I consider?

Thank you for any input.

 

[Allen Browne]

Bruce, I didn't go through that in detail, as I didn't follow the first Sub.

Perhaps:
a) Does you Command_Click need to save the record first?

b) Opening a form in dialog mode will pause Command_Click at this point
until the dialog is closed.

c) Response is not a declared variable. If you are not using Option
Explicit, it might help you track down the problem.

d) I did not understand the Sub instruction inside another sub.

Sorry: can't follow it at all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you for replying. My code doesn't involve any
recalculations,
but I think your answer must point to the problem. As you say, there must
be
something in my code where Access is "catching up" in break mode. Here is
an
overview of my code (and this has to do with writing letters):

' One form1, there is a command button that you click to open form2

Private Sub Command_Click()
DoCmd.OpenForm "form2", , , , acFormAdd, acDialog, newdata
Response = acDataErrAdded
Sub AddToListOfRecentLetters
End Sub

'form2 has several listboxes of return addresses
'names of addresses, and
'a textbox where you give the letter a name for saving
'there is a command button on form2 Captioned "WRITE LETTER"
'clicking this button opens Word by Automation and fills in Bookmarks
'with information from the listboxes.
'As to the name you are giving the letter (to prevent duplicate names)
'there is a local table containing a counter. A sub routine picks up the
'number in the table, adds that number at the end of the name, and
increments
'the table by one. (I use a Long variable type for this)
'Access "knows" there is a Word Document open because of a
"While ... Wend loops that keeks looping as long as the Document Exists
'when the Word Document is closed, the Document
'no longer exists and this kicks you out of the While ... Wend
'to close form2

On Error GoTo CloseForm2
While objWord.IsObjectValid(objWdDoc)
Wend
CloseForm2:
DoCmd.Close

'You are now back to form1, and the next line of form1
'calls a sub routine in the module
'(Sub AddToListOfRecentLetters) that inserts basic information
'about the letter (Addressee, File Name, Date of Letter)
'into a local table

Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset ' page 254,256 of Dev. Handbook
.LockType = adLockOptimistic ' page 256
.Open "RecentLetters", CurrentProject.Connection
.addnew
!clientID = lngclientID
!LetterFileName = strWdDocName & "_" & cstr(lngDocumentID)
.Update
End With
rst.Close

The bug occurs where you try to add strWdDocName to the table, and the
error
message says there is a type mismatch because strWdDocName is Null. This
variable is not null. When the letter was saved by Word automation, it
saved
properly under this name. Somehow, this variable has been "wiped out."

So thinking along the lines of your suggestion, is there a problem with
the
DoCmd.Close command happening too fast, or is there some problem getting
the
document ID number out of the local table, and why would that be? It is
as
if there is some subtle error that has occurred in an earlier line of the
code that finally triggers the crash on what should be an innocuous line
of
code.

Bruce, what event is this? And what is the line of code that errors?

There are cases where break mode gives Access the chance to catch up, and
so
the error disappears. For example, if you have a text box in the form
footer, and it's bound to:
=Sum([Amount])
and you have the code in Form_AfterUpdate, the total won't be calculated
at
the time the code executes (so will be Null.) However, if you enter break
mode, Access does get the chance to calculate the value before you can do
anything, and so it seems that the value is there -- even though it was
not
there when the code originally ran.

message

In my A2K app, there is a place where I get a runtime error. But if I
go
through that section in break mode, the error disappears.

More specifically, when I debug the line giving the error, Access says
there
is a type mismatch because the variable in the line of code is Null.
But
the
variable is not and should not be Null. Somehow the program has wiped
out
the variable.

In break mode, the variable doesn't lose it's value, and that's why
there
is
no error when I hit the offending line of code.

I've had a similar weird behaviour in Excel VBA, and I discovered that
the
problem was caused by declaring one of my variable as Integer rather
than
Long.

I've changed the Integer types to Long in my Access app, but the
problem
persists. Have other encountered this problem, and what other sources
of
it
should I consider?

 

[Bruce Maston]

Allen, I think you may have solved my problem.

I have declared
Public Response as Integer
in my module. "Response" is used over and over in my app as the value
retrieved from a message box. But I don't use Response in any other context,
and I don't update any recordsources or use it in conjunction with Requery.
Thus,
Response = acDataErrAdded
is unnecessary surplusage.
Your reply points out that my form1 is paused by acDialog, so there can't be
any need for Access to "catch up" while form2 is running. So maybe the
unnecessary use of Response is what is creating the subtle problem that is
crashing me several lines later.

Again, thank you in advance if this works!

Bruce, I didn't go through that in detail, as I didn't follow the first Sub.

Perhaps:
a) Does you Command_Click need to save the record first?

b) Opening a form in dialog mode will pause Command_Click at this point
until the dialog is closed.

c) Response is not a declared variable. If you are not using Option
Explicit, it might help you track down the problem.

d) I did not understand the Sub instruction inside another sub.

Sorry: can't follow it at all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you for replying. My code doesn't involve any
recalculations,
but I think your answer must point to the problem. As you say, there must
be
something in my code where Access is "catching up" in break mode. Here is
an
overview of my code (and this has to do with writing letters):

' One form1, there is a command button that you click to open form2

Private Sub Command_Click()
DoCmd.OpenForm "form2", , , , acFormAdd, acDialog, newdata
Response = acDataErrAdded
Sub AddToListOfRecentLetters
End Sub

'form2 has several listboxes of return addresses
'names of addresses, and
'a textbox where you give the letter a name for saving
'there is a command button on form2 Captioned "WRITE LETTER"
'clicking this button opens Word by Automation and fills in Bookmarks
'with information from the listboxes.
'As to the name you are giving the letter (to prevent duplicate names)
'there is a local table containing a counter. A sub routine picks up the
'number in the table, adds that number at the end of the name, and
increments
'the table by one. (I use a Long variable type for this)
'Access "knows" there is a Word Document open because of a
"While ... Wend loops that keeks looping as long as the Document Exists
'when the Word Document is closed, the Document
'no longer exists and this kicks you out of the While ... Wend
'to close form2

On Error GoTo CloseForm2
While objWord.IsObjectValid(objWdDoc)
Wend
CloseForm2:
DoCmd.Close

'You are now back to form1, and the next line of form1
'calls a sub routine in the module
'(Sub AddToListOfRecentLetters) that inserts basic information
'about the letter (Addressee, File Name, Date of Letter)
'into a local table

Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset ' page 254,256 of Dev. Handbook
.LockType = adLockOptimistic ' page 256
.Open "RecentLetters", CurrentProject.Connection
.addnew
!clientID = lngclientID
!LetterFileName = strWdDocName & "_" & cstr(lngDocumentID)
.Update
End With
rst.Close

The bug occurs where you try to add strWdDocName to the table, and the
error
message says there is a type mismatch because strWdDocName is Null. This
variable is not null. When the letter was saved by Word automation, it
saved
properly under this name. Somehow, this variable has been "wiped out."

So thinking along the lines of your suggestion, is there a problem with
the
DoCmd.Close command happening too fast, or is there some problem getting
the
document ID number out of the local table, and why would that be? It is
as
if there is some subtle error that has occurred in an earlier line of the
code that finally triggers the crash on what should be an innocuous line
of
code.

Bruce, what event is this? And what is the line of code that errors?

There are cases where break mode gives Access the chance to catch up, and
so
the error disappears. For example, if you have a text box in the form
footer, and it's bound to:
=Sum([Amount])
and you have the code in Form_AfterUpdate, the total won't be calculated
at
the time the code executes (so will be Null.) However, if you enter break
mode, Access does get the chance to calculate the value before you can do
anything, and so it seems that the value is there -- even though it was
not
there when the code originally ran.

message
In my A2K app, there is a place where I get a runtime error. But if I
go
through that section in break mode, the error disappears.

More specifically, when I debug the line giving the error, Access says
there
is a type mismatch because the variable in the line of code is Null.
But
the
variable is not and should not be Null. Somehow the program has wiped
out
the variable.

In break mode, the variable doesn't lose it's value, and that's why
there
is
no error when I hit the offending line of code.

I've had a similar weird behaviour in Excel VBA, and I discovered that
the
problem was caused by declaring one of my variable as Integer rather
than
Long.

I've changed the Integer types to Long in my Access app, but the
problem
persists. Have other encountered this problem, and what other sources
of
it
should I consider?