How can I get MultiSelect list box to populate individual rows in a table?

[Andy Sauer]

Hi, I want to enter data about when people attend a group session and which session they attended.

On a Form:
I have a Multi-Select list box of people's names.
I have a text box where I type in the date.
I have a drop-down box for the group session name.

In a Table:
I have a column for name, a column for date, and a column for group session name.

On the Form, I want to be able to highlight several names from the Multi-Select list box, select a single group session name from the drown-down, type in a date, and then enter those records. If I highlighted three names (John, Dick, and Harry), then in my table I should get 3 new rows, one per person. Each row should have the person's name, the date, and the session he or she attended.

How can I do this?

Currently, I can enter the data just fine if I do it individually. However, sometimes 8-10 people take the same session, so it would be a lot easier if I could do what I described above. I have downloaded some VBA code to get me started, but I get a lot of "Type Mismatch" errors and Object not Defined errors.

Any help would be greatly appreciated. Thanks!

[ADezii]

Just subscribing, will return later...

A few, basic, assumptions that I made for this Demo:

1. Table Name is tblSession consisting of the following Fields:
   1. [Name] - {TEXT}
   2. [Date] - {DATE/TIME}
   3. [Session Name] - {TEXT}
2. Controls on the Form are named as follows:
   1. lstNames - ListBox containing Names (MultiSelect = Yes)
   2. cboSession - Combo Box containing various Sessions
   3. txtDate - Text Box to store the Date Field
   4. The above Controls are Unbound
3. The following Code will write the Name(s) of the individual(s) selected, as well as the Session and Date to tblSessions:
   

   Dim MyDB As dao.Database
   Dim varItem As Variant
   Dim lst As ListBox
    
   Set lst = Me![lstNames]
    
   Dim rst As dao.Recordset
   If lst.ItemsSelected.Count = 0 Or IsNull(Me![cboSession]) Or IsNull(Me![txtDate]) Then
     MsgBox "You need to Select a Session(s), Name, and Date in order to proceed", vbExclamation, _
            "Missing Data Input"
              Exit Sub
   End If
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("tblSession", dbOpenDynaset, dbAppendOnly)
    
   With rst
     For Each varItem In lst.ItemsSelected
       .AddNew
          ![Name] = lst.ItemData(varItem)
          ![Date] = Me![txtDate]
          ![Session Name] = Me![cboSession]
       .Update
     Next varItem
   End With
    
   rst.Close
   Set rst = Nothing
    
   DoCmd.OpenTable "tblSession", acViewNormal, acReadOnly
   DoCmd.Maximize

4. Download the Attachment to see first hand operation.
5. Any questions, please feel free to ask.

[Andy Sauer]

This is a huge help, and you have gotten me to the 3-yard line. Your code works great, thanks so much!. It enters the data in individual rows.

After I click my button, it opens up the table so I can see the data have been entered. However, what would be really great is if it would move me to the next record so that I can continue entering patient/session information.

I tried to borrow the code from the "go to next record" button, but I'm getting errors that it can't go to specified record. Can you please help me finish the code so that it accepts the new data entries, and then moves me to the next record so I can continue entering? Here is what I tried adding to your code (right after the update part, oh, and my button is called ADD_NEW_RECORD_FOR_PATIENT):

.Update
Next varItem
End With

rst.Close
Set rst = Nothing

DoCmd.GoToRecord , , acNewRec

Exit_ADD_NEW_RECORD_FOR_PATIENT_Click:
Exit Sub

Err_ADD_NEW_RECORD_FOR_PATIENT_Click:
MsgBox Err.Description
Resume Exit_ADD_NEW_RECORD_FOR_PATIENT_Click

End Sub

[ADezii]

It is rarely a good idea to enter Data directly into a Table:

1. Create a Form whose Record Source is your Table
2. After Adding Records to the Table for multiple Names via Code, Open the Form in ADD Mode, then ADD your additional Record(s) in this manner.
3. The Code below will illustrate this point:
   

   'Code intentionally omitted.......................................
   With rst
     For Each varItem In lst.ItemsSelected
       .AddNew
          ![Name] = lst.ItemData(varItem)
          ![Date] = Me![txtDate]
          ![Session Name] = Me![cboSession]
       .Update
     Next varItem
   End With
    
   rst.Close
   Set rst = Nothing
    
   DoCmd.OpenForm "<Form Name Here>", , , , acFormAdd, acWindowNormal
   'Code intentionally omitted.......................................