fgruhl
asked on
Overflow error
Have a question about a perplexing error message -
Upon clicking a selection in a combo box, approx. 1/4 of the time I get a "Error #: 6 Description: Overflow" message.
After clicking "OK" I try to repeat with the same selection, but then don't get the error message, but can get it,
sometimes, by selecting another CustomerNo. I put error handling in the Sub, but never see this message box.
It's frustrating because it's not repeatable; sometimes Cust #5 goes through, and sometimes not; same with all
the others.
Here's the code:
Private Sub Combo_AfterUpdate()
'
Dim rs As Object
On Error GoTo SubCombo_Err
Set rs = Me.Recordset.Clone ' set up record for bookmarking
rs.FindFirst "CustomerNo = " & Str(Nz(Me![Combo], 0)) ' look for the
' combo box selected customer
If DCount("CustomerNo", "CustList", "CustomerNo=" & _
Str(Nz(Me![Combo], 0))) > 0 Then ' is count of occurances > 0?
Me.Bookmark = rs.Bookmark ' yes, bookmark this
Else
DoCmd.GoToRecord , , acNewRec ' no, start a new record
Me.CustomerNo = Str(Nz(Me![Combo], 0)) ' and show new Customer No
End If
SubCombo_Exit: ' standard exit
Exit Sub
SubCombo_Err:
MsgBox "Customer Database error: " & Err, vbCritical ' opps
Resume SubClick_Exit
End Sub
CustomerNo is formated as long integer, and may get up to 10E7. Any idea why
I'm getting this message? I don't get my MsgBox popping up, and setting breakpts.
never catches this. Should I be specifying a range for the DCount (it's less than
255)? I suspect this is something obvious that a newbie, like me, wouldn't see.
Fred
Upon clicking a selection in a combo box, approx. 1/4 of the time I get a "Error #: 6 Description: Overflow" message.
After clicking "OK" I try to repeat with the same selection, but then don't get the error message, but can get it,
sometimes, by selecting another CustomerNo. I put error handling in the Sub, but never see this message box.
It's frustrating because it's not repeatable; sometimes Cust #5 goes through, and sometimes not; same with all
the others.
Here's the code:
Private Sub Combo_AfterUpdate()
'
Dim rs As Object
On Error GoTo SubCombo_Err
Set rs = Me.Recordset.Clone ' set up record for bookmarking
rs.FindFirst "CustomerNo = " & Str(Nz(Me![Combo], 0)) ' look for the
' combo box selected customer
If DCount("CustomerNo", "CustList", "CustomerNo=" & _
Str(Nz(Me![Combo], 0))) > 0 Then ' is count of occurances > 0?
Me.Bookmark = rs.Bookmark ' yes, bookmark this
Else
DoCmd.GoToRecord , , acNewRec ' no, start a new record
Me.CustomerNo = Str(Nz(Me![Combo], 0)) ' and show new Customer No
End If
SubCombo_Exit: ' standard exit
Exit Sub
SubCombo_Err:
MsgBox "Customer Database error: " & Err, vbCritical ' opps
Resume SubClick_Exit
End Sub
CustomerNo is formated as long integer, and may get up to 10E7. Any idea why
I'm getting this message? I don't get my MsgBox popping up, and setting breakpts.
never catches this. Should I be specifying a range for the DCount (it's less than
255)? I suspect this is something obvious that a newbie, like me, wouldn't see.
Fred
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any other background processes which might be running?
When youy get the overflow, can you debug?
When youy get the overflow, can you debug?
ASKER
Re: source of the code - this was modified from that generated from a wizard to locate a record per combo box entry.
Thanks for the suggested code; DCount was the only way I could get the FindFirst to recognize the first entry (if found);
I'll try your suggestion to see if it works and improves the original problem.
Re: background processes - this was the only application running at the time. When the msg appeared, it would lock
all other input until OK was selected; there was then no trace of values in the VB window. Breakpoints always provided
the correct responses, but never coincided with the overflow error.
Thanks for the suggested code; DCount was the only way I could get the FindFirst to recognize the first entry (if found);
I'll try your suggestion to see if it works and improves the original problem.
Re: background processes - this was the only application running at the time. When the msg appeared, it would lock
all other input until OK was selected; there was then no trace of values in the VB window. Breakpoints always provided
the correct responses, but never coincided with the overflow error.
I think you were getting your overflow error on the line:
"CustomerNo = " & Str(Nz(Me![Combo], 0))
You were passing a long a string. The Ascii value of the string could easily get over the max value of a long.
kirenievs changed that to the correct:
"CustomerNo = " & Nz(Me![Combo], 0)
which I think will fix the overflow problem.
Nice to see an overflow problem -- brings me back about 40 years.
"CustomerNo = " & Str(Nz(Me![Combo], 0))
You were passing a long a string. The Ascii value of the string could easily get over the max value of a long.
kirenievs changed that to the correct:
"CustomerNo = " & Nz(Me![Combo], 0)
which I think will fix the overflow problem.
Nice to see an overflow problem -- brings me back about 40 years.
I would have thought a data type mis-match.
But good eye.
But good eye.
ASKER
Thanks for the suggestion. I'm having difficulty in pulling up the dbm file now - server is backing up - but will try this.
Question though - if "CustomerNo = " & Str(Nz(Me![Combo],0)) exceeded the max value of a long, how come this
was flagged an error only some of the time (for the same value of Combo)?
I too remember using overflows in limiting looping (1620 and 7090 era).
Fred
Question though - if "CustomerNo = " & Str(Nz(Me![Combo],0)) exceeded the max value of a long, how come this
was flagged an error only some of the time (for the same value of Combo)?
I too remember using overflows in limiting looping (1620 and 7090 era).
Fred
ASKER
Changed the rs.nomatch to "rs.nomatch = false" to get the right branch in kirenievs code, but had no luck. Tried a number of variations of that (e.g. CLng(Str conversion, using original DCount test, etc.) but still was getting occasional error, although it was trapping in my SubCombo_Err routine.
Then I went back to the original code, and ran "Compact and Repair" and got the original code to work! Perhaps the compiler is smart enough to know that certain newbies will get the type wrong in those statements. However, I've
changed the code to incorporate most of kirenievs suggestion, and this works as well. So I don't know why it was pushing the "overflow error" in the original version (needed garbage collection?) but it's working now with the "if rs.nomatch = false then" correction and keeping the mostly original Me.CustomerNo = Nz(Me![Combo],0). Thus, the final code looks like:
Private Sub Combo_AfterUpdate()
'
Dim rs As Object
On Error GoTo SubCombo_Err
Set rs = Me.Recordset.Clone ' set up record for bookmarking
rs.FindFirst "CustomerNo = " & Nz(Me![Combo], 0) ' look for the
' combo box selected customer
If rs.nomatch = false Then
Me.Bookmark = rs.Bookmark ' yes, bookmark this
Else
DoCmd.GoToRecord , , acNewRec ' no, start a new record
Me.CustomerNo = Nz(Me![Combo],0) ' and show new Customer No
End If
SubCombo_Exit: ' standard exit
Exit Sub
SubCombo_Err:
MsgBox "Customer Database error: " & Err, vbCritical ' opps
Resume SubCombo_Exit
End Sub
Then I went back to the original code, and ran "Compact and Repair" and got the original code to work! Perhaps the compiler is smart enough to know that certain newbies will get the type wrong in those statements. However, I've
changed the code to incorporate most of kirenievs suggestion, and this works as well. So I don't know why it was pushing the "overflow error" in the original version (needed garbage collection?) but it's working now with the "if rs.nomatch = false then" correction and keeping the mostly original Me.CustomerNo = Nz(Me![Combo],0). Thus, the final code looks like:
Private Sub Combo_AfterUpdate()
'
Dim rs As Object
On Error GoTo SubCombo_Err
Set rs = Me.Recordset.Clone ' set up record for bookmarking
rs.FindFirst "CustomerNo = " & Nz(Me![Combo], 0) ' look for the
' combo box selected customer
If rs.nomatch = false Then
Me.Bookmark = rs.Bookmark ' yes, bookmark this
Else
DoCmd.GoToRecord , , acNewRec ' no, start a new record
Me.CustomerNo = Nz(Me![Combo],0) ' and show new Customer No
End If
SubCombo_Exit: ' standard exit
Exit Sub
SubCombo_Err:
MsgBox "Customer Database error: " & Err, vbCritical ' opps
Resume SubCombo_Exit
End Sub
ASKER
Resume SubCombo_Exit
Thanks
Fred