how to prevent duplicate date entry in access form ? (1 Viewer)

kanxay

Registered User.
Local time
Today, 04:54
Joined
May 18, 2019
Messages
37
how to prevent duplicate date entry in access form ?
i have a simplify form with name, age, phone number and date collected.
i want the user cannot entry the form with same date or duplicate date how can i do ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,357
No duplicate dates, period? Or, no duplicate dates for the same person?
 

kanxay

Registered User.
Local time
Today, 04:54
Joined
May 18, 2019
Messages
37
No duplicate dates, period? Or, no duplicate dates for the same person?
[/QUOTE
excuse me (all of my fields are numeric and date ) i mean that when i entry the form with date and save to table, how can i prevent entry form with the same date that appear in table
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,357
Hi. So, if you don't want any duplicate dates at all, you can simply create a Unique Index on the date field. You do this in the Design View of the table.
 

kanxay

Registered User.
Local time
Today, 04:54
Joined
May 18, 2019
Messages
37
Hi. So, if you don't want any duplicate dates at all, you can simply create a Unique Index on the date field. You do this in the Design View of the table.
but ! i want alert box or pop up message to let's user know the duplicate date when they entry the form access
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,357
but ! i want alert box or pop up message to let's user know the duplicate date when they entry the form access
For that case, you can use the Date Control's BeforeUpdate event. Here's an example:

Code:
Private Sub DateControlName_BeforeUpdate (Cancel As Integer)

If DCount("*","TableName","DateFieldName=#" & Me.DateControlName & "#")>0 Then
    Cancel=True
    Me.DateControlName.Undo
    MsgBox "You entered a duplicate date. Please try again.", vbInformation,"Duplicate Date!"
End If

End Sub
Hope that helps...
 

kanxay

Registered User.
Local time
Today, 04:54
Joined
May 18, 2019
Messages
37
For that case, you can use the Date Control's BeforeUpdate event. Here's an example:

Code:
Private Sub DateControlName_BeforeUpdate (Cancel As Integer)

If DCount("*","TableName","DateFieldName=#" & Me.DateControlName & "#")>0 Then
    Cancel=True
    Me.DateControlName.Undo
    MsgBox "You entered a duplicate date. Please try again.", vbInformation,"Duplicate Date!"
End If

End Sub
Hope that helps...
excuse me , is this VBA code ? i don't know how to use and where to put on ?
could you help one more please ? and i can't upload file to show because The uploaded file does not have an allowed extension.
 

zeroaccess

Active member
Local time
Today, 06:54
Joined
Jan 30, 2020
Messages
671
but ! i want alert box or pop up message to let's user know the duplicate date when they entry the form access
If you turn on Indexing on the date field, with No Duplicates, users will get a message when entering a date that already exists. It seems odd that you would want to restrict this, though.
 

kanxay

Registered User.
Local time
Today, 04:54
Joined
May 18, 2019
Messages
37
If you turn on Indexing on the date field, with No Duplicates, users will get a message when entering a date that already exists. It seems odd that you would want to restrict this, though.
turn on Index on the date field, (With No Duplicates) the message box alert on table but not effect on the access Form of that table ???
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:54
Joined
Aug 30, 2003
Messages
36,118
The index should work, but not until the record is saved (or attempted to). Code like that in post 6 lets you catch it as soon as the user enters the value.
 

Users who are viewing this thread

Top Bottom