Multiple selection list boxes

  • Thread starter Thread starter mdunn
  • Start date Start date
M

mdunn

I am creating a database with a multiple list box of staff
in attendence for meetings. I have the list box created;
however, when I chose the selections (which I have created
in the properties window as extended), I cannot get the
names to transfer into my table.

I know this is a programming issue. I am not a programmer
so can anyone help me do what is needed to get those
chosen staff people into my table.
 
The only way I know to do this involves some programming - basically you
loop through the selected items in the listbox and use those values to add
records to the table -

Here's some sample code:

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub
 
Does this mean I place this in the visual basic program
area?
-----Original Message-----
The only way I know to do this involves some programming - basically you
loop through the selected items in the listbox and use those values to add
records to the table -

Here's some sample code:

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData (varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


[email protected] said:
I am creating a database with a multiple list box of staff
in attendence for meetings. I have the list box created;
however, when I chose the selections (which I have created
in the properties window as extended), I cannot get the
names to transfer into my table.

I know this is a programming issue. I am not a programmer
so can anyone help me do what is needed to get those
chosen staff people into my table.
Click to expand...

.
Click to expand...
 
Yes - this code would be the Event procedure for a command button. Create
the button, then look on the property sheet under the event tab for the
Click event. Using the draw down button, select '[Event Procedure]' in the
property then click the build button ('...') to open the VB Editor. Then
paste in the code - omitting the procedure declaration and ending (in other
words only paste in the guts of the procedure).

You'll need to change the SQL and field names to match your situation. FWIW,
you can see this code with the form if you take a look at the SelectRecords
sample on www.daiglenet.com/msaccess.htm. There's a lot going on in that
sample so don't be overwhelmed by it but the addOne button does exactly what
you are trying to do.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Does this mean I place this in the visual basic program
area?
-----Original Message-----
The only way I know to do this involves some programming - basically you
loop through the selected items in the listbox and use those values
to add records to the table -

Here's some sample code:

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData (varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


I am creating a database with a multiple list box of staff
in attendence for meetings. I have the list box created;
however, when I chose the selections (which I have created
in the properties window as extended), I cannot get the
names to transfer into my table.

I know this is a programming issue. I am not a programmer
so can anyone help me do what is needed to get those
chosen staff people into my table.
Click to expand...

.
Click to expand...
Click to expand...
 
A better solution would be to use a bound subform
No programming required. :)>

Main Form - bound to tblMeeting - Time, place, agenda etc
Well.. Agenda should probably be another subform but..

SubForm - Employee Attendance. This will be bound to tblAttendees It
will contain a combo Box linked to tblEmployees

Set Up a Master Child relationship linking the Form and sub form
Using structure below - tblMeeting.MeetingID to tblAttendees.MeetingID

Tables
tbl Meeting
MeetingId - primary Key
Venue
BookingTime
Duration
etc

tblEmployee
EmpID - PrimaryKey
EmpName
Etc

tblAttendees
AttendeeID Primary key
MeetingID - Link to tblMeeting
EmployeeID - Link to tblEmployee
etc
Set up a NoDuplicates index covering MeetingID and EmployeeID. This
will prevent the same emp attending the same meeting twice!
 
Do I have to create another table with just the names? I
am confused.

Is that what PersonClasses is? Why can't I just refer to
the information I created in the first table?

-----Original Message-----
The only way I know to do this involves some programming - basically you
loop through the selected items in the listbox and use those values to add
records to the table -

Here's some sample code:

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData (varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


[email protected] said:
I am creating a database with a multiple list box of staff
in attendence for meetings. I have the list box created;
however, when I chose the selections (which I have created
in the properties window as extended), I cannot get the
names to transfer into my table.

I know this is a programming issue. I am not a programmer
so can anyone help me do what is needed to get those
chosen staff people into my table.
Click to expand...

.
Click to expand...
 
Sorry, that was just an example that I had handy - you need to adapt it to
your own tables. So lets say your tables are Employees, Meetings and
MeetingAttendance:

Employees
--------
EmpId
LastName
FirstName
<etc>

Meetings
-----------
MeetingId
MeetingDate
Purpose
<etc>

MeetingAttendance
-------------------
MeetingID
EmpId

The purpose of the listbox then would be to select employees and add their
Empids to the MeetingAttendance table along with the MeetingId that you have
presumably selected elsewhere. The listbox would be bound to a query based
on the Employees table and would show the name fields but would be bound to
the Empid field. Typically with listboxes and combos, the primary key field
is hidden.

As someone else pointed out, it may be easier for you to simply use a main
form for Meetings and a subform for MeetingAttendance. Take a look a the
sample on www.daiglenet.com/msaccess.htm. Both methods are show using a
similar data model that uses Persons, Classes and PersonClasses.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Do I have to create another table with just the names? I
am confused.

Is that what PersonClasses is? Why can't I just refer to
the information I created in the first table?

-----Original Message-----
The only way I know to do this involves some programming - basically you
loop through the selected items in the listbox and use those values
to add records to the table -

Here's some sample code:

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData (varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


I am creating a database with a multiple list box of staff
in attendence for meetings. I have the list box created;
however, when I chose the selections (which I have created
in the properties window as extended), I cannot get the
names to transfer into my table.

I know this is a programming issue. I am not a programmer
so can anyone help me do what is needed to get those
chosen staff people into my table.
Click to expand...

.
Click to expand...
Click to expand...
 
mdunn said:
Now I am confused.

Do I need a separate form for this? I just wanted all the
information on one form with the multi-selection list box
as part of the other information. I don't want it to be a
separate form as it needs to be inclusive.
Click to expand...

The problem is that Multi-Select ListBoxes are not intended to allow you to
make multiple entries into a single field in a table as that is improper
database design. The only way the values in a Multi-Select ListBox can be
accessed or manipulated is through VBA code. If you have multiple
"Attendees" per "Meeting", then you should have two tables. A Meeting
table which has a one-to-many relationship with an Attendees table. Then
you can use a MainForm/SubForm arrangement to make the entries.

I believe what Sandra provided was a way to use the Multi-Select ListBox as
a way to enter the Attendee records into the separate table with code as an
alternative to using a SubForm. A separate ListBox would then display the
related Attendee records after the insertion took place.
 
Back
Top