No current record.

[mgbeye]

I am preforming a set of calculations for each record in a table if it fits certain characteristics. For some reason I always get an error message that says "No current record." AFTER it has gone through all of the records. I can't seem to keep it from attempting the calculations that last time. Can anyone help??

Private Sub Calculate_Click()
On Error GoTo Err_Calculate_Click

Dim dbsBM As Database
Dim rstFGResin As Recordset
Dim rstPFInv As Recordset
Dim rstProd As Recordset
Dim rstFGBOM As Recordset
Dim rstSHResin As Recordset
Dim rstBotBOM As Recordset
Dim rstPFBOM As Recordset
Dim rstScrap As Recordset
Dim rstUsage As Recordset
Dim rstLine As Recordset

'-------------------------------------------------------------------------------------------

'Run 3 Calculation Queries
DoCmd.RunMacro "Open Subtotals"

'Open database
Set dbsBM = CurrentDb()

'Open 2 calculation record tables
Set rstFGResin = dbsBM.OpenRecordset("Full Goods Resin Calculations")
Set rstSHResin = dbsBM.OpenRecordset("Sipa and Husky Resin Calculations")

'Open BOM tables and line table
Set rstFGBOM = dbsBM.OpenRecordset("Bottle to FG SF and Bottle Usage")
Set rstBotBOM = dbsBM.OpenRecordset("Preform to Bottle SF and Preform Usage")
Set rstPFBOM = dbsBM.OpenRecordset("Resin to Preform SF and Resin Usage")
Set rstLine = dbsBM.OpenRecordset("Product By Line")

'Open Inventory, Usage, Scrap, and Production tables
Set rstPFInv = dbsBM.OpenRecordset("Preforms pulled from inventory")
Set rstProd = dbsBM.OpenRecordset("Production")
Set rstScrap = dbsBM.OpenRecordset("Scrap")
Set rstUsage = dbsBM.OpenRecordset("Usage")

'---------------------------------------------------------------------------------------------
rstProd.MoveFirst
rstProd.GetRows

Do Until rstProd.EOF = True
    rstProd.GetRows
    
If rstProd!Material Like "1#####" Then
    rstFGResin.AddNew
    rstFGResin!Date = rstProd![Post date]
    rstFGResin!SAP = rstProd!Material
    rstFGResin!ims = rstProd![Old matl]
    rstFGResin!Line = DLookup("[line]", "Product by line", "[sap id] = '" & rstProd!Material & "'")
    rstFGResin![cases produced] = rstProd!SumOfQuantity
    
    
    If DLookup("[line]", "Product by line", "[sap id] = '" & rstProd!Material & "'") = 5 Or DLookup("[line]", "Product by line", "[sap id] = '" & rstProd!Material & "'") = 6 Then
    
