Design to prevent duplicates? (AXP 10.2627.2625)

Topic

New Reply

In an Attendance database, i present a form for entry of “Today’s Absences” in which the child’s name is a lookup.

This works well until such time that a record is changed on the same day: a kid marked ‘absent’ will show up, so his record must be changed to ‘tardy’.

This could be done easily on the form, by searching for the kid and fixing the record. BUT, what the users are doing is just making a new record, so now there are both an absent and a tardy record for the same kid for the same day.

Which is the better design choice: silently deleting the earlier record? Giving a message to the operator that there are duplicates? Something else?

Reply | Quote

Replies

The way I have handled similar situations is OnExit the name text box (or drop down), check that there is not all ready a record for that person on that day. If there is, I display it for modification. If there is not, I add a new record. Will that work?

Reply | Quote

It might work, but my find duplicates query for the day only works partially !?The lLookup is the result of a sorted query, and i have had problems with these before. i will work on the query and get back to you.

When you say you display the record, do you mean you display it in another modal form, or do you delete the current record and move to the previous entry?

Reply | Quote

Your reply makes it sound as if the duplicate record is first created. I am suggesting (as Jackson does) that first you determine whether a record for that StudentID and Date exists and only insert a new one if it does not exist. If it does exist, you can display the existing record for modification (that is, for example; the user can change Absent to Late and enter an arrival time).
This will eliminate trying to find duplicates or creating, then deleting records. Is that reasonable?

Reply | Quote

Perhaps reasonable but I am winding myself around the axle here.

The design i have is a continuous form. Each line has date, name, grade, absencetype, notes. Date defaults, and is not a tabstop. Name is a lookup. Grade is filled in.

In the proposed design, … I need a lookup for the child first in todays absences. If the kid is there, show that record. If he’s not there, i should present a blank record? This sounds more like a master-detail form design than continuous form. Am I understanding you guys (finally)?

Reply | Quote

Does each student have a uniqueID in a “students” table? If so, can you create a compound index for studentID and date in your “absentee” table, and then trap for the error when a duplicate is created? The error trap could cancel the Insert action and find the already-existing record.

Reply | Quote

yes, each child is unique. i will look at the table and see how i can fit this scheme into place.

When you say “find”, do you mean “move the active record to the found one”?

Reply | Quote

Yes – that’s what I mean — cancel the insert, and go to the record of interest.

following up on your reply to Thomas’ comment — Yes, I would use at it as a parent-child form. The parent would be the particular date, and the child form would be, well… the children sorry, couldn’t resist….

You can use your continuous form as the child subform.

Reply | Quote

Thanks for help and the pun .

Reply | Quote