multiple selections in a listbox

[Guest]

Hi all

I have an access 2000-table which contains a number of qualifications. These
qualifications apears in a form I´ve created as a dropdown combobox or
listbox. I would like to use the form for entering data into an
employee-database. So far so good.

My problem is, that I would like to have the possibility to choose more than
one qualification from the listbox and make all the chosen qualifications
show in the employee-database.

I know how to alter the listbox´ preferences so I can choose more than one
qualification, but I can´t make them show in the employee-table.....

How can I achieve this function ?

 

[Van T. Dinh]

You have data that should be related by the Many-to-Many relationship which
should be stored using 3 Tables but you tried to store the data in a single
Table.

Basically, you want One Employee to have Many Qualifications and One
Qualification can be possessed by Many Employee. So Employees and
Qualifications are related in a Many-to-Many relationship. In database
design, you need to use 3 Tables: tblEmployee, tblQualification and a Link
Table to join the other 2 Tables.

Check Access Help / books or database books on the Many-to-Many relationship
and get the Table Structure correct before trying to modify the GUI.

 

[John Vinson]

I know how to alter the listbox´ preferences so I can choose more than one
qualification, but I can´t make them show in the employee-table.....

How can I achieve this function ?

You can't make them show in the employee table, because a field in a
table can have one and only one value. Instead, you need TWO tables -
the Employees table and a "junction" table EmployeeQualifications,
with fields for EmployeeID and for the qualification.

It's simplest to use a Subform based on EmployeeQualifications; if an
employee has five qualifications, you'ld simply enter five records on
this subform (using a combo box, typically).

If you prefer the look and feel of a multiselect listbox, that can be
done too - post back, and I'll be willing to post a VBA function which
I use for that purpose.

John W. Vinson[MVP]

 

[Guest]

Hi John

Thanks a lot. It really helps me. Now I will try to experiment with your
suggestions and a subform. Unfortunately I have a great deal of pressure on
me, to get this database done, so I really would appreciate it, if you would
post that vba-code, you mentioned ? I guess that is how the end-users want it.

Thanks in advance

Henry

 

[John Vinson]

Hi John

Thanks a lot. It really helps me. Now I will try to experiment with your
suggestions and a subform. Unfortunately I have a great deal of pressure on
me, to get this database done, so I really would appreciate it, if you would
post that vba-code, you mentioned ? I guess that is how the end-users want it.

Sure. You'll need to adapt it of course; this was written for an
animal shelter and has to do with animals and their medical
conditions, so you'll want to change table and fieldnames. Watch for
word wrap too.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub




John W. Vinson[MVP]