'Calculations for bottle off of SIPA
        If rstFGBOM![Bottle IMS] = "bot33026ssl" Then
    
    'Calculate bottle usage and store in FG resin calculations table.
        rstFGResin![calculated bottle usage] = rstFGResin![cases produced] * rstFGBOM![Bottles per case]
    
    'Calculate bottle scrap and store in FG resin calculations table.
        rstFGResin![calculated bottle scrap] = (rstFGBOM![Bottles per case] * rstFGBOM![Bottle Scrap Factor]) * rstFGResin![cases produced]
    
    'Calculate amount of resin used not including Scrap
        rstFGResin![resin calculated] = rstProd!SumOfQuantity * DLookup([Bottles per case], "bottle to fg sf and bottle usage", "[sap id] = '" & rstProd!Material & "'")
        rstFGResin![resin calculated] = rstProd![resin calculated] * DLookup([Preforms needed], "bottle to fg sf and bottle usage", "[sap id] = '" & rstProd!Material & "'")
        rstFGResin![resin calculated] = rstProd![resin calculated] * DLookup([Resin Needed], "bottle to fg sf and bottle usage", "[sap id] = '" & rstProd!Material & "'")
    
    'Calculate amount of resin used to produce scraped bottles and add it to the total resin used
        rstFGResin![resin calculated] = rstFGResin![resin calculated] + (rstFGResin![calculated bottle scrap] * rstFGBOM![Resin Needed])
    
    'Calculate Resin Scrap.
        rstFGResin![resin scrap calculated] = (rstFGResin![calculated bottle usage] * rstFGBOM![Resin Scrap Factor]) + (rstFGResin![calculated bottle scrap] * rstFGBOM![Resin Scrap Factor])

    Else
    'Calculate bottle usage and store in FG resin calculations table.
        rstFGResin![calculated bottle usage] = rstFGResin![cases produced] * rstFGBOM![Bottles per case]
    
    'Calculate bottle scrap and store in FG resin calculations table.
        rstFGResin![calculated bottle scrap] = (rstFGBOM![Bottles per case] * rstFGBOM![Bottle Scrap Factor]) * rstFGResin![cases produced]

    'Calculate preform usage
        rstFGResin![calculated preform usage] = rstFGResin![calculated bottle usage] + rstFGResin![calculated bottle scrap]
        
    'calculate prefrom scrap and store in fg resin calculatinos table.
        rstFGResin![calculated preform scrap] = rstFGResin![calculated preform usage] * DLookup("[preform scrap factor]", "Bottle to FG SF and bottle usage", "[SAP ID] = '" & rstFGResin!SAP & "'")
           
    'Calculate total amount of resin used
        rstFGResin![resin calculated] = rstProd!SumOfQuantity * DLookup("[Bottles per case]", "bottle to fg sf and bottle usage", "[sap id] = '" & rstProd!Material & "'")
        rstFGResin![resin calculated] = rstFGResin![resin calculated] * DLookup("[Preforms needed]", "bottle to fg sf and bottle usage", "[sap id] = '" & rstFGResin!SAP & "'")
        rstFGResin![resin calculated] = rstFGResin![resin calculated] * DLookup("[Resin Needed]", "bottle to fg sf and bottle usage", "[sap id] = '" & rstFGResin!SAP & "'")

     'Calculate amount of resin used to produce scraped preforms/bottles and add to total
        rstFGResin![resin calculated] = rstFGResin![resin calculated] + (rstFGResin![calculated preform scrap] * rstFGBOM![Resin Needed])
    
    'Calculate Resin Scrap.
        rstFGResin![resin scrap calculated] = (rstFGResin![calculated preform usage] * rstFGBOM![Resin Scrap Factor]) + (rstFGResin![calculated preform scrap] * rstFGBOM![Resin Scrap Factor])
    
    End If '(Bottle from SIPA or husky)
    rstFGResin.Update
  End If '(not line 5 or 6)
End If '(1?????)


Loop '(do until rstprod.eof = true)


Exit_Calculate_Click:
    Exit Sub

Err_Calculate_Click:
    Msgbox Err.Description
    Resume Exit_Calculate_Click


End Sub

[/red]

X-)THANKS!!X-)

 

[mgbeye]

I believe my loop is trying to execute one too many times, but I am not able to figure out why.

 

[scking]

You need to debug the code. Go into the code and end 'REsume' following the line 'Resume Exit_Calculate_Click'. Then click F9 to set a breakpoint on the line 'Resume Exit_Calculate_Click'. Your code will stop on the breakpoint then place your cursor over the 'Resume' and enter <ctrl>F8 to move the run processing the the line and enter F8 which moves the processing by a single line to the line that caused the error. Inspect all values using the debug window. For instance, entering '?rst.EOF' would return a true if you were at the End Of File, entering '?AnyVal' would return the value. If you can't determine the problem by this then set the breakpoint earlier, maybe even the first line and evaluate what happens to each of the variables and you step through the code line-by-line.

Steve King Growth follows a healthy professional curiosity