Warning for duplicates with multiple conditions

L

Lorien2733

Hi,

I have a form that shows info for students that have applied to magnet
schools in the district. The form has student info (name,address etc) and a
subform that displays one record for each of the schools they have applied
to, linked by a student ID. They can apply, register and be accepted to
multiple schools but they can only be admitted to one. To further complicate
matters, there may be records from more than one school year.
I thought I could use Dlookups. Something like

Private Sub Enrolled_BeforeUpdate(Cancel As Integer)
Dim Var1, Var2, Var3 As String
Var1 = DLookup([Enrolled], "tblSchoolDetail", "[tblSchoolDetail].[StudentID]
= me![StudentID]")
Var2 = DLookup([SchoolAppliedTo], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")
Var3 = DLookup([SchoolYear], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")

If (Var1 = -1) And (Not Var2 = Me![SchoolAppliedTo]) And (Not Var3 =
Me![SchoolYear]) Then
If MsgBox("This student has already been accepted by " & "var2", vbQuestion
+ vbYesNo, "Duplicate Admission") = vbNo Then
Cancel = True
End If
End If

This of course doesn't work and I'm totally confused. Any suggestions?
Thank you for not laughing at my code.
 
M

Mike Painter

You use "accepted" in your code but say that they can be accepted to more
than one school.
Are you trying to find duplicate "accepted" for the same year and school?
What do you want to do if you find a duplicate?

An easy no code method would be to base the subform on a query sorted by
year and school. This would put duplicates next to each other and unless
there area lot of schools might be all you need.
 
L

Lorien2733

Sorry. The message in the code should read "been enrolled at" not "accepted."
They can apply to, be registered at and accepted by multiple schools but can
only be enrolled at one.
I guess I didn't give enough information. The idea is to have a database
maintained in the central office that has every student in the district who
has applied to any of the districts magnet schools. They want the individual
schools to see only those students who have applied to that school. I
accomplished this by having the subform based on a query sorted by
SchoolAppliedTo as you suggested. Since each school sees only those kids that
have applied there, they have know way of knowing, nor do they particularly
want to know, if the kid has applied to other schools. The only things in the
record that the individual schools can change are the check boxes that
indicate accepted, registered etc. for that school. Students are allowed to
apply, register etc to multiple schools but they can only be ENROLLED at one.
What I am trying to do is whenever a school checks the Enrolled box, I want
to go to the full database and see if that kid has been enrolled at any other
school and give a warning message. I thought I could use DLookups :if
enrolled was = -1, SchoolAppliedTo not = , and SchoolYear not = . (See my
attempt at code in original post) That's as far as I got.

Mike Painter said:
You use "accepted" in your code but say that they can be accepted to more
than one school.
Are you trying to find duplicate "accepted" for the same year and school?
What do you want to do if you find a duplicate?

An easy no code method would be to base the subform on a query sorted by
year and school. This would put duplicates next to each other and unless
there area lot of schools might be all you need.
Hi,

I have a form that shows info for students that have applied to magnet
schools in the district. The form has student info (name,address etc)
and a subform that displays one record for each of the schools they
have applied to, linked by a student ID. They can apply, register and
be accepted to multiple schools but they can only be admitted to one.
To further complicate matters, there may be records from more than
one school year.
I thought I could use Dlookups. Something like

Private Sub Enrolled_BeforeUpdate(Cancel As Integer)
Dim Var1, Var2, Var3 As String
Var1 = DLookup([Enrolled], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")
Var2 = DLookup([SchoolAppliedTo], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")
Var3 = DLookup([SchoolYear], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")

If (Var1 = -1) And (Not Var2 = Me![SchoolAppliedTo]) And (Not Var3 =
Me![SchoolYear]) Then
If MsgBox("This student has already been accepted by " & "var2",
vbQuestion + vbYesNo, "Duplicate Admission") = vbNo Then
Cancel = True
End If
End If

This of course doesn't work and I'm totally confused. Any suggestions?
Thank you for not laughing at my code.
Click to expand...
Click to expand...
 
B

Beetle

A few things to note here.

First, the following;
Dim Var1, Var2, Var3 As String
Click to expand...

is a poor way to declare variables. The above does *not* declare
all three of those variables as String type. It only declares Var3 as
a String. The other two would default to type Variant. It should be;

Dim Var1 As String, Var2 As String, Var3 As String

Second, you don't really need those variables in this case anyway.
Basically, you just need to look at the table and find out if the student
has been enrolled in any school for the current year, so you should
only need something like the following;

Dim strSchool As String

strSchool = Nz(DLookup("SchoolAppliedTo", "tblSchoolDetail", _
& "Enrolled = True And StudentID = " & Me!StudentID _
& " And ShoolYear = " & Me!SchoolYear), "")


Third, you say a student can only be in enrolled in one school at a time,
so why do you give the users a YesNo message box as if they are
still supposed to choose whether or not to enroll them?

Finally, since yo are doing this in the Before Update event of a check
box, you need to verify the value of the check box first, otherwise your
code could prevent the users from being able to uncheck the box if they
make a mistake.

Suggested modified code example;

Private Sub Enrolled_BeforeUpdate(Cancel As Integer)

Dim strSchool As String

strSchool = Nz(DLookup("SchoolAppliedTo", "tblSchoolDetail", _
& "Enrolled = True And StudentID = " & Me!StudentID _
& " And ShoolYear = " & Me!SchoolYear), "")

If Me!Enrolled = True Then
If strSchool <> "" Then
MsgBox "This student is already been accepted by " & strSchool, _
& vbInformation + vbOKOnly, "Duplicate Admission"
Cancel = True
End If
End If

End Sub

--
_________

Sean Bailey


Lorien2733 said:
Sorry. The message in the code should read "been enrolled at" not "accepted."
They can apply to, be registered at and accepted by multiple schools but can
only be enrolled at one.
I guess I didn't give enough information. The idea is to have a database
maintained in the central office that has every student in the district who
has applied to any of the districts magnet schools. They want the individual
schools to see only those students who have applied to that school. I
accomplished this by having the subform based on a query sorted by
SchoolAppliedTo as you suggested. Since each school sees only those kids that
have applied there, they have know way of knowing, nor do they particularly
want to know, if the kid has applied to other schools. The only things in the
record that the individual schools can change are the check boxes that
indicate accepted, registered etc. for that school. Students are allowed to
apply, register etc to multiple schools but they can only be ENROLLED at one.
What I am trying to do is whenever a school checks the Enrolled box, I want
to go to the full database and see if that kid has been enrolled at any other
school and give a warning message. I thought I could use DLookups :if
enrolled was = -1, SchoolAppliedTo not = , and SchoolYear not = . (See my
attempt at code in original post) That's as far as I got.

Mike Painter said:
You use "accepted" in your code but say that they can be accepted to more
than one school.
Are you trying to find duplicate "accepted" for the same year and school?
What do you want to do if you find a duplicate?

An easy no code method would be to base the subform on a query sorted by
year and school. This would put duplicates next to each other and unless
there area lot of schools might be all you need.
Hi,

I have a form that shows info for students that have applied to magnet
schools in the district. The form has student info (name,address etc)
and a subform that displays one record for each of the schools they
have applied to, linked by a student ID. They can apply, register and
be accepted to multiple schools but they can only be admitted to one.
To further complicate matters, there may be records from more than
one school year.
I thought I could use Dlookups. Something like

Private Sub Enrolled_BeforeUpdate(Cancel As Integer)
Dim Var1, Var2, Var3 As String
Var1 = DLookup([Enrolled], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")
Var2 = DLookup([SchoolAppliedTo], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")
Var3 = DLookup([SchoolYear], "tblSchoolDetail",
"[tblSchoolDetail].[StudentID] = me![StudentID]")

If (Var1 = -1) And (Not Var2 = Me![SchoolAppliedTo]) And (Not Var3 =
Me![SchoolYear]) Then
If MsgBox("This student has already been accepted by " & "var2",
vbQuestion + vbYesNo, "Duplicate Admission") = vbNo Then
Cancel = True
End If
End If

This of course doesn't work and I'm totally confused. Any suggestions?
Thank you for not laughing at my code.
Click to expand...
Click to expand...
Click to expand...
 
Top