Solved RunSQL Variable Destination (1 Viewer)

[Malcolm17]

Hi,

I am trying to run an Update Sql code line where the destination in the table is variable, however I just cannot quite get there. The line of code that I am using is:

DoCmd.RunSQL "UPDATE tbl_Net_Diary SET & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & = [Forms]![frmBackground]![subfrmHome]![txtDiaryEdit] WHERE TheDate = #" & [Forms]![frmBackground]![txtLocalSystemDate] & "#"

The issue appears to be in the destination as when I change it to a set location, eg ManagerDiary, ReceptionDiary etc then it works, however I just cannot make the variable work, I have tried many variations over the last few days including the following:

#[Forms]![frmBackground]![subfrmHome]![txtDiaryFeed]#
#" & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & "#
' & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & '
& " [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] " &
& [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] &

Thank you, Malcolm

 

[Gasman]

Your concatenation is all over the shop?
Put it all into a string, then you can debug.print it to see if correct

Should be more like

DoCmd.RunSQL "UPDATE tbl_Net_Diary SET " & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & " = " & [Forms]![frmBackground]![subfrmHome]![txtDiaryEdit] & " WHERE TheDate = #" & [Forms]![frmBackground]![txtLocalSystemDate] & "#"

Date will also need to be in mm/dd/yyyy or yyyy-mm-dd format, so make sure that is the case

HTH

 

[Malcolm17]

Hey Gasman,

It is all over the shop, its a textbox that is fed from a selectable combo that feeds a table with dates for various diaries in table fields, also with user access.

Thanks again for your help, the following code worked for me:

DoCmd.RunSQL "UPDATE tbl_Net_Diary SET " & [Forms]![frmBackground]![subfrmHome]![txtDiaryFeed] & " = [Forms]![frmBackground]![subfrmHome]![txtDiaryEdit] WHERE TheDate = #" & [Forms]![frmBackground]![txtLocalSystemDate] & "#"

Many thanks,

Malcolm