Run-time error 3420: Object invalid or no longer set

Can anyone tell me why I'm getting this error? It stops at .AddNew. I've tried deleting everything between AddNew and Update and it still gives this error. Does this mean that rst_TotalSLT is invalid? How is that possible since it's dim'd and set in the function?
Function Total()
Dim dbs As Database
Dim rst_PN As Recordset
Dim rst_input As Recordset
Dim rst_Output As Recordset
Dim rst_ParametersTotal As Recordset
Dim rst_TotalSLT As Recordset
 
Set dbs = DBEngine(0).Databases(0)
Set rst_PN = dbs.OpenRecordset("tblPN")
Set rst_input = dbs.OpenRecordset("tbl_PN_input")
Set rst_TotalSLT = dbs.OpenRecordset("tbl_TotalSLT")
Set rst_Output = dbs.OpenRecordset("tbl_PN_OutputDate")
 
rst_PN.MoveFirst
rst_TotalSLT.MoveFirst
 
Do Until rst_PN.EOF
 
    With rst_input
        .Edit
        rst_input![PN] = rst_PN![PN] 'this is what changes the PN in every loop
        .Update
    End With
    
    rst_Output.MoveFirst
    
    DoCmd.RunMacro "mcr_SafetyLT_CmdInput"
    
    Dim rst As Recordset
    Dim rst_cost As Recordset
    Dim objExcel As Excel.Application
    Set objExcel = CreateObject("Excel.Application")
    Dim FCSafetyStockCurrent As Double
    Dim FCSafetyStockNew As Double
    Dim LTSafetyStockCurrent As Double
    Dim LTSafetyStockNew As Double
    Dim FCLTSafetyStockCurrent As Double
    Dim FCLTSafetyStockNew As Double
    Dim HoldingCost As Double
    Dim NetAverage As Double
    Dim NetSD As Double
    Dim NetSDAdj As Double
    Dim UnitCost As Double
    
    'Get recordsets from database
    Set rst = dbs.OpenRecordset("SELECT * FROM [qry2_PN_BasicData]")
    Set rst_cost = dbs.OpenRecordset("SELECT * FROM [qry_PN_cost]")
    Set rst_ParametersTotal = dbs.OpenRecordset("tbl_ParametersTotal")
    rst_ParametersTotal.MoveFirst
    
    'Define variables
    HoldingCost = 0.2
    UnitCost = rst_cost![Price]
    
    NetAverage = rst![Average] * Sqr(rst_ParametersTotal![Mean Lead Time])
    NetSD = Sqr(rst_ParametersTotal![Mean Lead Time] * (rst![Average]) ^ 2 + (rst_ParametersTotal![SD Lead Time]) ^ 2 * (rst![StDev]) ^ 2)
    NetSDAdj = Sqr(rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj3] * (rst![Average] * rst_ParametersTotal![Adj1]) ^ 2 + (rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj4]) ^ 2 * (rst![StDev] * rst_ParametersTotal![Adj2]) ^ 2)
    FCSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], rst![Average], rst![StDev])
    FCSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], rst![Average] * rst_ParametersTotal![Adj1], rst![StDev] * Sqr(rst_ParametersTotal![Adj2]))
    LTSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], rst_ParametersTotal![Mean Lead Time], rst_ParametersTotal![SD Lead Time])
    LTSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj3], rst_ParametersTotal![SD Lead Time] * rst_ParametersTotal![Adj4])
    FCLTSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], NetAverage, NetSD)
    FCLTSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], NetAverage * rst_ParametersTotal![Adj1] * rst_ParametersTotal![Adj3], NetSDAdj)
    
    With rst_TotalSLT
        .AddNew
        rst_TotalSLT![PN] = rst_PN![PN]
        rst_TotalSLT![Mean LT] = rst_ParametersTotal![Mean Lead Time]
        rst_TotalSLT![SD LT] = rst_ParametersTotal![SD Lead Time]
        rst_TotalSLT![Current LT Inv] = LTSafetyStockCurrent
        rst_TotalSLT![New LT Inv] = LTSafetyStockNew
        rst_TotalSLT![Improvement LT] = ((LTSafetyStockCurrent - LTSafetyStockNew) / LTSafetyStockCurrent)
        rst_TotalSLT![Holding LT] = (HoldingCost * UnitCost * (LTSafetyStockCurrent - LTSafetyStockNew))
        
        rst_TotalSLT![Mean FCE] = rst![Average]
        rst_TotalSLT![SD FCE] = rst![SD]
        rst_TotalSLT![Current FCE Inv] = FCSafetyStockCurrent
        rst_TotalSLT![New FCE Inv] = FCSafetyStockNew
        rst_TotalSLT![Improvement FCE] = ((FCSafetyStockCurrent - FCSafetyStockNew) / FCSafetyStockCurrent)
        rst_TotalSLT![Holding FCE] = (HoldingCost * UnitCost * (FCSafetyStockCurrent - FCSafetyStockNew))
        
        rst_TotalSLT![Mean LTFCE] = NetAverage
        rst_TotalSLT![SD LTFCE] = NetSD
        rst_TotalSLT![Current LTFCE Inv] = FCLTSafetyStockCurrent
        rst_TotalSLT![New LTFCE Inv] = FCLTSafetyStockNew
        rst_TotalSLT![Improvement LTFCE] = ((FCLTSafetyStockCurrent - FCLTSafetyStockNew) / FCLTSafetyStockCurrent)
        rst_TotalSLT![Holding LTFCE] = (HoldingCost * UnitCost * (FCLTSafetyStockCurrent - FCLTSafetyStockNew))
        
        rst_TotalSLT![Usage] = rst![UsageAvg]
        rst_TotalSLT![Unit Cost] = UnitCost
        .Update
    End With
 
    rst_PN.MoveNext
 
    Loop
    
    'Close the database and excel object
    objExcel.Quit
    Set objExcel = Nothing
    
    rst_ParametersTotal.Close
    rst_TotalSLT.Close
    rst_PN.Close
    rst.Close
    rst_cost.Close
    rst_Output.Close
    rst_input.Close
    dbs.Close
    
