No duplicate value based on another value

[valeriej42]

This is a HUGE db filled with confidential data so I'm really don't think I can. It would be nearly impossible to remove the data.

 

[bob fitz]

I have tested the code below.

[FONT=Verdana][FONT=Verdana] If DCount("*", "TBL_EmpTrainDate", "[Employee_ID]='" & Me.Employee_ID & "' And [Training_Number] = " & Me.Training_Number & " AND [Date_Completed]=#" & Me.Date_Completed & "#") > 0 Then[/FONT]
[FONT=Verdana]   MsgBox ("A Record for this Employee, Training Class and Date Already Exists!")[/FONT]
[FONT=Verdana]   Cancel = True[/FONT]
[FONT=Verdana]   Me.Undo[/FONT]
[FONT=Verdana] End If[/FONT][/FONT]

It works, as long as the following statements are true:
1) The table is called "TBL_EmpTrainDate"
2) The field called “Employee_ID” is data type Text
3) The field called “Training_Number” is data type Number
4) The field called “Date_Completed” is data type Date
5) The form has a control called “Employee_ID”
6) The form has a control called “Training_Number”
7) The form has a control called “Date_Completed”

 

[valeriej42]

Where are you putting this code?

 

[bob fitz]

In the form's Before Update event.

 

[valeriej42]

I tried that. I then entered some data and tried to quit the form and got an error. Runtime error 3075, syntax error (missing operator) in query expression '[Employee_ID]=" And[Training_Number]= AND[Date_Completed]=##'

 

[bob fitz]

Perhaps it would help if you put a breakpoint at the start of the code. Then, try to update the record by moving to a new record the code will stop running at the breakpoint and the code window will open. If you then hold the cursor over the parameters in the code you should be shown their values. This would at least make it clear what is missing.

 

[valeriej42]

I'm sorry. No offense but you just spoke Greek (or is that Geek) haha. I'm not a programmer in any sense of the word. My expertise is in hardware. So I guess I'll need more instruction.

 

[bob fitz]

I'm sorry. No offense but you just spoke Greek (or is that Geek) haha. I'm not a programmer in any sense of the word. My expertise is in hardware. So I guess I'll need more instruction.

OK. For more on setting a breakpoint see: http://www.techonthenet.com/access/tutorials/vbadebug/debug02.php
When the code "breaks", hold the cursor over the bits that I've shown in red below.
If DCount("*", "TBL_EmpTrainDate", "[Employee_ID]='" & Me.Employee_ID & "' And [Training_Number] = " & Me.Training_Number & " AND [Date_Completed]=#" & Me.Date_Completed & "#") > 0 Then

Tell us the values of each.

 

[valeriej42]

The values are exactly what I entered. Employee ID is ssn, Training Number is 5 and date is 3/1/2013

 

[bob fitz]

Please check that you have the code in the forms Before Update event and not in the forms Before Insert event.

 

[valeriej42]

It is definitely in the Before Update.

 

[bob fitz]

Can you copy and paste the complete code here. It should look like:

Private Sub Form_BeforeInsert(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

 

[valeriej42]

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

 

[bob fitz]

What happens if you use:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If DCount("*", "TBL_EmpTrainDate", "[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

 

[valeriej42]

Runtime error 3075 syntax error (missing operator) in query expression '[Training_Number]= AND [Date_Completed]=##'

And this is happening when I try to exit the form using the quit button. And it still lets me put in duplicates.

 

[bob fitz]

Sorry, my mistake. What happens with:

  If DCount("*", "TBL_EmpTrainDate", "[Employee_ID]='" & Me.Employee_ID & "' 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

If Training_Number is an auto-number it can not be duplicated, so no need to test for this.

 

[valeriej42]

Same thing except change training_number to employee_id

Runtime error 3075 syntax error (missing operator) in query expression '[Employee_ID]= AND [Date_Completed]=##'

 

[Solo712]

Same thing except change training_number to employee_id

Runtime error 3075 syntax error (missing operator) in query expression '[Employee_ID]= AND [Date_Completed]=##'

If you are using date format other than "mm/dd/yyyy" in your geography you will need to change the criteria to

"[Employee_ID]='" & Me.Employee_ID & "' And [Training_Number] = " & Me.Training_Number & " AND [Date_Completed]=#" & Format(Me.Date_Completed, "mm/dd/yyyy") & "#"

Best,
Jiri

 

[valeriej42]

I have an input mask set for short date.

 

[Solo712]

I have an input mask set for short date.

Hi valeriej,
problem is the domain function will not register the input mask. Try to change the code in the criteria as I suggested. At this point, what have you got to lose ?

Best,
Jiri