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...
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...
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...
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...
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...
**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...
I got it to work! using a combination of arnelgp's code above and by matching the indexes of the items selected.
Thankyou arnelgp for your help!
Option Compare Database
Option Explicit
Private Sub button_keyIDs_Click()
Dim strSQL As String
Dim db As DAO.Database...
Hi Gasman. I meant to only select 6 in each list when taking the screenshot.
If there are 6 of each selected, it does not matter which tag goes with which key, as long as there is one tag per key.
thank you again arnelgp! it's so close... but it does something different again, see image below. Is it possible to have only one number in trans_tag_no for one trans_key_no?