force a combo box to reselect from a command button

[Skip Bisconer]

I have a form to update training records (see attached). This normally will be used to record employee training for a specific training policy. The entry will be repeated for up to 30 emplyees all training in the same policy.

I would like the user to pick an employee and for the first time select a policy number using the unbound combo box which record source columns will update several fields on the form, some showing some hidden.

The form works well except the Policy must be reselected each time after a record update. It would nice if I could get the combo to reselect as the user is tabbing through the Add Record command button instead of the user having to click the down arrow and select the policy again for each entry.

Is this possible? If so what code could I use? I am not stuck on this particular format as I am still in development, but this seems to be the cleansest that I have experimented with so far. Any suggestions would be welcomed.

 

[pbaldy]

If the information would be the same, I'd use a multiselect listbox to select the employees so you only did it once.

 

[Skip Bisconer]

Thanks Paul, that sounds like a good suggestion. I will figure out how to do that and see how it works.

 

[pbaldy]

I have some code and a sample db here:

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

 

[Skip Bisconer]

Thanks Paul this really helps.

 

[Skip Bisconer]

Paul,

I apparently don't understand how this code you provided me for using a MultiSelect Listbox, works. With my limited understanding I modified the code as shown below but the results are not, what I am sure, you intended with the code. The following results occur when I select 5 employees to update.

If I select 5 employees and build the Training Statistics on the form the highest employeeID'a selected data ends up in all 5 rows in the table when I click the AddRecord button. Could you straighten me out here? Again, I have attached the form and the form in design mode.

Private Sub cmdAddRecord_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("tblTrainingLog", dbOpenDynaset, dbAppendOnly)
  'make sure a selection has been made
  If Me.Employee.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.Employee
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EmpID = ctl.ItemData(varItem)
    rs!CWSPolicy = Me.CWSPolicy
    rs!EmpID = Me.Employee.Column(0)
    rs!Title = Me.Title
    rs!DateTrained = Me.DateTrained
    rs!Intv = Me.Intv
    rs!RetrainDate = Me.RetrainDate
    rs!Position = Me.Employee.Column(3)
    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
DoCmd.Close
DoCmd.OpenForm "TestingMultiSelectListBox"
End Sub

 

[pbaldy]

You're populating EmpID twice, and the second one is overwriting the first one (which is the correct one). Get rid if this:

rs!EmpID = Me.Employee.Column(0)

 

[Skip Bisconer]

Thanks again Paul.