how to prevent duplicate date entry in access form ?

[kanxay]

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]

No duplicate dates, period? Or, no duplicate dates for the same person?

 

[kanxay]

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]

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]

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]

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:

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]

For that case, you can use the Date Control's BeforeUpdate event. Here's an example:

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.

 

[pbaldy]

Looks like DBguy is offline. VBA goes here:

First VBA

 

[zeroaccess]

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]

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]

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.