Passing a value from a variable to an update query

[mukudu99]

Hi everyone
I have a variable that captures a maximum value
How do i pass it to an update query?
I am am doing the following and i am getting the pass parameter dialog box:
UPDATE tbl_Table1 SET tbl_Table1.ID = lngGetMaxID;

any ideas

Thanks

 

[pbaldy]

You can't refer to a variable directly in a query, but you can create a function that returns the value of the variable, and use that.

 

[tranchemontaigne]

pbaldy is right

(1) Build an event that will make a function call. Fot demo purposes I have imagined a form named frmParameterWindow that contains a command button named cmdUpdate and a text box named txtParameterValue

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

dim lngMax_ID as long

lngMax_ID = [Forms]![frmParameterWindow]![txtParameterValue].value

call fnUpdate(lngMax_ID)

if fnUpdate = true then
msgbox "Update complete"
else
msgbox "An error was encountered - records were not updated"
end if

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

Next write the function that the click event calls. I named this fnUpdate for this example

public function fnUpdate(lngGetMaxID as long)
on error goto err_fnUpdate

dim strSQL as string

'build the SQL string
strSQL = "UPDATE
strSQL = strSQL & "tbl_Table1 "
strSQL = strSQL & "SET "
strSQL = strSQL & "tbl_Table1.ID = "
strSQL = strSQL & lngGetMaxID
strSQL = strSQL & ";"

'turn warnings off, rune the query, and turn warnings back on
with DoCmd
.setwarnings = false
.runsql strSQL
.setwarnings = true
end with

'notify calling event that update was successful
fnUpdate = true

exit_fnUpdate:
exit function

err_fnUpdate:
'notify calling event that update was unsuccessful
fnUpdate = false
resume exit_fnUpdate

end function

That's it. Pretty simple once you think about it
________
YAMAHA MOTIF HISTORY

 

[pbaldy]

That's not exactly what the OP is trying to do. They want this type of thing:

Public Function ReturnVariable()
  ReturnVariable = lngGetMaxID
End Function

Then the query:

UPDATE tbl_Table1 SET tbl_Table1.ID = ReturnVariable()