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.
Code:
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