I want to check for duplicate date before upadating the table. In the Update Button Click Event I have used the follwoing code forcchecking duplicate (1 Viewer)

[sbaud2003]

the code is not working , even no error is there, records gets updated with duplicate date?- Please Help anyone

Dim NewDt As Date
Dim stLinkCriteria As String
NewDt = Me.txtfrom.value
stLinkCriteria = "[FROM] = " & NewID
If Me.txtfrom = DLookup("[FROM]", "[REPORT_LEAVE]", stLinkCriteria) Then
MsgBox "This Date:" & [NewID] & ", already exist taken in the Database, " _
& vbCr & vbCr & "Please enter a new Date", vbInformation, "HELLO USER.... DUPLICATE INFORMATION"
Me.Undo
End If

 

[arnelgp]

put your code in the BeforeUpdate event of your Form (not on any button)

 

[Gasman]

Why would you think a date would have the same ID?, surely you would look for the date?

 

[bastanu]

Check your spellings, you declare NewDt varaible yet you use undeclared NewID....

 

[MajP]

Option Explicit statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[Pat Hartman]

Your logic is also not complete. If you are checking to see if the date exists for a particular value of NewID, the DLookup isn't doing that. The DLookup() will return the first value it encounters for NewID which isn't necessarily the one you are looking for. If you want only a single result returned from the DLookup(), you MUST provide unique search criteria. I changed to a DCount()

Dim NewDt As Date
Dim stLinkCriteria As String
    NewDt = Me.txtfrom
    stLinkCriteria = "[FROM] = " & NewID & " AND [From] = #" & Me.From & "#")
    If Me.txtfrom = DCount("*", "[REPORT_LEAVE]", stLinkCriteria) > 0 Then
        MsgBox "This Date:" & [NewDT] & ", already exist taken in the Database, " _
            & vbCr & vbCr & "Please enter a new Date", vbInformation, "HELLO USER.... DUPLICATE INFORMATION"
        Me.Undo
        Cancel = True
        Exit Sub
    End If

PS, if your standard date format is not mm/dd/yyyy, you MUST format your date as mm/dd/yyyy or yyyy/mm/dd when you use it as a string to avoid ambiguity. Is 1/3 Jan 3rd or Mar 1st?

PS - put the validation code into the Form's BeforeUpdate event so you can prevent bad data from being saved by cancelling the save process.

 

[Gasman]

Please do us all a favour.
Put your code between code tags. Not bold tags.