Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / January 2008

Tip: Looking for answers? Try searching our database.

Changing field value in underlying linked table

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Roderick O'Regan - 14 Jan 2008 11:50 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")

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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.