MultiSelect list box

[Guest]

I've got a form (frmEmpl) based on a table of data (tblEmployees). One of
the fields in that table is GL Product. In frmEmpl, you assign a product or
multiple products to each employee, so I thought a multi select list box
would work here. Trouble is, how do I get the items selected to actually
write to the GL Product field in tblEmployees?

I've got this code to identify the items selected, but I'm not sure how to
proceed. Maybe there's a better way - I'm not married to the list box if
there's something better.

Dim varPosition As Variant

For Each varPosition In Me.lstGLProd.ItemsSelected
'Debug.Print lstGLProd.ItemData(varPosition)

Next varPosition

 

[Dirk Goldgar]

I've got a form (frmEmpl) based on a table of data (tblEmployees).
One of the fields in that table is GL Product. In frmEmpl, you
assign a product or multiple products to each employee, so I thought
a multi select list box would work here. Trouble is, how do I get
the items selected to actually write to the GL Product field in
tblEmployees?

The best way to store multiple items in a single field is not to do it
at all. Instead, use multiple records in a related table to represent
these items. It's a principle of relational database design that a
single field (column) holds only one datum.

An Access subform is designed to display and edit multiple records from
a related table (these records being related to the record currently
displayed on the main form), and does it with no code at all. A list
box isn't designed to do this, but for small "pick-lists" I do like the
multiselect list box. However, you need to use code to read the related
records for each new main record and select the appropriate items in the
list box, and then whenever the list box is updated you need to use code
to update the set of records in the related table.

Here's code from a sample form that represents "family members", with a
list box named "lstHobbies" that represents, for each family member,
that person's hobbies from the list of all possible hobbies.


'----- start of code -----
Option Compare Database
Option Explicit

Private Sub ClearHobbySelections()

Dim intI As Integer

With Me.lstHobbies
For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI
End With

End Sub

Private Sub Form_Current()

Dim rs As DAO.Recordset
Dim intI As Integer

' Clear all currently selected hobbies.
ClearHobbySelections

If Not Me.NewRecord Then

Set rs = CurrentDb.OpenRecordset( _
"SELECT HobbyID FROM tblFamilyMembersHobbies " & _
"WHERE MemberID=" & Me.MemberID)

' Select the hobbies currently on record for this MemberID.
With Me.lstHobbies
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!HobbyID) Then
.Selected(intI) = True
Exit For
End If
Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With

End If

End Sub

Private Sub lstHobbies_AfterUpdate()

On Error GoTo Err_lstHobbies_AfterUpdate

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
blnInTransaction = True

' Delete all hobbies now on record.
strSQL = "DELETE FROM tblFamilyMembersHobbies " & _
"WHERE Memberid = " & Me.MemberID

db.Execute strSQL, dbFailOnError

' Add each hobby selected in the list box.
With Me.lstHobbies
For Each varItem In .ItemsSelected
strSQL = _
"INSERT INTO tblFamilyMembersHobbies " & _
"(MemberID, HobbyID) VALUES (" & _
Me.MemberID & ", " & .ItemData(varItem) & ")"
db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

Exit_lstHobbies_AfterUpdate:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_lstHobbies_AfterUpdate:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation, "Unable to Update"
If blnInTransaction Then
ws.Rollback
blnInTransaction = False
End If
Resume Exit_lstHobbies_AfterUpdate

End Sub

Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)

Dim intI As Integer

' Don't allow hobbies to be updated before a MemberID has
' been generated.
If IsNull(Me.MemberID) Then
MsgBox "Please enter other information for this family " & _
"member before choosing hobbies.", , _
"Define Member First"
Cancel = True
Me.lstHobbies.Undo
' Clear the user's selection.
ClearHobbySelections
End If

End Sub
'----- end of code -----

As you see, there's a fair amount of code involved, because we're using
the list box to do something it wasn't built to do, but it works quite
nicely.

 

[Guest]

The issue here is that you want to assign multiple products to one employee.
That is a many to one relationship which requires an additional table. You
need an EmployeeProducts table that has the employee primary key and the
product primary key.
A list box would work for this, but would take more coding that just using a
subform with the EmployeeProducts table as the record source on the employee
form.

 

[Guest]

Hi Kirk,

If I understand correctly it seems as though your database design is
inadequate for the requirement. Is there only one field in the Employee
table for a Product foreign key? If so you are 'forcing' a many-to-one
relationship between Employees and Products.

If there is a many-to-many relationship between Employees and Products then
you need what I like to call a cross reference (or XRef) table consisting of
two columns; one for the Employee primary key and one for the Product primary
key. (You can add other columns if you like - say for date added.) You can
choose to make the primary key of this XRef table to be the compound of the
Employee key and the Product key (this prevents duplicate associations) or
you can add a separate primary key.

None of this answers your question though, does it?

OK, what you need to do within your For Each loop is to write a new record
to the table(s). I suggest you separate this into a new procedure that you
call from within the loop passing the Employee and Product keys as
parameters. How do you get the Product key? Well add it to your List Box
and make it the bound column. Then ItemData returns the desired value.

You don't mention whether you use ADO or DAO for database access. Personally
I use ADO and am very rusty regarding DAO. I can give you sample ADO code for
your table addition but prefer to leave it to someone else to supply DAO code.

I can't think of any macro commands/functions (similar to DLookUp, etc) for
adding a record; there may be one but if there is I don't know it offhand so
I think you are going to have to write some simple VBA code.

Regards,

Rod

 

[Guest]

You are absolutely correct. I've modified my design and created a
one-to-many subform. Thanks for unblocking my mental block!!

 

[Ronald Dodge]

I'm assuming he's using bound forms/controls, which in that case uses DAO
coding behind the scenes.

In general, ADO coding seems to be pretty good and better than DAO coding,
but when it comes to the Jet Engine, the one thing that I don't like about
the ADO coding is the fact you can't use Dynamic Cursor Keyset against a Jet
Engine, which has been documented. Given this, you are at the disadvantage
of not being able to see when changes are made within the recordset, when
using ADO coding against a Jet Engine.