Form Validation Issue with Partial Duplicates

[Jenaccess]

Hi,

This question pertains to Access 2010, though I'm familiar with 2007 as well. I'm trying to set up validation rules in one of my forms so that the combination of two fields is not duplicated. More specifically, I want to avoid the following scenario:

Student Program Subject EntryDate ExitDate
Student1 LifeSkills Math 7/1/2010
Student1 LifeSkills Math 8/7/2010 10/17/10
Student1 LifeSkills Math 9/1/2010


Stuff like this has actually happened in a previous version of the database, so I want to make sure I iron out some of the kinks and loopholes in Version 2.0. Ideally, I would like to test that if a Program and subject are the same, then an exit date is mandatory, and that the entry date for the new record is after the exit date.

Ideally, the second and third overlapping records would not be allowed to be added, forcing the user to keep the data "clean" i.e.:

Student Program Subject EntryDate ExitDate
Student1 LifeSkills Math 7/1/2010 10/17/2010

Does anyone have any suggestion how I'd go about this? This form is based on two tables, a tblProgram and a tblStudentProgram (a junction table between my tblStudent and tblProgram). I've tried using the DCount function and putting in the BeforeUpdate even on the form, but if pretty much flags everything as a duplicate, even when the program and subject fields have different values.

If anyone can point me in the right direction, I'd really appreciate it. As always thanks for your time!

 

[spikepl]

Perhaps you should reconsider how you input data? Your problem seems related to modification of things that already exist. Since the data already exists, it should be offered for modification rather than re-input. What are EntryDate and ExitDate? A course would usually have a startdate and enddate, applying to all students enrolled in it. So this is not it?

It is difficult to say anything sensible not knowing the business rules applying.

If you insist to persist with your approach, then show the code you have done that does not quite do what you want. Also, clarify what is intended - can a student be enrolled in more than one program/subject at the same time? Your "new record" where EntryDate must be later than ExitDate, does that apply to the same student on new combination of program/subject? Or to what exactly?

 

[Jenaccess]

Hi Spike,

Thank you for responding. I’m always willing to reconsider if there’s a better solution. In terms of explaining the business rules, here’s a little background about the database:

I’m designing it for an educational program, which is comprised of about 4 smaller programs. At any given time a student may belong to 1 or more of these programs. The thing that unites these programs is that all of them support students who have not passed our state’s exit exams for high school graduation. However, each program has a different flavor. Some are geared toward academics, others do more hands on/community service projects, and others are geared toward employment. This is the reason I have an Entry/Exit date for the program, instead of Start/End date because some of these programs do not have classes. In addition to the program form, I also have a Service form which captures the classes and activities that each student does with each program.

I would not mind designing the form so that when there is an existing program record, there is only an option to edit, rather than add a new record. I’m just not exactly sure how to go about it.

Your "new record" where EntryDate must be later than ExitDate, does that apply to the same student on new combination of program/subject? Or to what exactly?

Basically, I want to make sure that a student is not enrolled in the same program and subject twice simultaneously. Our programs provide academic support for different subjects, so Student1 may be enrolled in Program1 English and Program2 English and Program1 math simultaneously. Or they may be enrolled in Program1 English with an exit date of let’s say 7/21/2011 and reenrolled in Program1 English with an entry date of 7/22/2011 or greater. Those are both acceptable. However, I want to ensure that a student is never enrolled in Program1 English and Program1 English at the same time. It gets sloppy and confusing.

If you insist to persist with your approach, then show the code you have done that does not quite do what you want.


I placed my code in the BeforeUpdate form event. I created a query called qryProgramSubject and had it pull the StudentID, ProgramID, and Subject and concatenate them in a field called ProgramSubject . Then I did a DCountFunction i.e.:

If DCount(“qryProgramSubject”, “ProgramSubject”) > 1 And IsNull (Me.ExitDate) Then
MsgBox “Potential Duplicate”
End If

That didn’t work because anytime there was more than one record on that form, I got the potential duplicate message, even when both the programs and subjects were different.

If after hearing the business rules and code explanation, you think that a form redesign is in order, I’m willing to give it a try. Thanks so much for your time and advice!

 

[spikepl]

EntryDAte and ExitDate are still unclear: are they tied to a specific student's participation only or given by program/subject? For a given program/subject, can student A have EntryDate 1 Sep 2011 and Student B 10 Sep 2011 and also leave independently of each other? Or does that program/subject have a specific entry and exit date?

 

[Jenaccess]

EntryDAte and ExitDate are still unclear: are they tied to a specific student's participation only or given by program/subject?

EntryDate and ExitDate refer to a specific student in a specific program in a specific subject.

For a given program/subject, can student A have EntryDate 1 Sep 2011 and Student B 10 Sep 2011 and also leave independently of each other?

Yes, that’s absolutely right. A student may begin the course/program then move before completing it, or begin it before he/she knows whether she has passed the exam, and withdraw if the exam has been passed.

Or does that program/subject have a specific entry and exit date?

This is a great question, and it ties in with the last one. Classes do run at specific times, and when we’re not sure of a specific student’s entry date, we fill in the date of the first class. However, our employment program does not run classes, so we need all the flexibility we can get on entry/exit dates.

Just as a related question, one of my users asked if instead of actual entry/exit dates we could have semesters. For example, instead of having to fill in 9/1/2011, my user could select Fall 2011 from a combo box. However, we would also need the flexibility to put in the actual date. I was thinking that I could somehow rig the combo box to put in 9/1/2011 when Fall 2011 was chosen and so on, but still allow actual date entries. Do you think that’s a good idea, or should I just stick with dates, instead of trying to allow both dates and semesters? This is just a very minor side issue though.

Thanks for all your help!