Convert update query to VBA 1

[bjt52]

I currently have a query that is used to update two fields on my main form, I need to add several more fields to the form and I do not want to have to create and update query for 10 more fields. What I would like to know is how to add the (below) SQL to a after_update event of each of the fields with having to run separate queries.

UPDATE MasterData SET MasterData.resolved_date =forms!frmMain!txtresolved_date WHERE (((MasterData.CounterID)=[forms]![frmMain]![CounterID]));

Thanks

 

[PHV]

Have a look at the DoCmd.RunSQL or the CurrentDb.Execute method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bjt52]

Thanks PHV

 

[SmallTime]

Encapsulate the whole sql in quotes, get rid of the semi-colon at the end (VBA doesn’t seem to like it) and remove all line breaks (return) so the statement reads as one single line (you can break the line for easy reading with an underscore)

DoCmd.Runsql “UPDATE MasterData SET asterData.resolved_date =forms!frmMain!txtresolved_date WHERE(MasterData.CounterID)=[forms]![frmMain]![CounterID]))”

I think should do the trick

 

[TheAceMan1]

or perhaps:

[blue]   Dim SQL As String
   
   SQL = "UPDATE MasterData " & _
         "SET MasterData.resolved_date =#" & Forms!frmMain!txtresolved_date & "# " & _
         "WHERE (MasterData.CounterID=" & Forms!frmMain!CounterID & ");"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

 

[bjt52]

Thank you TheAceMan1 work great!