What’s the best way to stop duplicate date entries?

[martinr]

We’re using a single table to record daily data – the date field is set as the pk so only one record per date is allowed.
If the user enters a date (when creating a new record) that already exists in the table what is the best way to
a) avoid the Access error message about duplicate values
and
b) allows the user to enter an alternative date for the new record?

 

[MarkK]

A date is not a great choice for a unique ID because a date variable also allows room for the time of day.
If you enter a date like 1/23/12, it is stored as 1/23/12 00:00 (midnight), but your 'unique' index will allow this date too: 1/23/12 00:01 (one second past midnight).
Mark

 

[nanscombe]

1) Make sure the format of your TextBox is set to Short Date. This should stop people entering in a time (by accident).

2) Put some code into the BeforeUpdate event of the textbox, something like the following:

Private Sub YourDate_BeforeUpdate(Cancel As Integer)
Dim lngCount As Long
' See if any records already exist with this date
' I use the format of yyyymmdd of both side to avoid any ambiguity.

If DCount("YourDate", "YourTable", "Format(YourDate, 'yyyymmdd') = " & Format(Me.YourDate, "yyyymmdd")) > 0 Then

' If any records exist show an error message
    MsgBox ("This date already exists. Please try again")

' Cancel the update
    Cancel = True

End If
End Sub

If the TextBox were a ComboBox instead, filled with "Select Distinct YourDate from YourTable Order By 1", then people could see what values were in there already.

I hope this is useful.

I have to agree about the pk though.

 

[nanscombe]

I've attached a couple of examples of how I deal with finding records by date.

I hope you find it of interest.

 

[vbaInet]

It just seems odd to me that there will only be one transaction per day. I suspect that you actually want a ClientID and the Date in the validation.

 

[nanscombe]

... And then again, it could simply be a Daily Log.

A simple date and a memo field.

Or a Log with the date, the Duty Manager / Engineer and a subform containing events / transactions for the day in a separate table.

As long as the main table only contains information relevant to that date the idea is a valid one.

I guess not everyone needs such an elaborate system as we are probably used to.


Martinr is the one in the position to know.

 

[vbaInet]

Martinr is the one in the position to know.

That was why put it out there for him to rack his brains.

 

[martinr]

Thanks to all the suggestions posted - i've been offline for a week but
will test the ideas put forward. I understand the point from 'lagbolt' about
(not) using the date as the pk - so would you recommend having a separate
pk and just setting the date field to not allow duplicates or is there another way
you would recommend doing it?
Btw, the main purpose of the db is to track sales for charity stalls - each stall can only have one 'sales' figure for each day/date.
As it isn't always the same user recording the data the user needs to know that
the sales for date 'x' have already been recorded - otherwise there is a risk of having duplicate records for same dates etc; if that makes sense...

 

[vbaInet]

If you're going to create an extra Number field I would advise that you save the Date value into the Number field as well as keep the Date in the Date/Time field. So the Number field will hold the Long value of the date whilst the Date/Time field will hold the actual Date.

For the Number field here's what you do:

1. create a Number field
2. set the Field Size to Long
3. set the Default Value to Date()
4. set the PK on this field.

 

[martinr]

Thanks nanscombe for your example.
Thanks vbaInet i'll try your suggestion re the fields.
What do you recommend as the best way to lock/unlock the
form for editing using vba ie; using an event linked to a button
on the form to change the form property so the user has to
'unlock' the form to edit the record(?)

 

[vbaInet]

* Are you talking about locking/unlocking the form or the record?
* Is the form a Single Form, Continuous Form or in Datasheet View?

 

[martinr]

Thanks vbaInet - the form design is a single form - one record displayed at time.
I don't know exactly the difference between locking the form & record but
locking the form sounds a bit too restrictive. The objective is simply to ensure
that the user is made aware/alerted before they edit existing data.
My idea was to add a button that "unlocks/locks" the displayed record;
the default setting would probably be that the record is locked for editing
unless a) it's a new record OR b) the user chooses to edit an existing record.

 

[vbaInet]

Yes you will need to use a button for this.

* Toggle the Caption of the button between Lock and Unlock
* Toggle the form's Allow Edits property depending on the Caption of the button
* Set the Allow Edits property of the form to False in the After Update event of the form

You will also need to create a New Record button. If you don't want to do this then you need to re-think how you're going to unlock/lock the record. What you can do is to lock the Controls by setting their Locked property to True. This way the New Record button on the Navigation Bar is still useable.