A2K3: design for pupil incidents database, help needed

[Deano]

I need to knock something up quickly to allow a teacher to record incidents
at a school. This means logging the type of incident e.g bullying, fight
etc and the pupils that were involved.

So I have tblPupils, tblPupilEventLink (the junction table) and
tblIncidents. tblPupils relates to the junction table on PupilID and
tblIncidents relates
using IncidentID, as this is clearly a many to many relationship - many
events can involve a pupil and an event could relate to many pupils.

Now I see how I can create a form based on tblEvents and have a subform
based on tblPupils. The form and subform link on EventID and I can choose
pupils for a new event.

However instead of a continuous subform I would like to have a multi-select
listbox instead (maybe embedded into the subform?). This would be much
easier to use. The teacher can quickly select all the pupils involved and
click ok.

Naturally this is where I'm coming a cropper. Any advice on how to do this?
I would also like to have a combo box which would allow the user to filter
the listbox by class.

thanks
Martin

[Terry Kreft]

If you're using a multi select listbox then you have to insert the names
selected from the list into the tables using code, you can't bind the
listbox.

This means that as incidents are reviewed you also need code to select the
pupils in the list and if amendments are made code to update the tables
(mainly drop and create records).
--
Terry Kreft

"Deano" <de***@mailinator.com> wrote in message
news:43**********************@ptn-nntp-reader02.plus.net...

I need to knock something up quickly to allow a teacher to record incidents
at a school. This means logging the type of incident e.g bullying, fight
etc and the pupils that were involved.

So I have tblPupils, tblPupilEventLink (the junction table) and
tblIncidents. tblPupils relates to the junction table on PupilID and
tblIncidents relates
using IncidentID, as this is clearly a many to many relationship - many
events can involve a pupil and an event could relate to many pupils.

Now I see how I can create a form based on tblEvents and have a subform
based on tblPupils. The form and subform link on EventID and I can choose
pupils for a new event.

However instead of a continuous subform I would like to have a
multi-select
listbox instead (maybe embedded into the subform?). This would be much
easier to use. The teacher can quickly select all the pupils involved and
click ok.

Naturally this is where I'm coming a cropper. Any advice on how to do
this?
I would also like to have a combo box which would allow the user to filter
the listbox by class.

thanks
Martin

[Bob Quintal]

"Deano" <de***@mailinator.com> wrote in
news:43**********************@ptn-nntp-reader02.plus.net:

I need to knock something up quickly to allow a teacher to
record incidents at a school. This means logging the type of
incident e.g bullying, fight etc and the pupils that were
involved.

So I have tblPupils, tblPupilEventLink (the junction table)
and tblIncidents. tblPupils relates to the junction table on
PupilID and tblIncidents relates
using IncidentID, as this is clearly a many to many
relationship - many events can involve a pupil and an event
could relate to many pupils.

Now I see how I can create a form based on tblEvents and have
a subform based on tblPupils. The form and subform link on
EventID and I can choose pupils for a new event.

However instead of a continuous subform I would like to have a
multi-select listbox instead (maybe embedded into the
subform?). This would be much easier to use. The teacher can
quickly select all the pupils involved and click ok.

Naturally this is where I'm coming a cropper. Any advice on
how to do this? I would also like to have a combo box which
would allow the user to filter the listbox by class.

I posted some code to do something very similar a little mmore
than a year ago. (Bonjour José).

Just a simple listbox on the mainform will do. Double-Clicking
on a student name will immediately move it to the subform, so
you dont need the multiselect feature.

Notes:
Translate persons, to tblPupils,
clubmenbers to tblPupilEventLink and
clubs to tblEvents

