VBA to Update Date in Table?

[Sola]

Hi, all. I have a way to do what I need, but I am trying to make the process more efficient.

What happens is the user opens a form that shows four fields:
1. Account #
2. Owner name
3. UseCode = not "HM"
4. UseCodeRequestDate = Is Null (date the user requested use code be changed to HM)

The owner info is in tbl - Owner; everything else is in tbl - Main. All accounts in tbl - Main are pulled up based on the criteria, there doesn't have to be a corresponding entry in tbl - Owner for the record to show.

The user copies this list into Excel and sends it off with a request that the accounts be changed to Use Code HM. I have a query create a table, and I base the form on that table. Then when the user closes the form, another query runs that updates tbl - Main with Date() (in the UseCodeRequestDate field) for any account that is in the first created table.

What I really want is to just have the query in the form and update the UseCodeRequestDate with Date() when the user closes the form. I have tried:
Me!UseCodeRequestDate = Date()
Me.UseCodeRequestDate = Date()
[tbl - Main]!UseCodeRequestDate = Date()
[tbl - Main].UseCodeRequestDate = Date()
in the form's OnClose event, but I keep getting errors. I also tried a For Each loop that would insert a date for each acct that the form pulled up, but that REALLY didn't work.

I've had no luck searching online. Can someone tell me what I'm doing wrong? Or is it not possible because my source for the form includes more than one table?

 

[Pat Hartman]

If the UseCode will always be updated to be "HM" and the date will always be updated to a specific date, why send the spreadsheet to the users at all. Just run two update queries, one to update the UseCode and one to update the date. I suggest two queries since the criteria will be different for each update and it becomes more complicated to atempt to do them in a single query.

And lastly, you should change the form where they do the data entry to require the two missing fields or fill them in by default.