|
|
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.
|
|
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.
Lorien2733 wrote:
[Quoted Text] > 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.
|
|
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" wrote:
[Quoted Text] > 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.
>
> Lorien2733 wrote:
> > 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.
>
>
>
|
|
A few things to note here.
First, the following;
[Quoted Text] > Dim Var1, Var2, Var3 As String
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" wrote:
> 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" wrote:
>
> > 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.
> >
> > Lorien2733 wrote:
> > > 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.
> >
> >
> >
|
|
That 's what I thought.
Beatle has the answer.
|
|
Thank you both so much for your help.
"Mike Painter" wrote:
[Quoted Text] > That 's what I thought.
>
> Beatle has the answer.
>
>
>
>
|
|
|