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
fgruhlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fgruhlAuthor Commented:
Opps, I transcribed wrong - second to last line should be:

Resume SubCombo_Exit

Thanks

Fred
kirenievsCommented:
Not sure what the problem is. But I would have changed some of the code. From where do you get the source of the combo? If from CustList table, will it not always find a record? Under you find a simple suggestion?

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 Then        
    Me.Bookmark = rs.Bookmark                   '  yes, bookmark this
Else
    DoCmd.GoToRecord , , acNewRec               '  no, start a new record
    Me.CustomerNo = dmax("CustomerNo", "CustList") + 1   ' 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jerryb30Commented:
Any other background processes which might be running?
When youy get the overflow, can you debug?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

fgruhlAuthor Commented:
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.
thenelsonCommented:
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.
jerryb30Commented:
I would have thought a data type mis-match.
But good eye.
fgruhlAuthor Commented:
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
fgruhlAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.