• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Getting an error right before ending sub routine

Hello,

I keep getting an error at the end of my sub routine.  Here is my code:
Private Sub YEXCLUSIONS()
On Error GoTo ERR_YEXCLUSIONS

Dim str_ex, str_chk_yex, str_UPD_yex As String
Dim rs_yex, rs_chk_yex, rs_yEX_test As DAO.Recordset
Dim int_yex, i  As Integer
Dim str_CO_OR, str_MG As String
Dim EX_error As String


str_ex = "SELECT TMP_RR_CONS.ID, TMP_RR_CONS.RR_ID, TMP_RR_CONS.ORIGIN, TMP_RR_CONS.FINAL, " & _
        "TMP_RR_CONS.MG " & _
    "FROM TMP_RR_CONS " & _
    "WHERE TMP_RR_CONS.FINAL = False " & _
        "AND TMP_RR_CONS.RR_ID = " & dbl_REM_ID
Set rs_yex = CurrentDb.OpenRecordset(str_ex)
    
    If rs_yex.RecordCount <> 0 Then

            rs_yex.MoveLast
            int_yex = rs_yex.RecordCount
            rs_yex.MoveFirst

        'set up for loop based on the value of int_yex
        For i = 1 To int_yex
            str_CO_OR = rs_yex.Fields(2)
            str_MG = rs_yex.Fields(4)
            
            'build sql to check and see if this record will
            str_chk_yex = "SELECT YEXCLUSION.[Material Group], YEXCLUSION.[Grower Country], " & _
                    "YEXCLUSION.[Demand Country] " & _
                "FROM YEXCLUSION " & _
                "WHERE YEXCLUSION.[Material Group]= '" & str_MG & "' " & _
                    "AND YEXCLUSION.[Grower Country]= '" & str_CO_OR & "' " & _
                    "AND YEXCLUSION.[Demand Country]= '" & str_COUNTRY & "' "

            Set rs_yEX_test = CurrentDb.OpenRecordset(str_chk_yex)
                        
            If rs_yEX_test.RecordCount <> 0 Then

                'run a query to remove this particular record from the tmp_rr_cons by setting final to true
                str_UPD_yex = "UPDATE TMP_RR_CONS " & _
                    "SET TMP_RR_CONS.FINAL = True, " & _
                        "TMP_RR_CONS.REASON = '1' " & _
                    "WHERE TMP_RR_CONS.ID = " & rs_yex.Fields(0)
                CurrentDb.Execute (str_UPD_yex)
            
            End If
            
            rs_yex.MoveNext
            
        Next i
        
    End If


ERR_YEXCLUSIONS:
MsgBox "An error occurred: @YEXCLUSIONS" & vbCrLf & Err.Number & vbCrLf & Err.Description
Exit Sub

End Sub

Open in new window


I narrowed it down to its occurance happening right before the ERR_YEXCLUSIONS:

Thank you!
0
pwdells
Asked:
pwdells
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what is the error message?

to know which line is generating the error,
 comment this line from your code

   '  On Error GoTo ERR_YEXCLUSIONS

now run the code, when you get the error, click the Debug button to find where the code stops.

post here the result.
0
 
pwdellsAuthor Commented:
Ironically, I don't get an error to report about when I comment that code out...  
0
 
Rey Obrero (Capricorn1)Commented:
try this codes
Private Sub YEXCLUSIONS()
On Error GoTo ERR_YEXCLUSIONS

Dim str_ex, str_chk_yex, str_UPD_yex As String
Dim rs_yex, rs_chk_yex, rs_yEX_test As DAO.Recordset
Dim int_yex, i  As Integer
Dim str_CO_OR, str_MG As String
Dim EX_error As String


str_ex = "SELECT TMP_RR_CONS.ID, TMP_RR_CONS.RR_ID, TMP_RR_CONS.ORIGIN, TMP_RR_CONS.FINAL, " & _
        "TMP_RR_CONS.MG " & _
    "FROM TMP_RR_CONS " & _
    "WHERE TMP_RR_CONS.FINAL = False " & _
        "AND TMP_RR_CONS.RR_ID = " & dbl_REM_ID
Set rs_yex = CurrentDb.OpenRecordset(str_ex)
    
    If rs_yex.RecordCount <> 0 Then

            rs_yex.MoveLast
            int_yex = rs_yex.RecordCount
            rs_yex.MoveFirst

        'set up for loop based on the value of int_yex
        For i = 1 To int_yex
            str_CO_OR = rs_yex.Fields(2)
            str_MG = rs_yex.Fields(4)
            
            'build sql to check and see if this record will
            str_chk_yex = "SELECT YEXCLUSION.[Material Group], YEXCLUSION.[Grower Country], " & _
                    "YEXCLUSION.[Demand Country] " & _
                "FROM YEXCLUSION " & _
                "WHERE YEXCLUSION.[Material Group]= '" & str_MG & "' " & _
                    "AND YEXCLUSION.[Grower Country]= '" & str_CO_OR & "' " & _
                    "AND YEXCLUSION.[Demand Country]= '" & str_COUNTRY & "' "

            Set rs_yEX_test = CurrentDb.OpenRecordset(str_chk_yex)
                        
            If rs_yEX_test.RecordCount <> 0 Then

                'run a query to remove this particular record from the tmp_rr_cons by setting final to true
                str_UPD_yex = "UPDATE TMP_RR_CONS " & _
                    "SET TMP_RR_CONS.FINAL = True, " & _
                        "TMP_RR_CONS.REASON = '1' " & _
                    "WHERE TMP_RR_CONS.ID = " & rs_yex.Fields(0)
                CurrentDb.Execute (str_UPD_yex)
            
            End If
            
            rs_yex.MoveNext
            
        Next i
        
    End If

ERR_YEXCLUSIONS_Exit:
    Exit Sub

ERR_YEXCLUSIONS:
MsgBox "An error occurred: @YEXCLUSIONS" & vbCrLf & Err.Number & vbCrLf & Err.Description

    Resume ERR_YEXCLUSIONS_Exit
End Sub

Open in new window

0
 
pwdellsAuthor Commented:
Cap rox again.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now