Preventing duplicates of two pieces of data?

tmc · Nov 17, 2009

Hello,

I wonder if anyone could please help,

I have created a table for logging employee attendance and dates, among other things, and would like to find out how to prevent the same employee ID being entered on the same date, twice.

My table structure needs duplicates of the ID number and date in their own individual columns, but i only need it to flag a duplicate when trying to enter an ID number on the same date twice.

I hope i've explained myself.

I wondered about having two primary keys for this but can't find a work around.

If you have any ideas and could help i'd appreciate it.

Many thanks,

Tmc.

boblarson · Nov 17, 2009

I would use a form for input, not let the users access tables directly, and then use the form's BEFORE UPDATE event to check for the combination and bring back an error message if the update would create a duplicate.

You can also make sure that the table level validation is there just in case someone were to access the table, by setting a multi-field index on those two fields (doesn't have to be a composite key).

tmc · Nov 17, 2009

Hello & thanks for the reply.

I do use a form but thought the best way to prevent the duplicates over the two fields was at table level, sorry - i'm a real noob at this!

If i was to use the beforeupdate function then what would i have to put there to prevent the duplicates?

I've used the beforeupdate function a few times already but just can't think a way of how to 'code' in to prevent the duplicates.

If you could help further i'd appreciate your time.

Thanks very much already! tmc.

tmc · Nov 17, 2009

I've also just looked at your multi-field index link and will also try this. which method would be most suitable / easy?

Would the multi field index work on access 2003?

boblarson · Nov 17, 2009

tmc said:
I've also just looked at your multi-field index link and will also try this. which method would be most suitable / easy?

I would, perhaps, use a combination of both. The multi-field index will take care of it if someone gets directly in the tables but the before update event code will manage things more elegantly for the user.

Would the multi field index work on access 2003?

yes it works on all versions of Access.

To do the Before Update event, something like this should do it:

Code:

If DCount("[AttendanceDateFieldNameHere]", "YourTableNameHere", "[EmployeeID]=" & Me!EmployeeID) > 0 Then
   MsgBox "You can't enter this information as that date already exists for that Employee.", vbExclamation, "Error"
   Cancel = True
   Me.AttendanceDateTextBoxNameHere.SetFocus
End If

tmc · Nov 17, 2009

bob, a sincere thanks for your time. I'll give those suggestions a go tonight.

Thanks again for the help.

Preventing duplicates of two pieces of data?

tmc

Registered User.

boblarson

Smeghead

tmc

Registered User.

tmc

Registered User.

boblarson

Smeghead

tmc

Registered User.

Similar threads

Users who are viewing this thread