Problem checking duplicates in subform

[Numpty]

I have a form [BookedCourse]which displays details relating to a courses. Within this form there is a subform which allows people known as [Delegates] to be booked onto the course.
The subform is based on a link table or intersection table between [BookedCourse] and [Delegates].

Table: BookedCourse/DelegateLink
Primary Key: BookedCourse/DelegateLinkID
DelegateID
BookedCourseID

The subform allows duplicate Delegates to be booked onto a course but obviously this should not be allowed. I've tried for the last couple of days to tinker with this, using this forum to help, but no luck as yes.

This is the code I have come up with so far in the before update event of the subform.

If DCount("[DelegateID]", "Delegate/BookedCourse Link", "[DelegateID] = " & _
Me![DelegateID] & " AND [BookedCourseID], = " & Me![BookedCourseID] > 0) Then
MsgBox "Error", vbOKOnly, "This Delegate is already booked on this course"
End If

When this code executes it will display the error message ven if the Delegate entered is not a duplicate. i.e I have cleared the link table of all entries - booked one delegate onto a course OK but then when a book a different delegate it displays the error message.

Hope someone can help.

 

[WayneRyan]

Numpty,

I think your right parenthesis is in the wrong place ...

If DCount("[DelegateID]", "Delegate/BookedCourse Link", "[DelegateID] = " & _ 
          Me![DelegateID] & " AND [BookedCourseID], = " & Me![BookedCourseID]) > 0 Then 
    MsgBox "Error", vbOKOnly, "This Delegate is already booked on this course" 
End If

hth,
Wayne

 

[Numpty]

Thanks for looking at that for me Wayne.

Doesn't seem to like that though.

I've made that change but got the following error.

Run-Time Error '3075'

Syntax error (comma) in query expression '[DelegateID] = 17 AND
[BookedCourseID], = 1'.

Unfortunatly that means nothing much to me.

Any idea's?

 

[WayneRyan]

Numpty,

The comma after [BookedCourseID] should not be there.

Wayne

 

[Numpty]

Hey, thats great Wayne thanks for all your help, its really appreciated.