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

[inkbird01]

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

 

[The_Doc_Man]

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]

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]

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]

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?

 

[Cotswold]

Integer .............: -32768 to 32767
Long Integer...: -2147483648 to 2147483647

 

[ebs17]

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

Here you treat PIN as text. For what particular reason?

 

[The_Doc_Man]

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]

This needs to be in before update not after update. This can be simplified.

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]

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.

 

[Pat Hartman]

Dim stLinkCriteria As Integer

stLinkCriteria = "[PIN]= " & "'" & Me.txt_PIN & "'"

You Dim as Integer but populate with a string. Change the Dim to Dim as String.