No duplicate value based on another value

V

valeriej42

Registered User.
Local time
Today, 08:04
Joined
Mar 29, 2013
Messages
34
I have a form that training is entered into. It has Employee name, training # and description, and date. I want users to not be able to enter the same training on the same date. Different training on the same date is ok and the same training on different dates is ok. How do I go about doing this.:banghead: Please remember that I am just a newbie on Access and I don't understand a lot about programming. Thanks.:)
 
Perhaps some code in the form's Before Update event which uses DCount() function to test for records that match the date and training number. If the DCount() function returns a number >0 then the Update can be cancelled and the user advised with a message box.
 
Thanks I guess but since as I stated I don't know very much at all about coding I have no clue what you mean or how to start. I know where the before update is but that's about it. Please be more specific/give example.
 
Take a look at the attached file which as an examlpe of how you might use the DCount() function within an If/Then to cancel the form's update event. Take a look at the DCount() function in the help files. Post back if you have any further questions.
 

Attachments

Last edited:
OK Getting closer maybe. However each employee can have the same training no/description and same date but not both at the same time. For example if John Doe has CPR training on 3/1/2013 then CPR cannot be entered again on 3/1/2013 but it can be entered for a different date. That's what I need. I've attached a screen shot of what the form looks like. Thanks for helping.

Additional info: This doesn't really update a table but creates several reports.
 

Attachments

Last edited:
Just to clarify, your need is to only have the same training for the same employee on a given date entered once, but can have the same training for the same employee on a different date?

Can you also have the same training for a different employee on the the given date?

Linq ;0)>
 
Yes that is true. Each employee can have each training once for each date. But can have mulitple trainings on one date or the same training on different dates. And yes the same training for different employees on the same date is necessary.
 
What is the Datatype of training #, and what is the actual Field name?

Linq ;0)>
 
Datatype is Autonumber and Field name on that form is CB_TrainNo.
 
Sorry, I need to take a step back, here! If CB_TrainNo is an AutoNumber, it's impossible for it to ever be duplicated; it's assigned by Access and the Field cannot be manually entered/edited.

So, it must be the Employee, Date and Description Fields that mustn't be duplicated; is that correct?

If it is, you need something like this:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 If DCount("*", "tblTraining", "Employee='" & [Employee] & "' And [TrainingDate] = #" & Me.TrainingDate & "# and [Description]='" & [Description] & "'") > 0 Then
   
  MsgBox ("A Record for this Employee, Training Class and Date Already Exists!")
   
  Cancel = True
  
  Me.Undo
 
 End If

End Sub

You'll need to replace tblTraining with the actual name of the Table that holds this data, and do the same thing for the Fields named Employee, TrainingDate and Description, above.

Because this Validation depends on these Fields be entered the exact same way, every time, it would probably be a good idea to use a Combobox to enter the class descriptions.

Records such as

John Doe CPR 3/1/2013

and

John Doe CPR training 3/1/2013

are not duplicates! Nor is

John Doe CRP 3/1/2013

Linq ;0)>
 
Last edited:
There is a combo box to look up the employee which then populates the employee number(which is also a combo box) and name. Then there is a combo box to look up the training number which populates the training description. Then only the date has to be entered. I tried the code you suggested and it doesn't seem to work. Maybe because of all the combo boxes.
 
I'm going to update my question to see if I can get an answer. I have a form to enter training by employee. If the training number, employee and date all together have already been entered I need it to not allow it. If any one of the three is different it can be entered.
 
valeriej42 said:
I'm going to update my question to see if I can get an answer. I have a form to enter training by employee. If the training number, employee and date all together have already been entered I need it to not allow it. If any one of the three is different it can be entered.
Click to expand...
It still sounds to me, as though the method I suggested in post #2 (example in post #4) would be appropriate. If you want to post a copy of your db in A2003 mdb format I will take a look at it.
 
I have tried to do this code and since I don't really understand how it works I'm obviously doing something wrong. I am now getting a syntax error in date. This is my code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "TBL_EmpTrainDate", "[Employee_ID]='" & Me.Employee_ID & "' And [Training_Number] = #" & Me.Training_Number & "# and [Date_Completed]=" & Me.Date_Completed & "") > 0 Then

MsgBox ("A Record for this Employee, Training Class and Date Already Exists!")

Cancel = True

Me.Undo

End If
End Sub
 
1)What is the data type of field Employee_ID in the table called "TBL_EmpTrainDate"
2)What is the data type of field Training_Number in the table called "TBL_EmpTrainDate"
3)What is the data type of field Date_Completed in the table called "TBL_EmpTrainDate"
 
Try:
Code: 
If DCount("*", "TBL_EmpTrainDate", "[Employee_ID]='" & Me.Employee_ID & "' And [Training_Number] = " & Me.Training_Number & " AND [Date_Completed]= #" & Me.Date_Completed & "#") > 0 Then
 
  MsgBox ("A Record for this Employee, Training Class and Date Already Exists!")
 
  Cancel = True
 
  Me.Undo
 
End If
 
Thanks for trying. It still isn't working. Not sure where I'm going from here.
 
Can you post a copy of the db in A2003 mdb format
 
You must log in or register to reply here.

Similar threads

T
please help me with db, stuck with option group
2 3 4
Replies
64
Views
2,084
TipsyWolf
T
V
How to display data in a combo box with a query
Replies
2
Views
204
Gasman
Gasman
D
Updating table based on main form and combo box on subform
2
Replies
28
Views
2,128
Design by Sue
D
S
Using Not In, or wahtever will work
Replies
10
Views
348
Minty
Minty
D
How to select most current date by month, day and year
Replies
15
Views
1,042
Design by Sue
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom