How to add a string variable to an Update statement

[sheusz]

Hi all

Thanks in advance for your time.

I have some code that I am using to make a copy of a record in a backup table before I update the original record. There are two tables involved. Customers and CustomersBackup

The Customers table and CustomersBackup table are the same with the exception that the CustomersBackup table has an additional two fields, DateChanged and ChangedBy to save the date that the record was changed and by whom.

The CustomersBackup.DateChanged field is updated automatically by using the Now() function when the record is written but I would like to have the CustomersBackup.ChangedBy record populated with the LoginUserName variable.

This code works fine, except that I can't figure out how to add the LoginUserName to the Update code

Dim strSQL as String
strSQL = "INSERT INTO [CustomersBackup] SELECT Customers.* FROM Customers WHERE (((Customers.CustomerID)=[Forms]![NewCustomerPopup]![CustomerID]))" ';"
DoCmd.RunSQL strSQL

Does anyone know how I might be able to accomplish this?

I thought that I might have to run a separate Update on the CustomersBackup table after the record is written and add the LoginUserName variable to the CustomersBackup.ChangedBy field, but that doesn't seem right.

[pbaldy]

Untested but try:

"...SELECT Customers.*, '" & LoginUserName & "' FROM ..."

You may need to specify fields in both clauses, I don't know.

[sheusz]

Thanks Paul

I'm trying to understand how this would work if I do this

strSQL = "INSERT INTO [CustomersBackup] SELECT Customers.*, '" &  LoginUserName & "' FROM Customers WHERE  (((Customers.CustomerID)=[Forms]![New Customer Popup]![CustomerID]))"  ';"

If I read the above correctly this is adding the LoginUserName to the Customers.* field list, but I don't see how the LoginUserName variable would be saved to the ChangedBy field in the CustomersBackup table.

Also, since the CustomersBackup table also contains the DateChanged field, would I not have to include that in the INSERT INTO statement?

BTW, I did try the above and received the error "No destination field name in INSERT INTO statement"

[pbaldy]

This is another shot in the dark:

"...SELECT Customers.*, '" & LoginUserName & "' As ChangedBy FROM ..."

Failing that you probably need to specify fields in both the INSERT clause and the SELECT clause.

[June7]

If you don't specify destination fields, they must be in same order in table design as corresponding fields in the SELECT query. If an autonumber ID field is the first field, this might cause an error, although, an INSERT action can insert data into autonumber field. So does destination table have an autonumber field?

Are you using CurrentDb.Execute or DoCmd.RunSQL? With the first, the form control reference must be concatenated.

Use the destination field name as an alias in the SELECT: , '" & LoginUserName & "' AS ChangedBy FROM

And yes, if you need to include today's date, just use Date() function in the string: Date() AS DateChanged

Syntax works for me.

[sheusz]

Hi Paul and June7

Thanks for the replies. I totally forgot about the AS operator that I have in fact used in the past Hopefully next time I'll remember. And that has resolved the issue.

I am using DoCmd.RunSQL and the desitnation table does have an autonumber field (primary).

So the code below worked very nicely.

strSQL = "INSERT INTO [CustomersBackup] SELECT Customers.*, Now() AS DateChanged, '" & LoginUserName & "' AS ChangedBy FROM Customers WHERE (((Customers.CustomerID)=[Forms]![New Customer Popup]![CustomerID]))" ';"
DoCmd.RunSQL strSQL

I'll continue tinkering with the whole backup routine. Now I'll need to implement a history query so users will be able to view a change history. Funny how one thing leads to another.

Thank you both again.