Solved Codes to avoid Duplicate Entry (1 Viewer)

sbaud2003

Member
Local time
Today, 17:53
Joined
Apr 5, 2020
Messages
172
Hi friends the following codes works fine to find the record (duplicate) but I want to display the message if the Date of Returned is Null (Filed= date_returned) How to do that , I tried with the code (stLinkCriteria = "[BOOK_FK] = " & NewID and isnull (Date_returned) in a different way but error data mismatch.

Private Sub TXTBKID_BeforeUpdate(Cancel As Integer)
Dim NewID As String
Dim stLinkCriteria As String
NewID = Me.TXTBKID.Value
stLinkCriteria = "[BOOK_FK] = " & NewID

If Me.TXTBKID = DLookup("[BOOK_FK]", "[LOAN]", stLinkCriteria) Then
MsgBox "This BOOK ID: " & [NewID] & ", Already exist taken in the Database, " _
& vbCr & vbCr & "Please Check enter the correct BOOK ID", vbInformation, "HELLO USER.... DUPLICATE INFORMATION"
Me.Undo
'undo the process and clear all fields
Me.SetFocus
End If

End Sub


Please help me
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:23
Joined
Sep 21, 2011
Messages
14,051
You will need to specifically return the date field as well, either with another DlookUp or perhaps a recordset if you are going to start wanting more than one item of information.?

If you are trying to combine the criteria then try along the lines of
Code:
stLinkCriteria = "[BOOK_FK] = " & NewID & " AND [DateField] IS NULL"
 

sbaud2003

Member
Local time
Today, 17:53
Joined
Apr 5, 2020
Messages
172
You will need to specifically return the date field as well, either with another DlookUp or perhaps a recordset if you are going to start wanting more than one item of information.?

If you are trying to combine the criteria then try along the lines of
Code:
stLinkCriteria = "[BOOK_FK] = " & NewID & " AND [DateField] IS NULL"
THANKS
ITS WRKING FINE
CAN IT BE USED WITH DCOUNT ALSO
DCount("*", "LOAN", "isnull(Date_Returned)") & "AND '[MEMBER_FK]=FORMS![FRMLOAN]!MEMBER_FK]" '")
 

sbaud2003

Member
Local time
Today, 17:53
Joined
Apr 5, 2020
Messages
172
You would use the same criteria structure.

Hi I am using the codes below in the criteria:- data mismatch error coming (Member_fk) field is string and in on eit is numbr
stLinkCriteria = "[MEMBER_FK] = " & NewID & " AND [Date_Returned] IS NULL"
T = DCount("[LOAN_ID]", "[LOAN]", stLinkCriteria)
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:23
Joined
Sep 21, 2011
Messages
14,051
If a control value is text enclose in single quotes, if date use # and numerics use nothing.

FWIW any ID I always think of as numeric. So if the BOOK_FK was numeric, so should the MEMBER_FK as that should match the ID from the Member table?
 

Users who are viewing this thread

Top Bottom