End Function

Open in new window

ltdanp22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
ltdanp22,

I can't see anything obvious that would "unset" rst_TotalSLT.

1. What does this Macro do?
DoCmd.RunMacro "mcr_SafetyLT_CmdInput"

2. Try explicitly declaring your databases and recordsets
Dim dbs As DAO.Database
Dim rst_TotalSLT As DAO.Recordset

3. Close AND set your objects to Nothing:
    rst_ParametersTotal.Close
    Set rst_ParametersTotal=Nothing
    rst_TotalSLT.Close
    Set rst_TotalSLT.=Nothing
... ect
    dbs.Close
    Set dbs=Nothing



JeffCoachman
CSLEEDSCommented:
i agreed with boag2000, thats wat im suggest:

'in your loop block, open the recordset only when u need it
Set rst_TotalSLT = dbs.OpenRecordset("tbl_TotalSLT")
    With rst_TotalSLT
       'ur add new code
    End With
'and close it immediately after use before another loop encounter
rst_TotalSLT.close

good luck!
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ltdanp22Author Commented:
CSLEEDS:

I made the changes you suggested and I get the same error...

    Set rst_TotalSLT = dbs.OpenRecordset("tbl_TotalSLT")
    With rst_TotalSLT
        .AddNew
        rst_TotalSLT![PN] = rst_PNtotal1![PN]
        rst_TotalSLT![Mean LT] = rst_ParametersTotal![Mean Lead Time]
        rst_TotalSLT![SD LT] = rst_ParametersTotal![SD Lead Time]
        rst_TotalSLT![Current LT Inv] = LTSafetyStockCurrent
        rst_TotalSLT![New LT Inv] = LTSafetyStockNew
        rst_TotalSLT![Improvement LT] = ((LTSafetyStockCurrent - LTSafetyStockNew) / LTSafetyStockCurrent)
        rst_TotalSLT![Holding LT] = (HoldingCost * UnitCost * (LTSafetyStockCurrent - LTSafetyStockNew))
       
        rst_TotalSLT![Mean FCE] = rst![Average]
        rst_TotalSLT![SD FCE] = rst![SD]
        rst_TotalSLT![Current FCE Inv] = FCSafetyStockCurrent
        rst_TotalSLT![New FCE Inv] = FCSafetyStockNew
        rst_TotalSLT![Improvement FCE] = ((FCSafetyStockCurrent - FCSafetyStockNew) / FCSafetyStockCurrent)
        rst_TotalSLT![Holding FCE] = (HoldingCost * UnitCost * (FCSafetyStockCurrent - FCSafetyStockNew))
       
        rst_TotalSLT![Mean LTFCE] = NetAverage
        rst_TotalSLT![SD LTFCE] = NetSD
        rst_TotalSLT![Current LTFCE Inv] = FCLTSafetyStockCurrent
        rst_TotalSLT![New LTFCE Inv] = FCLTSafetyStockNew
        rst_TotalSLT![Improvement LTFCE] = ((FCLTSafetyStockCurrent - FCLTSafetyStockNew) / FCLTSafetyStockCurrent)
        rst_TotalSLT![Holding LTFCE] = (HoldingCost * UnitCost * (FCLTSafetyStockCurrent - FCLTSafetyStockNew))
       
        rst_TotalSLT![Usage] = rst![UsageAvg]
        rst_TotalSLT![Unit Cost] = UnitCost
        .Update
    End With
    rst_TotalSLT.Close
    Set rst_TotalSLT = Nothing
    rst_PNtotal1.MoveNext
   
    Loop

