Duplicate Entries

[SarahBash]

I have the following code attached to an annual leave form, to prevent duplicate entries. However, the problem I am having is that it returns a run-time error when i try to test it by putting in a duplicate record. Anyone have any ideas as to what I may have done wrong?

Private Sub StartDate_Change()
 
 If DCount("*", "Tbl_Calendar", "EmployeeID =" & Me.EmployeeID & "And StartDate = #" & Me.StartDate & "#") > 0 Then
 '...Duplicate entry not found process onwards...
 Else
 MsgBox "...Duplicate Entry..."
 Me.Undo
 End If
End Sub

 

[Galaxiom]

Try using different names for the controls on the form and the fields in the table.

 

[SarahBash]

I am reluctant to do this as I have queries and other forms linked to it.

The runtime error I am receiving is:

Runtime Error '3075'
Syntax error in date in query expression 'EmployeeID=1And StartDate =#'

 

[Galaxiom]

Ah. You need a space between the double quote mark and the A in And.

 

[SarahBash]

Still returns the same runtime error????

 

[Galaxiom]

Try fully qualifying the field names in the condition to make sure Access really know what you mean.

"Tbl_Calendar.EmployeeID =" & Me.EmployeeID & " And Tbl_Calendar.StartDate = #" & Me.StartDate & "#")

Is your system date MM/DD/YYYY format?

 

[SarahBash]

No, its in the dd/mm/yyyy format.

 

[Galaxiom]

That is the problem. The date is being written in the local format. When used in sql the date must be expressed as #MM/DD/YYYY#

One way is to write:

Format({expression},"\#mm/dd/yyyy\#")

Another way is to simply refer directly to the control rather than concatenating the value. Access takes care of the date formatting itself.

"EmployeeID = Forms!formname!EmployeeID And StartDate = Forms!formname!StartDate"

It just means you must make full reference to the control because Me doesn't apply in this context.

 

[SarahBash]

YAY!!!! It works, thank you very much!!!

 

[SarahBash]

I may have spoken too soon, as it comes up with the duplicate entry message every time I try to enter an employee's leave dates, and I know that they are not in the table.

Private Sub StartDate_Change()
 
 If DCount("*", "Tbl_Calendar", "EmployeeID = Forms!frmLeaveApplication!EmployeeID And StartDate = Forms!frmLeaveApplication!StartDate") > 0 Then
 'continue with form
 Else
 MsgBox "...This Entry already exists..."
 Me.Undo
 End If
End Sub

 

[SOS]

This line is incorrect:
If DCount("*", "Tbl_Calendar", "EmployeeID = Forms!frmLeaveApplication!EmployeeID And StartDate = Forms!frmLeaveApplication!StartDate") > 0 Then
'continue with form

You would need to say = 0 not > 0

 

[SarahBash]

Asistance from a fellow Red Dwarf fan - awesome!!!!

When I do this it doesn't catch the duplicates.

 

[SOS]

Well, it should actually be:

If DCount("*", "Tbl_Calendar", "EmployeeID = " & Forms!frmLeaveApplication!EmployeeID & " And StartDate =#" & Forms!frmLeaveApplication!StartDate & "#") = 0 Then

 

[SarahBash]

I had the # in previously, however, this is giving me a runtime error, as explained in previous posts above.

 

[SOS]

Well, you can try leaving them out.

 

[SOS]

Oh, and by the way the event should be the BEFORE UPDATE event to catch the duplicates so you can issue a

CANCEL = TRUE

if there is a duplicate.

 

[SarahBash]

Managed to fix it by changing Private Sub StartDate_Change() to Private Sub StartDate_AfterUpdate()

Thanks to everyone for their help!!!!!

 

[Galaxiom]

Well, it should actually be:

DCount("*", "Tbl_Calendar", "EmployeeID = " & Forms!frmLeaveApplication!EmployeeID & " And StartDate =#" & Forms!frmLeaveApplication!StartDate & "#")

This a remarkably common misconception even among very experienced developers.

Concatenation is one way of inserting the value read from a control into an expression. The other way is to simply refer directly to the control which also returns its Value property for the current record. It will also return the value of a field for the current record.

The advantage of the reference technique is that Access automatically deals with local date formats and eliminates the need for quotes around strings because it understands the format of the control. Referring to a control in a comparision is closely related to the comparisons used in queries. It can be used in Access SQL statements and the #mm/dd/yyyy# format is not required for dates.

The advantage of concatenation technique is that it allows of Me and the Parent property to be included within an SQL statement. The reference technique must fully address the control all the way from the Forms or Reports collections. This can be a very long expression particularly in subforms.

Consequently the code is not as reusable particularly in a subform's VBA module in the way that concatenation allows.