data type mismatch duplicates

[trey3143]

Have this code to check for duplicates and if I change my BoxID to a text and run it as a string it works, but I need it to be a Long Integer. The data mismatch I am getting is underlined there are two. Thanks

Private Sub BoxID_AfterUpdate()

'*********************************
'Code sample courtesy of srfreeman
'*********************************

    Dim BoxID As Integer
    Dim stLinkCriteria As Integer
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    BoxID = Me.BoxID.Value
    [B][U]stLinkCriteria = "[BoxID]=" & "'" & BoxID & "'"[/U][/B]

    'Check CONTAINR table for duplicate BoxID
    [U][B]If DCount("BoxID", "CONTAINR", _
              stLinkCriteria) > 0 Then[/B][/U]        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning BoxID " _
             & BoxID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original BoxID
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

[Stewart Ross]

Hi. If your BoxID value is a long integer you don't need the single quotes on either side of the value in your stLinkCriteria where clause. The use of the single quotes sets the type of that value to Text, which I think will be the cause of your type mismatch.

stLinkCriteria = "[BoxID]=" & BoxID

-Stewart

[trey3143]

I plugged that in and it didnt work and I though about what you said and I changed stLinkCriteria to string and left boxID as integer and it worked. Thanks so much, you saved me alot of time today.

[trey3143]

for any future people that use this to check duplicates, change the boxID to a double and the table field to double, if you dont you get an error with over 6 numbers in field

[Stewart Ross]

Hi again. Double is not the right choice for whole-number values; use Long instead. The range limitations for Integer and Long values are as follows (as listed in the MS Help information):

Integer 2 bytes -32,768 to 32,767
Long (long integer) 4 bytes -2,147,483,648 to 2,147,483,647

Double is good for working with non-whole number decimal values, but because of inherent limits to the precision of the way numbers are represented double-precision values may be subject to slight round-off errors that make them a poor choice for working with whole numbers. There is no such issue at all with Long integer values.

-Stewart

[trey3143]

That makes sense, I will have to go back and change that. Thanks