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 - 14 Jan 2008 12:17 GMT
> 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

Signature
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
John Spencer - 14 Jan 2008 12:37 GMT
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

Signature
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
> Microsoft XP Pro - Access 2002
>
[quoted text clipped - 31 lines]
>
> Roderick
Roderick O'Regan - 14 Jan 2008 13:49 GMT
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