Implacable said:
Hi everybody! I am trying to multi select in a list box but i get
this error message:
The field <name> cannot contain a Null value because the Required
property for this field is set to True. Enter a value in this field.
(Error 3314) What I am trying to do is to select names from a table
and store them in another table (employees who took a class on this
date). I could only do it if i select only one, if I enable multi
select either simple or extended that is when i get this message. Is
there any macro or code that would let me do
this task? Thanks!
A multiselect list box can't be bound to a field, because a single field
in a single record must hold a single value. Therefore, the Value
property of a multiselect list box is always Null. You need to have a
secondary, related, table to store each of the selected values in a
separate record. If you want to use a multiselect list box to display
and edit those multiple records, you *can* do it, but you have to use
code to load and unload the values yourself. Here's a discussion of the
technique that I have posted before:
*** Storing Multiple Selections From A List Box ***
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.
