By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,961 Members | 2,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,961 IT Pros & Developers. It's quick & easy.

"Object Invalid or No Longer Set" Error Using CurrentDb

100+
P: 547
I regularly get this error ("Object Invalid or No Longer Set") in the following code

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_Form_AfterUpdate
  2.  
  3.   Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim strSql As String
  6.  
  7. Set MyDB = currentdb
  8. strSql = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
  9.  
  10. 'See if the Data has already been Captured, if not, Add, not Edit the Record
  11. If DCount("*", "Fis_CaptDataT", "[Fis_CaptID] = " & Me![Fis_CaptID]) = 0 Then     'NOT Captured/ADD
  12.   Set rst = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
  13.     With rst
  14.       .AddNew
  15.       ![Client_lookup] = Me![Client_lookup]
  16.       ![InvoiceDate] = Me![InvoiceDate]
  17.       ![Item_Lookup] = Me![Item_Lookup]
  18.       ![CPrice] = Me![Price]
  19.       ![Providers] = Me![Providers]
  20.       ![Supplier] = Me![Supplier Lookup]
  21.       ![Order_No] = Me![Order_No]
  22.       ![DateReceive] = Me![DateReceive]
  23.       ![OrderDate] = Me![OrderDate]
  24.       ![ReceiveQty] = Me![ReceiveQty]
  25.       ![OrderQty] = Me![OrderQty]
  26.       ![DataCapturer] = Me![DataCapturer]
  27.       ![Transact] = Me![Transaction]
  28.       ![Order] = Me![Order]
  29.       ![QtyReq] = Me![QtyReq]
  30.       ![Stock_on_hand] = Me![Stock_on_hand]
  31.       ![Comment] = Me![Comment]
  32.       ![AveUsage] = Me![AveUsage]
  33.       ![Outstanding] = Me![Outstanding]
  34.       ![InvoiceQty] = Me![InvoiceQty]
  35.       .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
  36.       ![Fis_CaptID] = Me![Fis_CaptID]
  37.       .update
  38.      End With
  39.  
  40.    Else        'Data Captured, so Edit the Recordset
  41.   Set rst = MyDB.OpenRecordset(strSql, dbOpenDynaset)
  42.     With rst
  43.     .Edit
  44.    ![Client_lookup] = Me![Client_lookup]
  45.    ![InvoiceDate] = Me![InvoiceDate]
  46.    ![Item_Lookup] = Me![Item_Lookup]
  47.    ![CPrice] = Me![Price]
  48.    ![Providers] = Me![Providers]
  49.    ![Supplier] = Me![Supplier Lookup]
  50.    ![Order_No] = Me![Order_No]
  51.    ![DateReceive] = Me![DateReceive]
  52.    ![OrderDate] = Me![OrderDate]
  53.    ![ReceiveQty] = Me![ReceiveQty]
  54.    ![OrderQty] = Me![OrderQty]
  55.    ![DataCapturer] = Me![DataCapturer]
  56.    ![Transact] = Me![Transaction]
  57.    ![Order] = Me![Order]
  58.    ![QtyReq] = Me![QtyReq]
  59.    ![Stock_on_hand] = Me![Stock_on_hand]
  60.    ![Comment] = Me![Comment]
  61.    ![InvoiceQty] = Me![InvoiceQty]
  62.    .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
  63.    ![Fis_CaptID] = Me![Fis_CaptID]
  64.    .update
  65.     End With
  66.    End If
  67.    rst.close
  68.    Set rst = Nothing
  69. [Forms]![OrderingF]![PHBudgetSF].Requery
  70. Exit_Form_AfterUpdate:
  71.   Exit Sub
  72.  
  73. Err_Form_AfterUpdate:
  74.   MsgBox Error$
  75.   Resume Exit_Form_AfterUpdate
Will it help to add a database object variable to my code that refers to the CurrentDb function?
The table in the form where data is captured = Fis_CaptureT
The table where it is copied to: Fis_CaptDataT

Will it help to create this module below and how would i add it to the above code? Do i have to use Fis_CaptureT and Fis_CaptDataT in the code?

Expand|Select|Wrap|Line Numbers
  1. Sub CurrentDbSuccess()
  2.          Dim db As Database
  3.          Dim td As TableDef
  4.          Set db = CurrentDb()
  5.          Set td = db.TableDefs("Fis_CaptureT")
  6.          MsgBox td.Name
  7.       End Sub
Please assist.
May 15 '12 #1
Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
For someone with over 300 posts, you really should have learned by now, to specify WHICH line gives the error.

The only thing I notice is that you do not cleanup your database variable, you need to add:
Expand|Select|Wrap|Line Numbers
  1. Set MyDB=Nothing
and that something seems off with your code ast line 7, since currentdb is all in lowercase letters. Normally intellisense would fix that for you, and write CurrentDB.

I wonder if you have a variable or function somewhere that you have called currentdb.
May 15 '12 #2

100+
P: 547
Thx i will try the suggestion first.

Expand|Select|Wrap|Line Numbers
  1.     Set MyDB=Nothing
May 15 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.