Murp,
This is somewhat different, in that you are now adding a second step to
the process, namely the population of additional fields in the records
created. I would envisage this as (a) first create the records in the
table based on the selections in the listbox, and then (b) open a form
whose recordsource is the newly created records for the user to fill in
the rest of the fields. As far as (b) goes, I would use a contimuous
forms view, if possible, rather than a single form, so it is clear they
have so many records to fill in. You can find some sample code for the
command button's Click event, with the following assumptions on object
names:
Name of the listbox: MyListBox
Name of the target table: MyCodes
Name and type of the field in it that wil be populated from the listbox
selections: fldCode, Text
Name of the second form: MyCodesForm
Substitute the actual object names in the code where different:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSel As String
Dim strWhere As String
If Me.MyListBox.ItemsSelected.Count = 0 Then
msg "No items selected."
typ = vbExclamation
ttl = "Can't add records"
MsgBox msg, typ, ttl
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("MyCodes")
For Each itm In Me.NyListBox.ItemsSelected
rst.AddNew
rst.Fields("fldCode") = Me.MyListBox.ItemData(itm)
rst.Update
strSel = "'" & Me.MyListBox.ItemData(itm) & "',"
Next
rst.Close
Set rst = Nothing
Set db = Nothing
strSel = Left(strSel, Len(strSel) - 1)
strWhere = "fldCode In (" & strSel & ")"
DoCmd.OpenForm "MyCodesForm", , , strWhere
Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if Microsoft DAO 3.6 reference is present among the
ones checked at the top of the list. If not, scroll down to find
Microsoft DAO 3.6 Object Library reference and check it.
HTH,
Nikos
Thanks Nikos. The list box is unbound and draws its data from an SQL query.
The user selects the items they want using the simple multi select property.
Then they hit a command button that will open a form [MyCodesForm] and add
the selected items to the record source [MyCodes]. The user then adds other
data to other fields relating to the items added to the form.
:
Murp,
It's definitely possible with the use of VBA code (which is the only way
to "read" a multi-select listbox anyway). The idea is to open the table
to append to as a recordset, loop through the selected items in the
listbox and add one record for each. You will get much more detailed
help if you post your listbox and table details.
HTH,
Nikos
