Dlookup problem with joint PK Subform
Posted on 2004-10-11
I have created a form and subform
The main form 'Students' is PK'd on 'Student code'
The Subform 'TblEnrolmentBookings' is based on a joint PK of 'Student code' and 'Course Code', so I have a list of booking for a particular student
I am try to prevent users from booking students on the same course twice using Dlookup...
Private Sub Course_Code_LostFocus()
If Me.NewRecord And Not IsNull(DLookup("[Course Code]", "TblEnrolmentBookings", _
"[Student Code]='" & Me![Student Code] & "'")) Then
'PK already exists
Debug.Print Me![Student Code]
MsgBox "Student already booked on this course. Choose another"
DoCmd.GoToControl "[Course Code]"
I thought the lookup would return null if it didn't find the course code in the table but it is flagging it every time, even when the course code is new to the entire Bookings tbale, not just for the significant Student Code.
To add insult to injury, the GoToControl action doesn't even work. It just moves to the next field!
Help! and thanks in advance