Run SQL in VBA

[Malcolm17]

Hello,

I am trying to update a field in a table, the field to update is variable based upon what the user selects on a form, but the field to update is created in a textbox - its a mixture of the day of the week selected and the name of the field to update, so at the moment I am trying to update EarlyDutyManagerTuesday field. (It will be able to update the fields LateDutyManager, EarlyReception, LateReception etc and they also all end in Monday, Tuesday etc.

I am getting the error of '3073: Operation must use an updateable query'

I have tried the following which would give me the variable place to put my update:

DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET Forms!frmBackground!subfrmTickets!txtTableFieldDay = 'TESTx1'"
DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET [Forms]![frmBackground]![subfrmTickets]![txtTableFieldDay] = 'TESTx1'"

If I can get this above to work then it will look more like this, so it updates from a textbox:

DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET [Forms]![frmBackground]![subfrmTickets]![txtTableFieldDay] = txtMonday"

The following works but is not variable:

DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET EarlyDutyManagerMonday = 'TEST'"

Thank you for your help in advance.

Malcolm

 

[June7]

Proper syntax is:
UPDATE tablename SET fieldname = form control name WHERE somecriteria

Need criteria or EVERY record in table will get same data.

Where is the VBA located? In what form and what event? Why do you need UPDATE action to save data? Is form and its controls BOUND?

If you really just want to set value of a textbox, that does not use SQL, simply: Me.textboxname = somevalue

 

[sonic8]

If I can get this above to work then it will look more like this, so it updates from a textbox:
Code:
DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET [Forms]![frmBackground]![subfrmTickets]![txtTableFieldDay] = txtMonday"

DoCmd.RunSQL "UPDATE tbl_Net_TicketDays SET " & [Forms]![frmBackground]![subfrmTickets]![txtTableFieldDay] & " = [txtMonday]"

(Requires txtMonday to be in the active form, otherwise you will need to fully qualify it with Forms!...)

Please note: This will update all records in the table! You probably want to add some criteria to limit the update to certain records.

 

[Gasman]

Non normalised data I assume?
If I read your request correctly, you are trying to determine the field from a form control value, and that value to be updated from another control called Testx1?

If so, you need to use concatenation.
If the SQL is in the form, I tend to use Me.

 

[Malcolm17]

Hi all,

Thank you both for your help, Sonic8 thats exactly what I needed - thank you!!

 

[plog]

You shouldn't have fields named after days of the week. Your adding bandaids on top of bandaids with the issue you posted about.

The real fix is to normalize your data properly, then just bind forms to them so they handle the data adding, updating and deleting.

 

[June7]

Even if data is not normalized, form design could still be BOUND for data entry and not need VBA running SQL.

If you want to avoid popup warnings triggered by RunSQL, need to turn off/on with SetWarnings or use CurrentDb.Execute instead.