Update Record in Table based on Listbox Selection

[JimLes]

Hello,

I am trying to update a field in a table based on a multi-selection listbox. I have a text box that contains a date entered by the user on my form named txtDateHolder. Based on this date, I want to update the DateHRVerified field in tblHRVerification where the employee id (EID) is equal to the selection (first column) in the list box. The first column in my listbox is EID where it is bound. Here is my current code but I keep getting an error:

Private Sub MarkAsReceived_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tblHRVerification")

If IsNull(Me.txtDateHolder) Then
MsgBox "No Date selected...", vbExclamation
Exit Sub
End If

On Error GoTo Err_MarkAsReceived_Click

For Each var In Me.HRList.ItemsSelected
db.Execute "UPDATE tblHRVerification SET [DateHRVerified] = Me.txtDateHolder.Text WHERE [tblHRVerification.EID] = Me.HRList.Column(0, var)"

Next



MsgBox "Dates HR Verified Updated successfully...", vbInformation


Me.HRList.Requery

Forms!frmSeverance.Refresh


rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Exit_MarkAsReceived_Click:
Exit Sub

Err_MarkAsReceived_Click:
MsgBox "One of the employees selected already has a date entered for HR verification!"

Exit Sub
End Sub

 

[PHV]

What about this ?
db.Execute "UPDATE tblHRVerification SET DateHRVerified=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID=" & Me!HRList.Column(0, var)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JimLes]

Thanks, I modified it just slightly and it worked like a charm!! Very much appreciated.

db.Execute ("UPDATE tblHRVerification SET DateHRVerified=#" & Format(Me!txtDateHolder, "yyyy-mm-dd") & "# WHERE EID='" & Me.HRList.Column(0, var) & "'")