Here's the code for the function that calls this function...

Private Sub Cmd_CalcTotal_Click()
    Dim dbs As Database
    Set dbs = DBEngine(0).Databases(0)
    Dim rst_ParametersTotal As Recordset
    Set rst_ParametersTotal = dbs.OpenRecordset("tbl_ParametersTotal")
   
        If Not rst_ParametersTotal.EOF Then
            rst_ParametersTotal.MoveFirst
            Do Until rst_ParametersTotal.EOF
                rst_ParametersTotal.Delete
                rst_ParametersTotal.MoveNext
            Loop
        End If
        With rst_ParametersTotal
            .AddNew
            rst_ParametersTotal![Mean Lead Time] = txt_LeadTime1.Value
            rst_ParametersTotal![SD Lead Time] = txt_LeadTimeSD1.Value
            rst_ParametersTotal![Adj1] = txt_Adj11.Value
            rst_ParametersTotal![Adj2] = txt_Adj21.Value
            rst_ParametersTotal![Adj3] = txt_Adj31.Value
            rst_ParametersTotal![Adj4] = txt_Adj41.Value
            rst_ParametersTotal![Service Level] = txt_ServiceLevel1.Value
            .Update
        End With
    rst_ParametersTotal.Close
    Set rst_ParametersTotal = Nothing
    Call Total
   
End Sub

The macro in Total() (DoCmd.RunMacro "mcr_SafetyLT_CmdInput") does the following:

Calls this function...

Function ClearTable()
    Dim dbs As Database
    Dim rst_Forecast As Recordset
    Dim rst_201 As Recordset
    Dim rst_961 As Recordset
    Dim rst_101 As Recordset
    Set dbs = DBEngine(0).Databases(0)
    Set rst_Forecast = dbs.OpenRecordset("tbl_PN_Forecast")
    Set rst_101 = dbs.OpenRecordset("tbl_PN_Movements101")
    Set rst_201 = dbs.OpenRecordset("tbl_PN_Movements201")
    Set rst_961 = dbs.OpenRecordset("tbl_PN_Movements961")
   
    Call ClearData(rst_Forecast)
    Call ClearData(rst_101)
    Call ClearData(rst_201)
    Call ClearData(rst_961)
   
    rst_Forecast.Close
    rst_101.Close
    rst_201.Close
    rst_961.Close
    dbs.Close

End Function

And runs these queries

SELECT tblMovements_101.PN, tblMovements_101.Location_Type, tblMovements_101.Location, tblMovements_101.Type, tblMovements_101.[Mat Doc_], tblMovements_101.Item_, tblMovements_101.Date, tblMovements_101.Qty_, tblMovements_101.Unit INTO tbl_PN_Movements101
FROM tbl_PN_input INNER JOIN tblMovements_101 ON tbl_PN_input.PN = tblMovements_101.PN
ORDER BY tblMovements_101.Date;

