I have the following database to track school children (Mentees) that attend our academic sessions.

tblMentees(menteeID, fName, sName, school, year)

tblSchools(schoolID, schoolName, schoolType, address)

tblSessions(sessionID, mentorName, date, start, end, activity)

tblMenteeToSession(Mentee, Session)

tblActivity(activityID, activityName, description)

the problem I have is that when a user is going through the motions of adding children to the attended list on the session form if they add all of the children in the database which is likely early on in its use then there can be a problem.

Say they add every child to the session then they add one of them for a second time access will tell them no duplicates of a primary key. If they try to click delete it will tell them again and re set the focus to the same object. If they delete the text in the combobox access tells them that a null value is not permitted. The only way out is to close down access.

what should i be doing to prevent this trap?

If the user wants to add a different record, he should be able to just change the selection from the combo. If he wants to back out of the update entirely, he can hit the esc key twice. Once to get out of the current field, the second time to get out of the record.

Or, you can add a cancel button to the form. The code for the button's click event should undo the current changes:

Me.Undo

PS, you need some changes to your tables -
1. date and Year are a poor choice for column names since they are the names of functions. This will cause a problem if you need to write any VBA.
2. Foreign keys should retain the same name as the primary keys to which they refer. This makes it easier to identify them by sight as being foreign keys.

thanks.

Will the cancel button work as it seems that every time that I click away from the combobox the error comes up before anything else is allowed to happen.

thanks for the table tips too.

If you're having trouble getting out of the combo, the undo button won't help you. The combo's events will still fire and you'll still get the message. You must have some code in the combo's events that is causing the problem. Post it if you can't figure out what is wrong.