The rowsource for the listbox is:
SELECT persons.personID, persons.person
FROM persons
WHERE (((persons.personID) Not In (select personID from
clubmembers where clubID = forms!form1.clubid)));
Private Sub List3_AfterUpdate()
DoCmd.RunSQL "insert into clubmembers " _
& "(clubID,personID) " _
& values ('" & Me.clubID.Value _
& "','" & Me.Combo3.Value & "');"
Me.Child1.Form.Requery 'show the new record
Me.List3.Requery ' hide the missing record
End Sub

Private Sub List_Enter()
Me.List3.Requery
End Sub

Private Sub Child1_Exit(Cancel As Integer)
' reinsert rows in listbox if we deleted them from the subform
Me.List3.Requery
End Sub

The class filter should just go in the rowsource for the
listbox.

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn*********************@207.35.177.135:

"Deano" <de***@mailinator.com> wrote in
news:43**********************@ptn-nntp-reader02.plus.net:

I need to knock something up quickly to allow a teacher to
record incidents at a school. This means logging the type of
incident e.g bullying, fight etc and the pupils that were
involved.

So I have tblPupils, tblPupilEventLink (the junction table)
and tblIncidents. tblPupils relates to the junction table on
PupilID and tblIncidents relates
using IncidentID, as this is clearly a many to many
relationship - many events can involve a pupil and an event
could relate to many pupils.

Now I see how I can create a form based on tblEvents and have
a subform based on tblPupils. The form and subform link on
EventID and I can choose pupils for a new event.

However instead of a continuous subform I would like to have
a multi-select listbox instead (maybe embedded into the
subform?). This would be much easier to use. The teacher
can quickly select all the pupils involved and click ok.

Naturally this is where I'm coming a cropper. Any advice on
how to do this? I would also like to have a combo box which
would allow the user to filter the listbox by class.
I posted some code to do something very similar a little mmore
than a year ago. (Bonjour José).

Just a simple listbox on the mainform will do. Double-Clicking
on a student name will immediately move it to the subform, so
you dont need the multiselect feature.

Notes:
Translate persons, to tblPupils,
clubmenbers to tblPupilEventLink and
clubs to tblEvents

The rowsource for the listbox is:
SELECT persons.personID, persons.person
FROM persons
WHERE (((persons.personID) Not In (select personID from
clubmembers where clubID = forms!form1.clubid)));
Private Sub List3_AfterUpdate()
DoCmd.RunSQL "insert into clubmembers " _
& "(clubID,personID) " _
& values ('" & Me.clubID.Value _
& "','" & Me.Combo3.Value & "');"
Me.Child1.Form.Requery 'show the new record
Me.List3.Requery ' hide the missing record
End Sub

OOPS. & "','" & Me.Combo3.Value & "');" should be
& "','" & Me.List3.Value & "');"

Private Sub List3_Enter()
Me.List3.Requery
End Sub

Private Sub Child1_Exit(Cancel As Integer)
' reinsert rows in listbox if we deleted them from the subform
Me.List3.Requery
End Sub

The class filter should just go in the rowsource for the
listbox.

--
Bob Quintal

PA is y I've altered my email address.

[Deano]

Bob Quintal wrote:

Thanks Bob, I have been trying this out but I have a few queries below
before I can get it working. I have amended the code as suggested.

Just a simple listbox on the mainform will do. Double-Clicking
on a student name will immediately move it to the subform, so
you dont need the multiselect feature. The rowsource for the listbox is:

SELECT tblPupils.PupilID, tblPupils.Surname
FROM tblPupils
WHERE (((tblPupils.PupilID) Not In (select PupilID from
tblPupilEventLink where EventID = Forms!Form1.EventID)));

Does Form1 refer to the main form where the listbox is placed?
When the form opens to run this query I get prompted for a parameter value
for the Forms!Form1.clubid part. I have this control on the main form. I
guess at this point there is no link between the event and the list of
pupils in the listbox. Should I simply ignore the message when starting the
form?
BTW I'm using just tblEvents as the datasource for Form1.
Private Sub List3_AfterUpdate()
DoCmd.RunSQL "insert into tblPupilEventLink " _
& "(EventID,PupilID) " _
& values ('" & Me.EventID.Value _
& "','" & "','" & Me.List3.Value & "');"
Me.Child1.Form.Requery 'show the new record
Me.List3.Requery ' hide the missing record
End Sub

I'm getting a syntax error here. What is "values"?

[Bob Quintal]

"Deano" <de***@mailinator.com> wrote in
news:43***********************@ptn-nntp-reader03.plus.net:

Bob Quintal wrote:

Thanks Bob, I have been trying this out but I have a few
queries below before I can get it working. I have amended the
code as suggested.

Just a simple listbox on the mainform will do.
Double-Clicking on a student name will immediately move it to
the subform, so you dont need the multiselect feature.

The rowsource for the listbox is:

SELECT tblPupils.PupilID, tblPupils.Surname
FROM tblPupils
WHERE (((tblPupils.PupilID) Not In (select PupilID from
tblPupilEventLink where EventID = Forms!Form1.EventID)));

Does Form1 refer to the main form where the listbox is placed?

yes it does, if your form has a different name, adjust
accordingly.
When the form opens to run this query I get prompted for a
parameter value for the Forms!Form1.clubid part. I have this
control on the main form. I guess at this point there is no
link between the event and the list of pupils in the listbox.
Should I simply ignore the message when starting the form?
BTW I'm using just tblEvents as the datasource for Form1.
You should not get this prompt. Forms!Form1.clubid should refer
to eventId, probably.


Private Sub List3_AfterUpdate()
DoCmd.RunSQL "insert into tblPupilEventLink " _
& "(EventID,PupilID) " _
& values ('" & Me.EventID.Value _
& "','" & "','" & Me.List3.Value & "');"
Me.Child1.Form.Requery 'show the new record
Me.List3.Requery ' hide the missing record
End Sub

I'm getting a syntax error here. What is "values"?

In a SQL Insert statement (which adds records), the list of
values is what goes into the new record, in the same order as
the list of fields above it. You have two commas there, and
single quotes around the values. If your IDs are numeric, you
will want the list to be (1,47), so the code would be
& " values (" & me.eventID & "," & me.list3.value & ");"
If they are string, your list should be ('event1','student2')
and you need the single quote marks.
& " values ('" & me.eventID & "','" & me.list3.value & "');"

You are also missing a doublequote before the word values.
--
Bob Quintal

PA is y I've altered my email address.

[Deano]

Bob Quintal wrote:

Hooray i made it work. The parameter error was my fault (used the wrong
form name). The afterupdate for the list box has one too many commas. Took
that out and it worked fine.

Now I'm going to work out how to move records back from the subform into the
listbox and also apply the class filter.

Thanks Bob, this looks quite nice and I would not have thought to use this
sort of interface.

[Deano]

Bob Quintal wrote:

"Deano" <de***@mailinator.com> wrote in
news:43**********************@ptn-nntp-reader02.plus.net:

I need to knock something up quickly to allow a teacher to
record incidents at a school. This means logging the type of
incident e.g bullying, fight etc and the pupils that were
involved.

Just a simple listbox on the mainform will do. Double-Clicking
on a student name will immediately move it to the subform, so
you dont need the multiselect feature.

I think I need some SQL help! Read on...
The rowsource for the listbox is:

SELECT tblPupils.PupilID, tblPupils.Surname
FROM tblPupils
WHERE (((tblPupils.PupilID) Not In (select PupilID from
tblPupilIncidentLink where IncidentID = Forms!Form1.IncidentID)));

If I create a new incident then the selected pupil now has an entry in my
junction table, tblPupilIncidentLink, along with the IncidentID.
The above SQL means that when I try to select a pupil from the listbox, the
INSERT statement in the afterupdate event won't add this pupil to the
junction table because they are already there. I think the crucial code is
the "Not In" bit.

Is there a way of tweaking the SQL to allow a pupil to be linked to more
than one event in the junction table?

e.g at the moment I have this sort of situation (2 events with no repeated
pupils which isn't realistic)

PupilID------IncidentID
10 3
11 4
21 3
22 3
whereas I would like to see this, so I can show that, for example, pupils 10
and 11 have been involved in more than one incident;

PupilID------IncidentID
10 3
10 4
11 4
11 3
21 3
22 3
Or should I be working on a temporary empty junction table for each new
incident and then append the inserted records to tblPupilEventLink_Final?

Thanks for any further thoughts on this.

[Deano]

Deano wrote:

Bob Quintal wrote:

"Deano" <de***@mailinator.com> wrote in
news:43**********************@ptn-nntp-reader02.plus.net:

I need to knock something up quickly to allow a teacher to
record incidents at a school. This means logging the type of
incident e.g bullying, fight etc and the pupils that were
involved.

Just a simple listbox on the mainform will do. Double-Clicking
on a student name will immediately move it to the subform, so
you dont need the multiselect feature.

I think I need some SQL help! Read on...

<snipped>

Ignore me, talking complete rubbish again! I only had a one-to-one
relationship between tblPupils and the junction table, now fixed and seems
to be working.

[Bob Quintal]

"Deano" <de***@mailinator.com> wrote in
news:43***********************@ptn-nntp-reader03.plus.net:

Bob Quintal wrote:

Hooray i made it work. The parameter error was my fault (used
the wrong form name). The afterupdate for the list box has
one too many commas. Took that out and it worked fine.

Now I'm going to work out how to move records back from the
subform into the listbox and also apply the class filter.
That's so easy: just delete the record from the subform, then
requery the listbox.

The class filter is not that difficult either, if your
structures are good. A combobox rowsource of "SELECT DISTINCT
Classroom from
Students;" should do there, and tour listbox control source just
needs to add to the where clause " AND students.class = " &
Forms!form1.combobox", adding single quotes around the combobox
reference if it contains text.
Thanks Bob, this looks quite nice and I would not have thought
to use this sort of interface.

Glad to share the knowledge.

--
Bob Quintal

PA is y I've altered my email address.