Warn on duplicate date entry.

[dewa]

I have a data entry form linked to a table that records daily catch details (date, invoice number, buyer, kilos, price, etc). For the last 2 years I've been using the no duplicate index on the date field of the table to prevent duplicates, until he (my husband) decided that he wanted to sell his catch to different buyers on the same date. I've changed the index to allow duplicates but now I need to create a message box which warns that he is entering a duplicate date as he has a history of trying to enter the same info twice. I've found examples of code that warns on duplicate text entries but nothing for duplicate dates?

Thanks

 

[burrina]

Try this:

If DCount("MyDateField", "MyTable", "MyDateField=#" & Date & "#") > 0 Then
Select Case MsgBox("Duplicate Date, Are You Sure?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)

Case vbYes

Case vbNo

End Select
End If

HTH

 

[dewa]

Thanks burrina
I tried the code but the prompt only appears if I make the count >= 0
I thought it might have been because I'm using the form in data entry format but it didn't make any difference when I changed it. I'm also using a single form not a datasheet, could this be the problem?

 

[burrina]

So, is it working for you now? The form data entry format should not make a difference.

 

[dewa]

No not exactly the only way I can get it to work is for the warning to come up each time the date is entered even if it's not a duplicate date. DCount value >0 doesn't work only >= to?

 

[burrina]

Try adding this to the syntax: And [MyDateField] = Date Then

 

[dewa]

Thanks again
The Code now looks like this

Private Sub CatchDate_BeforeUpdate(Cancel As Integer)
If DCount("CatchDate", "CatchDetails", "CatchDate=#" & Date & "#") > 0 And [CatchDate] = Date Then
Select Case MsgBox("Duplicate Date, Are You Sure?", vbYesNo)
Case vbYes
Case vbNo
End Select
End If
End Sub

But it doesn't appear to do anything at all now? Have I added "And [CatchDat] = Date" code in the wrong place?

Sorry I'm a bit (alot) dim.:banghead:

 

[dewa]

Would it make a difference if the date in the form is a long date and the date in the table is a short date?

 

[burrina]

What kind of form are you using, is it a regular form, continuous form, datasheet?

This worked for me.

If DCount("[CatchDate]", "CatchDetails") > 1 And [CatchDate] = Date Then
Select Case MsgBox("Duplicate Date, Are You Sure?", vbYesNo)
Case vbYes

Case vbNo
Me.CatchDate = ""

End Select

End If

 

[burrina]

Hope this helps.

 

[GinaWhipp]

Perhaps this will help...
http://answers.microsoft.com/en-us/...-overlap/438dd3ad-0b6b-462d-adb7-e85bdba36138

 

[jdraw]

dewa,

When you changed the business rules to sell his catch to different buyers on the same date. and changed the index, did you /do you have a Primary key on the table involved?

Do you have a compound unique index on buyer and catchDate?

Seems to me this sort of modification to meet your new rules would help with duplicate prevention. There may be more details than have been posted.

 

[dewa]

Thank you everyone for your help on this.

The answer turned out to be really simple. I created a query that counted each catch date and then referred back to the query instead of the the source table. It may not be the best way to do it but it works.

Private Sub CatchDate_BeforeUpdate(Cancel As Integer)
If DCount("[CatchDate]", "CatchDateRepeatedQuery") > 1 Then
Select Case MsgBox("DUPLICATE CATCH DATE!! Are you sure this hasn't been entered allready?", vbYesNo)
Case vbYes

Case vbNo
Me.Undo

End Select
End If
End Sub