Solved VBA update query, syntax for multiple where conditions? (1 Viewer)

[WithRegards]

**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:

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:

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.

 

[Micron]

You didn't say what error you're raising or what the result is? Maybe there is no error and it just doesn't provide the expected result?
First thing that comes to mind is that you appear to be treating a date as a string because you're wrapping what looks like a date field reference in single quotes.
In the absence of clarity around your issue, I'm going to end with that observation.

 

[theDBguy]

Hi. Welcome to AWF!

Are you able to share a demo version of your db?

 

[June7]

Instead of looping through all items in list, just loop the ItemsSelected. Example:

With Me.lbxRates
    For Each Varitem In .ItemsSelected
        If Not IsNull(Varitem) Then
            Debug.Print .Column(1, Varitem)
        End If
    Next
End With

Use # instead of apostrophe to delimit parameter for date/time field.

 

[WithRegards]

You didn't say what error you're raising or what the result is? Maybe there is no error and it just doesn't provide the expected result?
First thing that comes to mind is that you appear to be treating a date as a string because you're wrapping what looks like a date field reference in single quotes.
In the absence of clarity around your issue, I'm going to end with that observation.

Hi Micron, thanks for your reply. The date as a string isn't an issue... I do not need to perform arithmetic on that date. That code currently works fine.

The first code block I submitted works fine, but does not work as I want because the where criteria is not narrow enough.

The second code block I submitted throws the error in the attached screenshot. I will appreciate any suggestions you may have

 

[arnelgp]

as advised, use # to delimit the Date datatype:

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)
        trans_key_no = Me.listbox_checkin_lockkeynos.Column(1, i)        
        strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in=#" & Format(Me.textbox_date_returned.Value, "mm\/dd\/yyyy") & "# WHERE trans_lock_no=" & trans_lock_no & " AND trans_key_no=" & trans_key_no
        DoCmd.RunSQL strSQL
    End If
Next

 

[WithRegards]

Hi. Welcome to AWF!

Are you able to share a demo version of your db?

Hi theDBguy, thanks for your reply. I can't post the DB publicly but if you think you can help I could send you a private link?

 

[WithRegards]

as advised, use # to delimit the Date datatype:

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)
        trans_key_no = Me.listbox_checkin_lockkeynos.Column(1, i)       
        strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in=#" & Format(Me.textbox_date_returned.Value, "mm\/dd\/yyyy") & "# WHERE trans_lock_no=" & trans_lock_no & " AND trans_key_no=" & trans_key_no
        DoCmd.RunSQL strSQL
    End If
Next

Hi ArnelGP thanks for your reply, my issue is not with the date as string but with the syntax to set multiple where conditions in this code below. Would you know how to do that?

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

 

[arnelgp]

check and run the code I made.
also is the trans_date_in a Date/Time, then it is proper to delimit (#) it.
otherwise use ' if it is text.

 

[WithRegards]

Instead of looping through all items in list, just loop the ItemsSelected. Example:

With Me.lbxRates
    For Each Varitem In .ItemsSelected
        If Not IsNull(Varitem) Then
            Debug.Print .Column(1, Varitem)
        End If
    Next
End With

Use # instead of apostrophe to delimit parameter for date/time field.

Hi June7, thanks for your reply. I am not exactly sure how this will help to set multiple conditions in the where clause?

 

[WithRegards]

check and run the code I made.
also is the trans_date_in a Date/Time, then it is proper to delimit (#) it.
otherwise use ' if it is text.

It throws the error in the attached screenshot

 

[Micron]

WHERE trans_lock_no=" & trans_lock_no & trans_key_no="

Parts of your code seem to be missing, even when I scroll (if there is a scroll bar).
The quoted part is wrong but I'm not sure what to suggest. Perhaps
WHERE trans_lock_no=" & trans_lock_no & " AND trans_key_no = " & trans_ky_no...
Having fields and controls with the same names is not a good idea IMO.

OK, while replying there has been plenty of activity in the meantime. When arnelgp starts giving out a ton of code, I am done teaching anyone why something doesn't work.

 

[arnelgp]

change the string SQl:

strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & trans_lock_no & " AND trans_key_no='" & trans_key_no & "'"

 

[WithRegards]

change the string SQl:

strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & trans_lock_no & " AND trans_key_no='" & trans_key_no & "'"

I tried it and it throws a slightly different error, screenshot attached

 

[arnelgp]

trans_lock_no is missing there:

strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & Val("0" & trans_lock_no) & " AND trans_key_no='" & Nz(trans_key_no,"@!") & "'"

you are still using ' for your trans_date?
you better go to Table design and change the trans_date to Date/Time.
you'll have difficulty on using trans_date when it is time to have Date calculation on your query/code.

 

[WithRegards]

trans_lock_no is missing there:

strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in='" & Me.textbox_date_returned.Value & "' WHERE trans_lock_no=" & Val("0" & trans_lock_no) & " AND trans_key_no='" & Nz(trans_key_no,"@!") & "'"

you are still using ' for your trans_date?
you better go to Table design and change the trans_date to Date/Time.
you'll have difficulty on using trans_date when it is time to have Date calculation on your query/code.

Ok the code works - but still not as expected. But I think the problem is not with the code but that the trans_key_no is not unique either (I thought it was). I think I might have to add another field to the form that will help to identify the right record in TBL_transaction and then include that in the where clause instead of trans_key_no. I will try it and see if it works, and thanks so much for your help so far

 

[arnelgp]

add a Unique composite index.
you can use these fields combi for your update.

 

[WithRegards]

add a Unique composite index.
you can use these fields combi for your update.

I am not sure how to add a unique composite index... I tried adding a combo box to select the date checked out and using that instead of the trans_key_no in the where clause. But I get a Type Mismatch Error - I think because the where clause has the trans_date_out as a string not date.

Below is the code - how would I change the trans_date_out part to be a date? (in TBL_transaction it is date)

DoCmd.SetWarnings False
Dim trans_key_no As String
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)
        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=" & Val("0" & trans_lock_no) & " AND trans_date_out='" & Me.cbo_checkin_dateout.Value & "'"
        DoCmd.RunSQL strSQL
    End If
Next

 

[WithRegards]

It's working! Thank you all for your input I really really appreciate it

On your suggestions I re-created the TBL_transaction, not with a composite index just with a normal Auto-Number field, called trans_ROW_ID. Then used the trans_row_ID in the where clause and it works perfectly. I also used arnelgp's code to change the trans_date_in to date/time which could be useful in future.

Here is the working code:

Private Sub button_checkin_Click()
DoCmd.SetWarnings False
Dim trans_row_ID 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_row_ID = Me.listbox_checkin_lockkeynos.Column(2, i)
        strSQL = "UPDATE TBL_transaction SET trans_status='checked in', trans_date_in=#" & Format(Me.textbox_date_returned.Value, "mm\/dd\/yyyy") & "# WHERE trans_row_ID=" & trans_row_ID
        DoCmd.RunSQL strSQL
    End If
Next
 
End Sub

 

[WithRegards]

add a Unique composite index.
you can use these fields combi for your update.

Many thanks again arnelgp