Changing field value in underlying linked table

[Roderick O'Regan]

Microsoft XP Pro - Access 2002

I have a form based on a query (which has calculated fields in it) and
which, in turn, is looking at a linked table ("orders")

From the open form I want to change the value of a checkbox
("OrderInvoiceSignedOff") in the above table from unchecked to
checked.

I have written the following "On Click" event procedure attached to a
command button on the open form in an attempt to achieve this:

stLinkCriteria = Me![txtPO_Nbr]
strSQL = "SELECT * FROM Orders WHERE PO_Nbr=" & stLinkCriteria
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.Edit
.Fields("OrderInvoiceSignedOff") = True
.Update
.Close
End With

As soon as the third line is run it gives me the following error:
"Object variable or With block variable not set"

stLinkCriteria is defined as Long
rs is defined as DAO.Recordset, and...
db as DAO.Database

I'm sure the answer is staring me in the face but even after trying a
number of code permutations I still can't get the error to go away.

Can someone spot the glaring mistake in my code, please?

Roderick

 

[Albert D. Kallal]

Microsoft XP Pro - Access 2002

I have a form based on a query (which has calculated fields in it) and
which, in turn, is looking at a linked table ("orders")

If the form is bound to that table and on the same record, and only have to
do is go

me!OrderInvoiceSignedOff = True

 

[John Spencer]

I don't see any error in what you've posted. But you have not posted the
entire routine.

Did did you set Db? Set Db = CurrentDB()
Where did you Dim DB?

Personally I would just execute an update query.

Dim Db as DAO.Database
Set db = CurrentDb()

stLinkCriteria = Me![txtPO_Nbr]

strSQL = "UPDATE Orders " & _
" Set OrderInvoiceSignedOff = true " & _
" WHERE PO_Nbr=" & stLinkCriteria

Db.Execute strSQL, dbFailonError

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Roderick O'Regan]

Thank you Albert and John for your replies.

As my form isn't directly bound to the table I have followed John's
suggestion.

It works as you expected.

Thanks again to both of you for your replies.

Roderick