Overflow error - not using integer right? (1 Viewer)

inkbird01

New member
Local time
Today, 08:36
Joined
Oct 5, 2022
Messages
20
Hi, my form has a textbox (txt_PIN), when the text box is updated i want to check the user table to see if it already exists. I keep getting errors, i think itt is because of the way the numerical value is being referenced?


Private Sub txt_PIN_AfterUpdate()

Dim PIN As Integer
Dim stLinkCriteria As Integer
PIN = Me.txt_PIN.Value
stLinkCriteria = "[PIN]= " & "'" & Me.txt_PIN & "'"

If Me.txt_PIN = DLookup("[PIN]", "tbl_users", stLinkCriteria) Then
MsgBox "The PIN, " & txt_PIN & ", already exists in database." _
& vbCr & vbCr & "Please choose an alternative username", vbInformation, "Warning"
Me.Undo
Me.txt_PIN.Value = ""
Me.txt_PIN.SetFocus
End If
End Sub
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:36
Joined
Feb 28, 2001
Messages
27,488
You are DIMing some variables as INTEGER, which for VBA is a 16-bit integer. However, I would suspect the table's fields were probably defined as LONG (32-bit integer) because out-of-the-box, Access defaults all integers to LONG.

As a side note, the .Value is superfluous for Me.txt_PIN.Value because if something HAS a value, then .Value is the default property. You can just call out Me.txt_PIN and you get the value anyway.

Just to clarify, that technique doesn't work for, say, a label control, because labels don't have values.
 

inkbird01

New member
Local time
Today, 08:36
Joined
Oct 5, 2022
Messages
20
You are DIMing some variables as INTEGER, which for VBA is a 16-bit integer. However, I would suspect the table's fields were probably defined as LONG (32-bit integer) because out-of-the-box, Access defaults all integers to LONG.

As a side note, the .Value is superfluous for Me.txt_PIN.Value because if something HAS a value, then .Value is the default property. You can just call out Me.txt_PIN and you get the value anyway.

Just to clarify, that technique doesn't work for, say, a label control, because labels don't have values.
Thank, i changed the table field to integer but i am getting an "Overflow" error
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:36
Joined
Feb 28, 2001
Messages
27,488
When you get that error, you should have a single line highlighted. OR if it offers to let you DEBUG the code, it would highlight the line. So... which line is highlighted?
 

inkbird01

New member
Local time
Today, 08:36
Joined
Oct 5, 2022
Messages
20
When you get that error, you should have a single line highlighted. OR if it offers to let you DEBUG the code, it would highlight the line. So... which line is highlighted?
 

Attachments

  • 2023-05-31_14-57-09.jpg
    2023-05-31_14-57-09.jpg
    26.5 KB · Views: 76

Cotswold

Active member
Local time
Today, 08:36
Joined
Dec 31, 2020
Messages
532
Integer .............: -32768 to 32767
Long Integer...: -2147483648 to 2147483647
 

ebs17

Well-known member
Local time
Today, 09:36
Joined
Feb 7, 2020
Messages
2,022
Code:
stLinkCriteria = "[PIN]= " & "'" & Me.txt_PIN & "'"
' ...
Me.txt_PIN.Value = ""
Here you treat PIN as text. For what particular reason?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:36
Joined
Feb 28, 2001
Messages
27,488
Clearly, since PIN is DIM'd as an INTEGER, Me.txt_PIN must not be an INTEGER. It is probably a LONG. Since it is happening in a context across an equals-sign (where automatic type conversion would occur if possible), the overflow MUST be that txt_PIN contains a number greater than 32767 or less than -32768 - which is therefore your source of overflow.

This wasn't a Type Mismatch error, so the problem isn't something like trying to store an all-numeric STRING. It is an overflow error, which means Access can't store the number you are trying to store in the place where you are trying to store it because it is too big.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:36
Joined
May 21, 2018
Messages
8,685
This needs to be in before update not after update. This can be simplified.
Code:
Private Sub txt_PIN_beforeUpdate(Cancel as integer)
If Dcount("*", "tbl_users", "PIN = " & nz(me.txt_Pin,0)) > 0 Then
  MsgBox "The PIN, " & me.txt_PIN & ", already exists in database." _
  & vbCr & vbCr & "Please choose an alternative username", vbInformation, "Warning"
  cancel = true
  Me.Undo
  Me.txt_PIN.SetFocus
End If
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 00:36
Joined
Mar 14, 2017
Messages
8,963
Careful with your variable Declarations vs. usage.
I support explicitily referencing Value. People who don't usually don't avoid doing so because they understand proprties and defauts, but because they don't - thus it is more a symptom of ignorance than knowledge. And for people WITH the knowledge, it takes more mental work to remember default properties than it does to just be in the habit of spelling out the property all the time.

Thus I consider that trivia but not practical.
 

Users who are viewing this thread

Top Bottom