How to find record with certain ID and update its field.

[kassem]

Hi



So i have a table called estimates and a variable called IdHolder which I use to store a number. I want to create a button that when clicked finds the record with the same ID as IDholder in the table estimates, and sets a checkbox variable called Need_Approval to true.

[ranman256]

in the form that shows the table records...

'onClick()
me.filter = "[IDholder] = " & IdHolder
me.filteron = true
checkbox.value = true

[ItsMe]

Something like this should work

dim rs as dao.recordset 
set rs = currentdb.openrecordset("estimates", dbopendynaset)

rs.findfirst "[ID] = " & IdHolder

if rs.nomatch then
msgbox "Unable to update the record"
set rs = nothing
exit sub
end if

rs.edit
rs![Need_Approval] = -1
rs.update


rs.close
set rs = nothing

[kassem]

ranman, How do i do this when im not on the form with the table records

[hapm]

Or try:

CurrentDb.Execute "UPDATE estimates SET [Need_Approval] = -1 WHERE [ID] = " & IdHolder

[kassem]

Thanks Hapm worked like a charm!

[ItsMe]

When using .Execute with action queries, you may want to consider employing dbfailonerror to avoid exceptions during runtime and to help ensure data integrity.