stop duplicate entries in SubForm

[patkeaveney]

I have a main form and subform. based on a query.
The main form shows courses/schedules with the subform showing students based on this course/schedule combination.
The main form has 2 combo boxes.
1 select course name - (source, query on tblCourses)
2 select schedule - (source, query on tblschedules using CourseID as parameter)


Main form - frmCourseShedulesStudents
Sub Form - Classes Subform

tbl courses
pk courseID

tbl schedules
pk scheduleID
fk courseID

tblStudentsandCourses (link table)
pkstudentClassID
fk studentID
fk scheduleID

tbl Students
pk StudentID

At the moment the same student can be added to to subform more than once
How can i prevent this.

I have command buttons on subform to perform validation before record is added to the subform.

Thanks for any help

I have searched the forums but cant find a solution

Pat

 

[Zaeed]

How are students added to the subform?

 

[patkeaveney]

the studentID field on the subform is a dropdown - from tblStudents.
The user selects the student from the combo box then clicks on command button to add
student

hope this helps

PS
The subform record source is built using the code builder to extract data from the above tables


Thanks

 

[Zaeed]

so just to clarify.

You select a Student, hit Add, that user is displayed in some sort of list.
Your problem is that the same student can be added multiple times.

If this is the case, I would remove that user from the dropdown box when their name is added. This could be done a number of ways. First way that comes to mind would be to modify the combo box's record source to not include that particular student.
To do this you would simply have a bit of code that on the Add buttons event along the lines of,

strSQL = strSQL & " AND studentName <> " & comboBox.Value

 

[patkeaveney]

thanks Zaeed

THe problem with your suggestion, is that the user can add student then close the form, reopen form and add student again. (the combo box is based on tblstudents)

ANy other help would be welcome

Pat

 

[MarkK]

You can write a query that only includes records that don't exist in a different query, which is what you want.
You'll need a DCount() in the WHERE clause, something like...

SELECT StudentID, StudentName
FROM tblStudent
WHERE DCount('StudentID', 'SomeQuery') = 0

And 'SomeQuery' provides a definition of which students already exist somewhere else, maybe...

SELECT StudentID
FROM tblStudentCourses
WHERE ScheduleID = 'some known ScheduleID'

I'd be pretty surprised if you couldn't express that as a single query that joins those tables. And the 'some known ScheduleID' exists in the current record of the main form.
If you can`t get it maybe post the db. I bet it`s doable.

 

[patkeaveney]

Thanks lagbolt.

Sort of took your approach.

I changed the record source of the subform to a query
Then added the following code in the on click event of my add student command button

If DCount("[StudentID]", "qryClassesSubform", _
"[StudentID] = Forms![frmCourseShedulesStudents]![Classes Subform]![StudentID] ") > 0 Then
MsgBox Me.StudentID.Text & vbCrLf & vbCrLf _
& " is Already Booked on this Course", vbOKOnly + vbExclamation
DoCmd.RunCommand acCmdUndo
Forms![frmCourseShedulesStudents]![Classes Subform
Exit Sub
End If


Thanks to all for your help

Pat

 

[MarkK]

Ya, that's really the same logic. Nice job.