Link to home
Create AccountLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Still having an issue with GoSub Return

I am still getting the No GoSub problem on the following code.
Public Sub cmdEmail_Click()
   
   On Error GoTo cmdEmail_Click_Error

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String

Set db = CurrentDb()

    If Me.optQry = "" Then
        Call MsgBox("Please make a selection, prior to proceeding.", vbCritical, "Alert")
        Exit Sub
    Else
        Select Case optQry
                '************************************************************************************************************************
                'This sql statement returns all Delinquent Courses for all Employees of the selected Manager- optQry = Manager Report
                '************************************************************************************************************************
            Case 1
                
                strSQL = "SELECT tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title] AS [Deliquent Courses]," & _
                            " tblEmp_RequiredLearning.DateRequired, tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr, tblEmployee.Mgr_OrgNo" & _
                        " FROM ((tblEmp_RequiredLearning LEFT JOIN tblCourseList_lkup ON" & _
                            " tblEmp_RequiredLearning.CourseRecID = tblCourseList_lkup.CourseRecID) LEFT JOIN" & _
                            " tblEmployee ON tblEmp_RequiredLearning.BEMS = tblEmployee.BEMS) LEFT JOIN" & _
                            " tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
                        " WHERE (((tblCourseList_lkup.OnXLS)=-1) and Mgr_OrgNo=" & gORG & ")" & _
                        " GROUP BY tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title]," & _
                            " tblEmp_RequiredLearning.DateRequired, tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr," & _
                            " tblEmployee.Mgr_OrgNo" & _
                        " HAVING (((Sum(IIf([CompletionDt] > [DateRequired], 1, 0))) > 0) And" & _
                            " ((IIf(Year([DateRequired]) = Year(Date()), 'x', '')) = 'x'))" & _
                        " ORDER BY tblCourseList_lkup.[Ilp Learning Title]"
                
                Set rs = db.OpenRecordset(strSQL)
                
                Do Until rs.EOF
                    gBEMS = rs.Fields("BEMS")
                    Debug.Print gBEMS
                    strSQL1 = "SELECT tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title] AS [Deliquent Courses]," & _
                                " tblEmp_RequiredLearning.DateRequired, tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr, tblEmployee.Mgr_OrgNo" & _
                            " FROM ((tblEmp_RequiredLearning LEFT JOIN tblCourseList_lkup ON tblEmp_RequiredLearning.CourseRecID = tblCourseList_lkup.CourseRecID)" & _
                                " LEFT JOIN tblEmployee ON tblEmp_RequiredLearning.BEMS = tblEmployee.BEMS) LEFT JOIN" & _
                                " tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
                            " WHERE (tblCourseList_lkup.OnXLS = -1) And (tblEmp_RequiredLearning.BEMS=" & gBEMS & ")" & _
                            " GROUP BY tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title], tblEmp_RequiredLearning.DateRequired," & _
                                " tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr, tblEmployee.Mgr_OrgNo" & _
                            " HAVING (((Sum(IIf([CompletionDt]>[DateRequired],1,0)))>0) AND ((IIf(Year([DateRequired])=Year(Date()),'x',''))='x'))" & _
                            " ORDER BY tblCourseList_lkup.[Ilp Learning Title]"
                   
                    Set rs1 = db.OpenRecordset(strSQL1)
                    gClasses = vbNullString
                    
                    Do Until rs1.EOF
                        gClasses = gClasses & vbCrLf & "Delinquent Course:  " & rs1![Deliquent Courses] & vbCrLf & "Due Date:" & rs1![DateRequired] & vbCrLf
                        rs1.MoveNext
                   Loop
                    
                    gClasses = Mid(gClasses, 3)
                    
                    strSQL1 = "SELECT BEMS, CourseName, DateRequired" & _
                            " FROM qryEmpTrain_Due30Days" & _
                            " WHERE BEMS=" & gBEMS & ""
                    
                    Set rs2 = db.OpenRecordset(strSQL1)
                    gClasses1 = vbNullString
                    
                    Do Until rs2.EOF
                        gClasses1 = gClasses1 & vbCrLf & "CoursesName:  " & rs2![CourseName] & vbCrLf & "Due Date:" & rs2![DateRequired] & vbCrLf
                        rs2.MoveNext
                    Loop
                    
                    gClasses1 = Mid(gClasses1, 3)

                    GoSub Process:
                rs.MoveNext
                Loop
                '************************************************************************************************************************
                'This sql statement returns all Delinquent Courses for selected Employees - optQry = Employee Report
                '************************************************************************************************************************
            Case 2
                
                strSQL = "SELECT tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title] AS [Deliquent Courses]," & _
                            " tblEmp_RequiredLearning.DateRequired, tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr, tblEmployee.Mgr_OrgNo" & _
                        " FROM ((tblEmp_RequiredLearning LEFT JOIN tblCourseList_lkup ON" & _
                            " tblEmp_RequiredLearning.CourseRecID = tblCourseList_lkup.CourseRecID) LEFT JOIN" & _
                            " tblEmployee ON tblEmp_RequiredLearning.BEMS = tblEmployee.BEMS) LEFT JOIN" & _
                            " tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
                        " WHERE (((tblCourseList_lkup.OnXLS)=-1) and tblEmp_RequiredLearning.BEMS=" & gBEMS & ")" & _
                        " GROUP BY tblEmp_RequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title]," & _
                            " tblEmp_RequiredLearning.DateRequired, tblEmployee.StableEmail, tblOrgListing_lkup.OrgCtr," & _
                            " tblEmployee.Mgr_OrgNo" & _
                        " HAVING (((Sum(IIf([CompletionDt] > [DateRequired], 1, 0))) > 0) And" & _
                            " ((IIf(Year([DateRequired]) = Year(Date()), 'x', '')) = 'x'))" & _
                        " ORDER BY tblCourseList_lkup.[Ilp Learning Title]"
                Set rs1 = db.OpenRecordset(strSQL)
                
                Do Until rs1.EOF
                    gClasses = gClasses & vbCrLf & "Delinquent Course:  " & rs1![Deliquent Courses] & vbCrLf & "Due Date:" & rs1![DateRequired] & vbCrLf
                    rs1.MoveNext
                Loop
                
                gClasses = Mid(gClasses, 3)
                
                strSQL1 = "SELECT BEMS, CourseName, DateRequired" & _
                        " FROM qryEmpTrain_Due30Days" & _
                        " WHERE BEMS=" & gBEMS & ""
                
                Set rs2 = db.OpenRecordset(strSQL1)
                    gClasses1 = vbNullString
                    
                    Do Until rs2.EOF
                        gClasses1 = gClasses1 & vbCrLf & "CoursesName:  " & rs2![CourseName] & vbCrLf & "Due Date:" & rs2![DateRequired] & vbCrLf
                        rs2.MoveNext
                    Loop
                    
                    gClasses1 = Mid(gClasses1, 3)
                    GoSub Process:
            Case Else
        End Select
    End If