SELECT tblMovements_201.PN, tblMovements_201.Location_Type, tblMovements_201.Location, tblMovements_201.Type, tblMovements_201.[Mat Doc_], tblMovements_201.Item_, tblMovements_201.Date, tblMovements_201.Qty_, tblMovements_201.Unit INTO tbl_PN_Movements201
FROM tbl_PN_input INNER JOIN tblMovements_201 ON tbl_PN_input.PN=tblMovements_201.PN
ORDER BY tblMovements_201.Date;

SELECT tblMovements_961.PN, tblMovements_961.Location_Type, tblMovements_961.Location, tblMovements_961.Type, tblMovements_961.[Mat Doc_], tblMovements_961.Item_, tblMovements_961.Date, tblMovements_961.Qty_, tblMovements_961.Unit INTO tbl_PN_Movements961
FROM tbl_PN_input INNER JOIN tblMovements_961 ON tbl_PN_input.PN = tblMovements_961.PN
ORDER BY tblMovements_961.Date;

SELECT tbl_PN_input.PN, tblForecast.Date, tblForecast.[Release No], tblForecast.Sched, tblForecast.[Delivery Date], tblForecast.[Scheduled qty] INTO tbl_PN_Forecast
FROM tbl_PN_input INNER JOIN tblForecast ON tbl_PN_input.PN = tblForecast.PN
ORDER BY tblForecast.Date, tblForecast.[Release No] DESC , tblForecast.[Delivery Date];

SELECT tbl_PN_input.PN, tblStockCoverage_Archive.Planner, tblStockCoverage_Archive.Coord, tblStockCoverage_Archive.Date, tblStockCoverage_Archive.Total_MRP_Stock, tblStockCoverage_Archive.SafeLT, tblPN.Desc, tblPN.[P/S], tblStockCoverage_Archive.ABC INTO tbl_PN_Stock
FROM (tbl_PN_input INNER JOIN tblStockCoverage_Archive ON tbl_PN_input.PN = tblStockCoverage_Archive.PN) INNER JOIN tblPN ON tbl_PN_input.PN = tblPN.PN;
CSLEEDSCommented:
the problem possibly cause by the cyclomatic complexity of your loop which contains of add, update, and delete of data through recordset.

try check the state of the recordset that causing problem by using .state or .status, execute the program in debug mode and step into each line of codes to check which process that actually causes the rst_TotalSLT to closed unexpectedly, and you may need to restructure the entire process flow so that the cyclomatic complexity can be reduced.

good luck!
CSLEEDSCommented:
and 1 more thing, in these lines of code:
    NetAverage = rst![Average] * Sqr(rst_ParametersTotal![Mean Lead Time])
    NetSD = Sqr(rst_ParametersTotal![Mean Lead Time] * (rst![Average]) ^ 2 + (rst_ParametersTotal![SD Lead Time]) ^ 2 * (rst![StDev]) ^ 2)
    NetSDAdj = Sqr(rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj3] * (rst![Average] * rst_ParametersTotal![Adj1]) ^ 2 + (rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj4]) ^ 2 * (rst![StDev] * rst_ParametersTotal![Adj2]) ^ 2)
    FCSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], rst![Average], rst![StDev])
    FCSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], rst![Average] * rst_ParametersTotal![Adj1], rst![StDev] * Sqr(rst_ParametersTotal![Adj2]))
    LTSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], rst_ParametersTotal![Mean Lead Time], rst_ParametersTotal![SD Lead Time])
    LTSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], rst_ParametersTotal![Mean Lead Time] * rst_ParametersTotal![Adj3], rst_ParametersTotal![SD Lead Time] * rst_ParametersTotal![Adj4])
    FCLTSafetyStockCurrent = objExcel.NormInv(rst_ParametersTotal![Service Level], NetAverage, NetSD)
    FCLTSafetyStockNew = objExcel.NormInv(rst_ParametersTotal![Service Level], NetAverage * rst_ParametersTotal![Adj1] * rst_ParametersTotal![Adj3], NetSDAdj)

try make use of cdbl() to convert the rst fields into double before u use them to go for calculation, and this block is the most suspicious block that cause your rst to closed accidently.

good luck!
ltdanp22Author Commented:
the rst fields are set to a table (tbl_ParametersTotal) that are already double set as double fields. will this automatically convert the recordset values to double or do I still have to manually convert all the recordset values to double?
CSLEEDSCommented:
try n c if can make a diff, and hv u step through the execution life span of the loop? what u got?
ltdanp22Author Commented:
We're getting somewhere...

