"Object Invalid or No Longer Set" Error Using CurrentDb

[neelsfer]

I regularly get this error ("Object Invalid or No Longer Set") in the following code

On Error GoTo Err_Form_AfterUpdate

  Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
 
Set MyDB = currentdb
strSql = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
 
'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "Fis_CaptDataT", "[Fis_CaptID] = " & Me![Fis_CaptID]) = 0 Then     'NOT Captured/ADD
  Set rst = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
    With rst
      .AddNew
      ![Client_lookup] = Me![Client_lookup]
      ![InvoiceDate] = Me![InvoiceDate]
      ![Item_Lookup] = Me![Item_Lookup]
      ![CPrice] = Me![Price]
      ![Providers] = Me![Providers]
      ![Supplier] = Me![Supplier Lookup]
      ![Order_No] = Me![Order_No]
      ![DateReceive] = Me![DateReceive]
      ![OrderDate] = Me![OrderDate]
      ![ReceiveQty] = Me![ReceiveQty]
      ![OrderQty] = Me![OrderQty]
      ![DataCapturer] = Me![DataCapturer]
      ![Transact] = Me![Transaction]
      ![Order] = Me![Order]
      ![QtyReq] = Me![QtyReq]
      ![Stock_on_hand] = Me![Stock_on_hand]
      ![Comment] = Me![Comment]
      ![AveUsage] = Me![AveUsage]
      ![Outstanding] = Me![Outstanding]
      ![InvoiceQty] = Me![InvoiceQty]
      .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
      ![Fis_CaptID] = Me![Fis_CaptID]
      .update
     End With
  
   Else        'Data Captured, so Edit the Recordset
  Set rst = MyDB.OpenRecordset(strSql, dbOpenDynaset)
    With rst
    .Edit
   ![Client_lookup] = Me![Client_lookup]
   ![InvoiceDate] = Me![InvoiceDate]
   ![Item_Lookup] = Me![Item_Lookup]
   ![CPrice] = Me![Price]
   ![Providers] = Me![Providers]
   ![Supplier] = Me![Supplier Lookup]
   ![Order_No] = Me![Order_No]
   ![DateReceive] = Me![DateReceive]
   ![OrderDate] = Me![OrderDate]
   ![ReceiveQty] = Me![ReceiveQty]
   ![OrderQty] = Me![OrderQty]
   ![DataCapturer] = Me![DataCapturer]
   ![Transact] = Me![Transaction]
   ![Order] = Me![Order]
   ![QtyReq] = Me![QtyReq]
   ![Stock_on_hand] = Me![Stock_on_hand]
   ![Comment] = Me![Comment]
   ![InvoiceQty] = Me![InvoiceQty]
   .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
   ![Fis_CaptID] = Me![Fis_CaptID]
   .update
    End With
   End If
   rst.close
   Set rst = Nothing
[Forms]![OrderingF]![PHBudgetSF].Requery
Exit_Form_AfterUpdate:
  Exit Sub

Err_Form_AfterUpdate:
  MsgBox Error$
  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?

Sub CurrentDbSuccess()
         Dim db As Database
         Dim td As TableDef
         Set db = CurrentDb()
         Set td = db.TableDefs("Fis_CaptureT")
         MsgBox td.Name
      End Sub

Please assist.

[TheSmileyCoder]

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:

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.

[neelsfer]

Thx i will try the suggestion first.

    Set MyDB=Nothing