Use multi select list box to populate multiple fields

[Stephenson]

Hello all,

I'm trying my best to create an employee attendance form. I would like to have a multi select list box with the employee's names that users can select. The names would then be stored in table, TcEmployee in field EmployeeID and the current date would populate the field DateWorked. Is it possible to split the selections from a list box into individual fields? If so I would greatly appreciate any advice on how to accomplish this.

Thanks for taking a look,

John

[pbaldy]

Does this help?

http://www.baldyweb.com/MultiselectAppend.htm

[ranman256]

Multi select lists require programming.
But if you use single select lists, add an append query to the dbl-click event.
then this can add each person to the attendance table.

[Stephenson]

Paul this is just what I'm looking for. Thank you for the help.

Have a great day,
John

[pbaldy]

Happy to help John!

[Stephenson]

The link pbaldy was excellent and now has me adding entries from a list box directly to my table, but I would like a bit more help. I have my list box on an unbound form with a subform (continuous format) displaying my entries. I would like my subform to requery when entries are made from the list box as well as fill in the current date in textbox txtDateWorked. I'm using the following code with no success.

Private Sub cmdAddRecords_Click()  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant


  On Error GoTo ErrorHandler


  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tcEmployee", dbOpenDynaset, dbAppendOnly)


  'make sure a selection has been made
  If Me.lstEmployee.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If


'  If Not IsNumeric(Me.txtOtherValue) Then
'    MsgBox "Must enter numeric Other Value"
'    Exit Sub
'  End If


  'add selected value(s) to table
  Set ctl = Me.lstEmployee
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EmployeeID = ctl.ItemData(varItem)
'    rs!OtherValue = Me.txtOtherValue
    rs.Update
  Next varItem


ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub


ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
Me.sfrmTandAList.[txtDateWorked] = Date
Me.sfrmTandAList.Form.Requery
End Sub

I've tried several variations to this with no luck. Could someone show me what I'm missing? The parent form is unbound and there is no link between the two forms.

Thanks for having a look,

John

[pbaldy]

If you want the date in the new data, use the line you commented out for OtherValue. I would expect the requery to work.

[Stephenson]

Thank you pbaldy. I will give this a shot.
I'm not sure why the requery isn't working. I'll keep playing with it.

Have a great night and thanks again for the help,

John

[pbaldy]

No problem, post back if you get stuck.

[Stephenson]

Will do pbaldy.

Looks like I just had my requery event in the wrong place. The following code works like a charm.

Private Sub cmdAddRecords_Click()  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant


  On Error GoTo ErrorHandler


  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tcEmployee", dbOpenDynaset, dbAppendOnly)


  'make sure a selection has been made
  If Me.lstEmployee.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If


'  If Not IsNumeric(Me.txtOtherValue) Then
'    MsgBox "Must enter numeric Other Value"
'    Exit Sub
'  End If


  'add selected value(s) to table
  Set ctl = Me.lstEmployee
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EmployeeID = ctl.ItemData(varItem)
    rs!DateWorked = Date
    rs.Update
  Next varItem
Me.sfrmTandAList.Form.Requery


ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub


ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select


End Sub

Thanks again for all the help,
John

[pbaldy]

Sorry, didn't notice it was in the error handler. Shouldn't view on a phone.