I've converted all the recordsets in the formulas block to double as follows...

Function Total()
Dim dbs As DAO.Database
Dim rst_PNtotal1 As DAO.Recordset
Dim rst_input As Recordset
Dim rst_ParametersTotal As Recordset
Dim rst_TotalSLT As DAO.Recordset

Set dbs = DBEngine(0).Databases(0)
Set rst_PNtotal1 = dbs.OpenRecordset("tblPN")
Set rst_input = dbs.OpenRecordset("tbl_PN_input")

rst_PNtotal1.MoveFirst

Do Until rst_PNtotal1.EOF

    With rst_input
        .Edit
        rst_input![PN] = rst_PNtotal1![PN] 'this is what changes the PN in every loop
        .Update
    End With
 
    DoCmd.RunMacro "mcr_SafetyLT_CmdInput"
   
    Dim rst As Recordset
    Dim rst_cost As Recordset
    Dim objExcel As Excel.Application
    Set objExcel = CreateObject("Excel.Application")
    Dim FCSafetyStockCurrent As Double
    Dim FCSafetyStockNew As Double
    Dim LTSafetyStockCurrent As Double
    Dim LTSafetyStockNew As Double
    Dim FCLTSafetyStockCurrent As Double
    Dim FCLTSafetyStockNew As Double
    Dim HoldingCost As Double
    Dim NetAverage As Double
    Dim NetSD As Double
    Dim NetSDAdj As Double
    Dim UnitCost As Double
   
    'Get recordsets from database
    Set rst = dbs.OpenRecordset("SELECT * FROM [qry2_PN_BasicData]")
    Set rst_cost = dbs.OpenRecordset("SELECT * FROM [qry_PN_cost]")
    Set rst_ParametersTotal = dbs.OpenRecordset("tbl_ParametersTotal")
    rst_ParametersTotal.MoveFirst
   
    'Define variables
    HoldingCost = 0.2
    UnitCost = rst_cost![Price]
   
    NetAverage = CDbl(CDbl(rst![Average]) * Sqr(CDbl(rst_ParametersTotal![Mean Lead Time])))
    NetSD = CDbl(Sqr(CDbl(rst_ParametersTotal![Mean Lead Time]) * CDbl((rst![Average])) ^ 2 + (CDbl(rst_ParametersTotal![SD Lead Time])) ^ 2 * (CDbl(rst![StDev])) ^ 2))
    NetSDAdj = CDbl(Sqr(CDbl(rst_ParametersTotal![Mean Lead Time]) * CDbl(rst_ParametersTotal![Adj3]) * (CDbl(rst![Average]) * CDbl(rst_ParametersTotal![Adj1])) ^ 2 + (CDbl(rst_ParametersTotal![Mean Lead Time]) * CDbl(rst_ParametersTotal![Adj4])) ^ 2 * CDbl((rst![StDev]) * CDbl(rst_ParametersTotal![Adj2])) ^ 2))
    FCSafetyStockCurrent = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), CDbl(rst![Average]), CDbl(rst![StDev])))
    FCSafetyStockNew = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), CDbl(rst![Average]) * CDbl(rst_ParametersTotal![Adj1]), CDbl(rst![StDev]) * Sqr(CDbl(rst_ParametersTotal![Adj2]))))
    LTSafetyStockCurrent = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), CDbl(rst_ParametersTotal![Mean Lead Time]), CDbl(rst_ParametersTotal![SD Lead Time])))
    LTSafetyStockNew = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), CDbl(rst_ParametersTotal![Mean Lead Time]) * CDbl(rst_ParametersTotal![Adj3]), CDbl(rst_ParametersTotal![SD Lead Time]) * CDbl(rst_ParametersTotal![Adj4])))
    FCLTSafetyStockCurrent = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), NetAverage, NetSD))
    FCLTSafetyStockNew = CDbl(objExcel.NormInv(CDbl(rst_ParametersTotal![Service Level]), NetAverage * CDbl(rst_ParametersTotal![Adj1]) * CDbl(rst_ParametersTotal![Adj3]), NetSDAdj))
   
    Set rst_TotalSLT = dbs.OpenRecordset("tbl_TotalSLT")
    With rst_TotalSLT
        .AddNew
        'rst_TotalSLT![PN] = rst_PNtotal1![PN]
        rst_TotalSLT![Mean LT] = rst_ParametersTotal![Mean Lead Time]
        rst_TotalSLT![SD LT] = rst_ParametersTotal![SD Lead Time]
        rst_TotalSLT![Current LT Inv] = LTSafetyStockCurrent
        rst_TotalSLT![New LT Inv] = LTSafetyStockNew
        rst_TotalSLT![Improvement LT] = ((LTSafetyStockCurrent - LTSafetyStockNew) / LTSafetyStockCurrent)
        rst_TotalSLT![Holding LT] = (HoldingCost * UnitCost * (LTSafetyStockCurrent - LTSafetyStockNew))
       
        rst_TotalSLT![Mean FCE] = rst![Average]
        rst_TotalSLT![SD FCE] = rst![StDev]
        rst_TotalSLT![Current FCE Inv] = FCSafetyStockCurrent
        rst_TotalSLT![New FCE Inv] = FCSafetyStockNew
        rst_TotalSLT![Improvement FCE] = ((FCSafetyStockCurrent - FCSafetyStockNew) / FCSafetyStockCurrent)
        rst_TotalSLT![Holding FCE] = (HoldingCost * UnitCost * (FCSafetyStockCurrent - FCSafetyStockNew))
       
        rst_TotalSLT![Mean LTFCE] = NetAverage
        rst_TotalSLT![SD LTFCE] = NetSD
        rst_TotalSLT![Current LTFCE Inv] = FCLTSafetyStockCurrent
        rst_TotalSLT![New LTFCE Inv] = FCLTSafetyStockNew
        rst_TotalSLT![Improvement LTFCE] = ((FCLTSafetyStockCurrent - FCLTSafetyStockNew) / FCLTSafetyStockCurrent)
        rst_TotalSLT![Holding LTFCE] = (HoldingCost * UnitCost * (FCLTSafetyStockCurrent - FCLTSafetyStockNew))
       
        rst_TotalSLT![Usage] = rst![UsageAvg]
        rst_TotalSLT![Unit Cost] = UnitCost
        .Update
    End With
    rst_TotalSLT.Close
    Set rst_TotalSLT = Nothing
    rst_PNtotal1.MoveNext
   
    Loop
   
    'Close the database and excel object
    objExcel.Quit
    Set objExcel = Nothing
   
    rst_ParametersTotal.Close
    Set rst_ParametersTotal = Nothing

    rst_PNtotal1.Close
    Set rst_PNtotal1 = Nothing
    rst.Close
    Set rst = Nothing
    rst_cost.Close
    Set rst_cost = Nothing
    rst_input.Close
    Set rst_input = Nothing
    dbs.Close
   
