variable to update table 2

[MicroZealous]

I just learned how to use DLOOKUP to set a public variable.
Now I want to write that variables value back into the table. I have looked in help files, but don't know the words I'm searching for! Just a hint or a simple example. TIA.

 

[randy700]

Do you want to add a new record to the table (APPEND query) or change an existing value (UPDATE query)?


Randy

 

[MicroZealous]

Here's my design: tblSettings holds public variables to survive program closing. When access starts, I use DLOOKUP to populate the public variables, always from tblSettings.ID = 1. This is working. VBA code may update any of variables as needed. OK. Now I want to save the variables as needed, by Update tblSettings, so this would be an update. I will also want to append the variables to tblLog as a diagnostic tool. (For context, I'm experienced with tables, queries, forms and reports, but new to VBA. Can I code SQL statements in VBA?) I'm trying.

 

[randy700]

To create and run the queries in code, something like...

Private Sub cmdButtonName_Click()
   Dim strSQL as String
   strSQL = "UPDATE myTable "
   strSQL = strSQL & "SET myField = '" & [COLOR=blue]VariableName[/color] & "' "
   strSQL = strSQL & "WHERE [i]criteria here[/i]"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True
End Sub

Remove the single quotes if the field is numeric.

Or, build a query and do something like this...

Private Sub cmdButtonName_Click()
   DoCmd.SetWarnings False
   DoCmd.OpenQuery [COLOR=bule]myQueryName[/color]
   DoCmd.SetWarnings True
End Sub

Randy

 

[JoeAtWork]

You can do something like this:

CurrentDB.Execute "UPDATE tblSettings SET Field1 = " & varField1 & ", Field2 = " & varField2 & " WHERE ID = 1"

 

[MicroZealous]

Thanks for your help. I really did try the Help file, etc. What I needed was the syntax:
"SET myField = '" & VariableName & "' "

Mike R

 

[JoeAtWork]

If the field is a string, you need the quotes. If it is a number, you don't use quotes.