[AccessD] Check for Duplicates

Change...

    OXK = Me.LockerNumber.Value

    stLinkCriteria = "[LockerNumber]=" & "'" & OXK & "'"

To...

    stLinkCriteria = "[LockerNumber]=" & Val(Me.LockerNumber)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Thursday, November 05, 2009 9:16 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Check for Duplicates

This should be easy, but I get confused on this all the time. I know why this doesn't work - it is because LockerNumber is a number field & not a text field. How do I make it work with a number field instead of a text field?

 

Private Sub LockerNumber_BeforeUpdate(Cancel As Integer)

   Dim OXK As String

   Dim stLinkCriteria As String

   Dim rsc As DAO.Recordset

 

    Set rsc = Me.RecordsetClone

 

If Not IsNull(Me.LockerNumber) Then

    OXK = Me.LockerNumber.Value

    stLinkCriteria = "[LockerNumber]=" & "'" & OXK & "'"

    

    'Check for duplicate

   If DCount("LockerNumber", "tbl_Locker", _

             stLinkCriteria) > 0 Then

        'Undo duplicate entry

       Me.Undo

        'Message box warning of duplication

      If MsgBox("Locker " _

            & OXK & " has already been assigned." _

            & vbCr & vbCr & "Would you like to view to the record?", _

                   vbYesNo, "Duplicate Record") = vbYes Then

              rsc.FindFirst stLinkCriteria

             Me.Bookmark = rsc.Bookmark

                Else

            Exit Sub

    End If

        End If

            End If

Set rsc = Nothing

End Sub

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com