VBA Update query

[Suncoast]

I am trying to have a button run an update query using code only. Currently it works great by setting up an update query and then code a DoCmd.openquery line on the buttons code. I was trying to learn on how to not have a prebuilt query. I have tried:

Dim strsql2 As String

strsql2 = "UPDATE tblJob SET tblJob.Active = Yes WHERE (((tblJob.EstimateID)=[Forms]![frmJobMan]![List13]));"

dbExecute strsql2

I have also tried:

dbExecute "UPDATE tblJob SET tblJob.Active = Yes WHERE (((tblJob.EstimateID)=[Forms]![frmJobMan]![List13]));"

dbexecute("UPDATE tblJob SET tblJob.Active where (((tblJob.EstimateID)=[Forms]![frmJobMan]![List13]))") = 1

I am running on Access 97. It appears that it does not like the dbExecute nor db.Execute commands.

dbExecute gives "Sub or Function not defined" with dbExecute highlighted
db.Execute gives "Variable not difined" with the db highlighted

 

[namliam]

currentdb.Execute is the syntax

And in code you cannot (pre 2007) refer to a form like that you have to replace the value in like so:
"...WHERE EstimateID=" & [Forms]![frmJobMan]![List13]

Removing "over" information where possible

 

[LPurvis]

Post 2007 too. ;-)

To use a generic solution (a standard replacement function) you can use the cousin of fDAOGenericRst (which is found here), fExecuteQuery.
e.g.
fExecuteQuery strsql2

Where fExecuteQuery is defined as:

[COLOR=black]Function fExecuteQuery(strQuery As String, Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _[/COLOR]
[COLOR=black]                                        Optional blnReturnAuto As Boolean = False, _[/COLOR]
[COLOR=black]                                        Optional pdb As DAO.Database) As Long[/COLOR]
 
[COLOR=black] Dim db As Database[/COLOR]
[COLOR=black] Dim prm As DAO.Parameter[/COLOR]
[COLOR=black] Dim qdf As QueryDef[/COLOR]
[COLOR=black] Dim rst As DAO.Recordset[/COLOR]
 
[COLOR=black] If Not pdb Is Nothing Then[/COLOR]
[COLOR=black]     Set db = pdb[/COLOR]
[COLOR=black] Else[/COLOR]
[COLOR=black]     Set db = CurrentDb[/COLOR]
[COLOR=black] End If[/COLOR]
 
[COLOR=black] Select Case Left(strQuery, 7)[/COLOR]
[COLOR=black] Case "INSERT ", "UPDATE ", "DELETE "[/COLOR]
[COLOR=black]     Set qdf = db.CreateQueryDef("", strQuery)[/COLOR]
[COLOR=black] Case Else[/COLOR]
[COLOR=black]     Set qdf = db.QueryDefs(strQuery)[/COLOR]
[COLOR=black] End Select[/COLOR]
 
[COLOR=black] For Each prm In qdf.Parameters[/COLOR]
[COLOR=black]     prm.Value = Eval(prm.Name)[/COLOR]
[COLOR=black] Next[/COLOR]
 
[COLOR=black] qdf.Execute intOptions[/COLOR]
[COLOR=black] If blnReturnAuto Then[/COLOR]
[COLOR=black]     Set rst = db.OpenRecordset("SELECT @@Identity")[/COLOR]
[COLOR=black]     fExecuteQuery = rst(0)[/COLOR]
[COLOR=black]     rst.Close[/COLOR]
[COLOR=black] End If[/COLOR]
 
[COLOR=black] Set prm = Nothing[/COLOR]
[COLOR=black] Set rst = Nothing[/COLOR]
[COLOR=black] Set qdf = Nothing[/COLOR]
[COLOR=black] Set db = Nothing[/COLOR]
[COLOR=black]End Function[/COLOR]