Before I spend a bunch of time trying different things, I would appreciate some input on the best approach to the following --
I have a list of financial transactions which is updated daily and uploaded to an Access table tblTrans. Each record has a field of a string of reference info such as: 1299876 Johns Hardware 0599 xm . Each record has a null field (when the record is created on the initial upload) for Category. I have a separate 'reference' table tblRef that has two fields: Payee and Category. (There might be 300 of these Payees.
After the daily upload of Transactions into tblTrans, I want to run VBA code that will update tblTrans by searching through each unprocessed record (a processed record is one that has been populated with the Category info) -- so there will only be 20-30 transactions to process each day. I think I need to populate a variable in the VBA code with each Payee (such as "Johns Hardware" (looping through the Payees) and search the string of reference info for the Payee name. If found, the Category is then updated to the table. If not found, the next Payee name is used to search in the active transaction reference string.
I can probably bootstrap a solution through a form, combo box, and query but I thought a VBA function (behind the scenes) could do the same thing more 'elegantly'. I think I need a SQL statement in the VBA code but I'm not sure.
Hope this is descriptive enough. Thanks for your help.
You don't need VBA code to update the category. Make a simple Update Query using Access' Query Designer. Make the appropriate join between the two tables. The field to update is tblTrans.Category, and you're updating it with tblRef.Category. The criteria is where tblTrans.category is null. Now you're good to go.