WithRegards
New member
- Local time
- Today, 10:50
- Joined
- May 20, 2020
- Messages
- 17
**Please note my issue is not with the trans_date_in as string not date.
I am using a multiselect listbox in a form to retrieve and update the trans_status field in my TBL_transaction to "checked out" and the trans_date_returned to the date in a combo box on a form. Currently I only have one condition in the where clause, which I am finding it not enough to retrieve the correct records.
Would anyone know the VBA syntax that I could use in my below code to set multiple where conditions?
In the where clause, I want to include where the second column in the listbox selected (Column 1) equals the field trans_key_no in the table (datatype of trans_key_no in TBL_transaction is Short Text).
Below is my current code:
I have tried many things, including this:
Thank you in advance.
I am using a multiselect listbox in a form to retrieve and update the trans_status field in my TBL_transaction to "checked out" and the trans_date_returned to the date in a combo box on a form. Currently I only have one condition in the where clause, which I am finding it not enough to retrieve the correct records.
Would anyone know the VBA syntax that I could use in my below code to set multiple where conditions?
In the where clause, I want to include where the second column in the listbox selected (Column 1) equals the field trans_key_no in the table (datatype of trans_key_no in TBL_transaction is Short Text).
Below is my current code:
Code:
Private Sub button_checkin_Click()
DoCmd.SetWarnings False
Dim trans_lock_no As Long
Dim strSQL As String
For i = 0 To Me.listbox_checkin_lockkeynos.ListCount - 1
If Me.listbox_checkin_lockkeynos.Selected(i) Then
trans_lock_no = Me.listbox_checkin_lockkeynos.Column(0, i)
strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & trans_lock_no
DoCmd.RunSQL strSQL
End If
Next
I have tried many things, including this:
Code:
DoCmd.SetWarnings False
Dim trans_lock_no As Long
Dim trans_key_no As String
Dim strSQL As String
For i = 0 To Me.listbox_checkin_lockkeynos.ListCount - 1
If Me.listbox_checkin_lockkeynos.Selected(i) Then
trans_lock_no = Me.listbox_checkin_lockkeynos.Column(0, i)
trans_key_no = Me.listbox_checkin_lockkeynos.Column(1, i)
strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & trans_lock_no & trans_key_no=" & trans_key_no
DoCmd.RunSQL strSQL
End If
Next
Thank you in advance.
Last edited: