Adding multiple records from 1 listbox?

[Guest]

I wrote a database for my school to track tardies and dress code violations.
The only common problem people have is that they have to add violators 1 at a
time. I use combo-boxes to have the teachers select their students.

Is there a way to use a list box to select multiple students from a list box
and have each one receive an individual record.

Example
frmTardyAdd has 2 fields cbostudentSelect: saves id# of student who had the
violation and cboTeacherSelect: save the teacher who listed the violation.
Date is saved automatically and TardyID is an autonumber.

What I would like is to have cboStudentSelect to be a list box containing
all students and for the teacher to scroll down the list and select all the
students who are tardy and then have 1 record created for each individual one.

How do I do that?

 

[Nikos Yannacopoulos]

Rip,

That takes a little VBA code - the only way to "read" a multi-select
listbox anyway. The idea is to open the target table as a recordset, and
loop through the selected items in the listbox, adding one record at a
time, all at the click of a button on the form. The code (untested)
could look something like:

Dim db as DAO.Database
Dim rst as DAO.Recordset
If Me.lstStudentSelect.ItemsSelected.Count = 0 Then
Msgbox "No students selected!", vbExclamation, "Attention!"
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("tblTardy")
For Each itm in Me.lstStudentSelect.ItemsSeelected
With rst
.AddNew
.Fields("StudentID") = Me.lstStudentSelect.ItemData(itm)
.Fields("TeacherID") = Me.cboTeacherSelect
.Fields("TardyDate") = Date
.Update
End With
Next
rst.Close
Set rst = Nothing
Set db = Nothing
For Each itm in Me.lstStudentSelect.ItemsSeelected
Me.lstStudentSelect.Selected(itm) = False
Next
Msgbox "Records Added", vbInformation, "Tardy Entry"

i hope my naming assumptions are evident, so you can substitute as
required. Note that I have added a second loop to de-select after
records are added, to avoid double adding. The two loops could probably
be merged (Selected(itm) = False right after rst.Update), but I haven't
tried it. Give it a shot.

Also, note that to run this code, it is required to have an appropriate
DAO Object Library reference. While in the VB editor window, go to menu
item Tools > References; check if a Microsoft DAO reference is present
among the ones checked at the top of the list. If not, scroll down to
find the appropriate Microsoft DAO X.X Object Library reference and
check it. The appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K
or later.

HTH,
Nikos

 

[Larry Daugherty]

Hi,

I can help you but hate to feel that I'm helping to make a loaded gun
available to people not trained in safety nor in the proper use of firearms.
My ex-wife was a school teacher and my daughter is so employed.

What you want can be done with a little thinking and effort but the end
result is that someone can quickly click a bunch of student names and indict
them as guilty of an infraction. Left unchallenged, mistakes are rarely
acknowledged, careless mistakes are often defended as righteous and the
burden of proof is shifted onto the student in ALL cases.

The below is all air code so your mileage may vary.

Note that you should really have a TardyDate textbox on your form so that
the date can
be set to an earlier date for late entry of infractions. Then the code
below should refer to the date you have entered rather than always taking
the system date.

The mechanics are roughly these: In the command button to record the
guilty:

Dim a variable for StudentID, TeacherID(or name), TardyDate

Open a recordset on tblTardy.
Step through the index of items in your listbox.
If the item is selected then
rstTardy.Add
rstTardy!StudentID=lstIndex!StudentID
rstTardy!TeacherID=lstIndex!TeacherID
rstTardy!TardyDate=Date()
rstTardy.Update
End If
Next Index

rstTardy.close
rstTardy=Nothing

Note that I answered the first question you asked. The second has no answer
because while there is a multiselect LISTBOX, there is no multiselect
combobox. You referred to a listbox and a combobox as though they were the
same thing. They're not.

HTH