Process:
    If gClasses = vbNullString And gClasses1 = vbNullString Then
        Select Case _
            MsgBox("There is no Training Required at this time for the selected Manager/Employee", _
            vbOKCancel Or vbExclamation Or vbDefaultButton1, _
            "No Records Found")
        
           Case vbOK Or vbCancel
                Return
                Exit Sub
        End Select
    ElseIf gClasses <> vbNullString Or gClasses1 <> vbNullString Then
        gEmpEmail = Nz(DLookup("StableEmail", "tblEmployee", "BEMS = " & gBEMS))
        EmailTraining
    End If

Return
         Select Case _
            MsgBox("Do you wish to send the Training Notification EMails Now?", _
            vbOKCancel Or vbExclamation Or vbDefaultButton1, _
            "No Records Found")
           Case vbOK
                SendMessages
                Exit Sub
           Case vbCancel
                Exit Sub
        End Select

   On Error GoTo 0
   Exit Sub

cmdEmail_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEmail_Click of VBA Document Form_frmTrainStatus"
End Sub

Open in new window

Avatar of TechEEKnight123
TechEEKnight123

Please Look For Any Syntax Errors
Some times,

there are Errors in the Syntax of the Script Code, But A Script is always depends upon the Logic of The Script Programmer, e.g. your Way of Doing tasks in VB Code can be a Issue For Some one Else, and he might force his way of Source Coding,

I Hope you Understand TechEEKnight123
Avatar of Jim Dettman (EE MVE)

 It's  GoSub Process, not  GoSub Process:

 A colon indicates a label.

<<Please Look For Any Syntax Errors >>

  Simply do a compile.  If there are any they will be high lighted.

JimD
Avatar of Karen Schaefer

ASKER

It complies fine -  and the code runs great even through the subroutine, however, until it gets to the last record and then I get the No goSub error.

What am I missing?

Karen
Here is the error msg when I reach the last record in the series.
errorMsg.jpg
I Believe there Might be a Logical Problem, Some Critical Parameters Shoule Be Missing or Not Properly Initliased...

I hope that you Understand this Also.... with ease....
Sorry have no idea what you are talking about.

K
Karen,

You need to do something here after the end if and before the Process:

        End Select
    End If

Process:
    If gClasses = vbNullString And gClasses1 = vbNullString Then

 
  What's happening is that your falling through your routine and then not exiting before Process:  you fall through that, hit the RETURN and get an error because you ended up going through Process: without using a GOSUB.  So when it hits the return, it has no idea what it's supposed to return to.

JimD.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I took your suggestion and removed the GoSub and placed the code within body of the code.

Thanks for the assistance
Thank You, JDettman,

For supplying/Reviewing the Source Code, Actually kfschaefer1 had a problem with his Syntaxtual Error, i was just Pointing kfschaefer1 to that only, to review and recode his work....

But JDettman, You are fast than me,
Thank You, kfschaefer1
Thanks