Help with changing from string to double

[DAHMB]

The following codes works when Lines 2 and 3 are strings but when I change them to doubles it does not work. I believe my error lies in line 9 but I need help in fixing it.

Thanks
Dan

Private Sub SPBI_BeforeUpdate(Cancel As Integer)
    Dim SID As Double
    Dim stLinkCriteria As Double
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me![SPBI]
    stLinkCriteria = "[SPBI]=" & "'" & SID & "'"

    'Check tblSexOffenders table for duplicate SPBI Number
    If DCount("SPBI", "tblSexOffenders", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning SPBI Number " _
             & SID & " has already been used." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Employee ID Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

[ADezii]

The following codes works when Lines 2 and 3 are srings but when I changge them to doubles it does not work. I believe my error lies in line 9 but I need help in fixing it.

Thanks
Dan

Private Sub SPBI_BeforeUpdate(Cancel As Integer)
    Dim SID As Double
    Dim stLinkCriteria As Double
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me![SPBI]
    stLinkCriteria = "[SPBI]=" & "'" & SID & "'"

    'Check tblSexOffenders table for duplicate SPBI Number
    If DCount("SPBI", "tblSexOffenders", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning SPBI Number " _
             & SID & " has already been used." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Employee ID Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

If SID is truly a DOUBLE Precision Data Type:

stLinkCriteria = "[SPBI]=" & SID

[DAHMB]

SID is an integer that under field size is set for double. Do I have this formated wrong?

[ADezii]

SID is an integer that under field size is set for double. Do I have this formated wrong?

If SID will 'always' be an INTEGER, define it as such, and use the syntax that I have showed you.

[DAHMB]

Now I get a runtime error 6 overflow on line 8

[DAHMB]

I got it !!!!!!

Wierd but I used your code for line 9 and changed lines 2 and three to string as follows:

Private Sub SPBI_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me![SPBI]
    stLinkCriteria = "[SPBI]=" & SID

    'Check tblSexOffenders table for duplicate SPBI Number
    If DCount("SPBI", "tblSexOffenders", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning SPBI Number " _
             & SID & " has already been used." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original SPBI ID Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

Thanks for the help!

[ADezii]

Now I get a runtime error 6 overflow on line 8

SID is probably a LONG Integer (Whole Number > 32,767), so Declare it as such, and maintain Line #9:

Dim SID As Long

stLinkCriteria = "[SPBI]=" & SID

[DAHMB]

I changed SID to Long and it works perfectly! Thank you!

[ADezii]

I changed SID to Long and it works perfectly! Thank you!

You are quite welcome.