End Function

The code runs past .AddNew as long as I comment out "'rst_TotalSLT![PN] = rst_PNtotal1![PN])" (right after .AddNew). If I don't comment out that line, it breaks there and throws the "Object invalid or no longer set" error as before.

If I do comment out that line, it breaks again at "rst_PNtotal1.MoveNext" (after the End With statement) and throws the error "Object invalid or no longer set".

It seems pretty clear that there is a problem with rst_PNtotal1. The PN field of tblPN (which I use to set rst_PNtotal1) is a text field. Any problem with that?

Dan
CSLEEDSCommented:
i think the problem cause by the eof of the rst_PNtotal1, try add the following code in the addnew block:

if not rst_PNtotal1.eof
rst_TotalSLT![PN] = rst_PNtotal1![PN]
end if

this make sure there is a record being retrieved from db and kept in the  rst_PNtotal1, so that if  rst_PNtotal1 reach eof then it wont throw an exception.

and u have to make sure that numbers of record in the  rst_PNtotal1 is equal to numbers of record to be added using the rst_TotalSLT (thats the problem exception raised said object no longer set cox rst_PNtotal1 may already reach eof), else when rst_PNtotal1 reach eof, new record added with rst_TotalSLT wont have the field [PN]

another is make sure both field in both db is set to the same type.

good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ltdanp22Author Commented:
Got it. Turns out the macro was calling some functions that close the database. Closing the db in the middle of my fcn caused the recordset to close. Found it by adding a watch on some of the recordsets.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.