going from sub routine to original routine errors occure

[Robert M]

I have a program that calls for a sub routine to process the records then return to the original routine. There is nothing that is passed to or from this process.
Both programs run and do as they are designed to do except when the sub routine is returning to the original routine.

"THIS IS THE ORIGINAL ROUTINE"
'THIS CALLS UP THE SUB ROUTINE Egroup"
Call Egroup
'ON RETURN REOPEN FORM WITH FILTERED QUERY
DoCmd.OpenForm "Email_List_Selection", acNormal, "Email_List_Arts"

"THIS IS THE SUB ROUTINE Egroup"
"(PROCESSES THE DATABASE RECORDS CORRECTLY THEN RETURNS TO ORIGINAL ROUTINE)"
rst.close
Set rst = Nothing
Set DB = Nothing

Exit_cmdExit_Click:
DoCmd.OpenForm "Email_List_Selection", acNormal, "Email_List"
DoCmd.ShowAllRecords
Exit Sub
Err_Oops_Click:
msgbox Err.Description
Rtrn_Click
Resume Exit_cmdExit_Click

End Sub

Everything works up until the sub routine is returned to the original routine. At that point I start getting "Enter Parameter Value" errors for three fields that I have checked to make sure they are in the queries. Everything is in the query that the "Enter Parameter Value" error ask for.

Does anybody have any thoughts on this, what could cause it and what I would need to check to get it back to working correctly?

Thank you for your help and your time.

Robert M

 

[boblarson]

You need to include the entire code for Egroup. We can't know what is happening unless we can see what the other code is.

 

[Robert M]

Didn't think that mattered as both programs work, but here is the Main sub routine and sub routine that is called from the Main sub routine
'THERE ARE ABOUT 9 OF THESE MAIN ROUTINES THAT CAN BE SELECTED, ALL ARE SET UP THE SAME WAY EXCEPT FOR THE FINAL LINE.
Private Sub Church_Click()
SetLook

HideButtons

Show = "CHURCH" 'THIS WILL BE USED TO SELELCT THE CASE IN THE SUB ROUTINE CALLED
Showit = "Church"

Egroup
'IT IS AT THIS AFTER i HAVE LEFT THE SUB ROUTINE CALLED AND BEFORE I RETURN HERE THAT I GET MY FIRST "Enter Parameter Value" ERROR.
'I GET TWO MORE "Enter Parameter Value" ERROR WITH DIFFERING FIELDS

DoCmd.OpenForm "Email_List_Selection", acNormal, "Email_List_Church"
'THE ABOVE LINE OF CODE REOPENS THE FORM USING THE FILTER QUERY SELECTED.

End Sub

'THIS IS THE CALLED SUB Egroup
Private Sub Egroup()
On Error GoTo Err_Oops_Click
'IDENTIFY AND DEFINE VARIABLES
Dim Egrp As String
Dim Egrp1 As String
Dim Egrp2 As String
Dim strSQL As String
Dim DB As DAO.Database
Dim rst As DAO.Recordset

Set DB = CurrentDb
'SELECT CASE DEPENDING ON FILTER QUERY SELECTED VIA FIELD "SHOW"
Select Case Show
Case "Arts"
strSQL = "SELECT Email FROM Email_List_Arts"
Case "Church"
strSQL = "SELECT DISTINCT Email FROM Email_List_Church"
Case "Governmental"
strSQL = "SELECT DISTINCT Email FROM Email_List_Governmental"
Case "Healthcare"
strSQL = "SELECT DISTINCT Email FROM Email_List_Healthcare"
Case "Not For Profit"
strSQL = "SELECT DISTINCT Email FROM Email_List_NFP"
Case "Nursing Home Assited Living"
strSQL = "SELECT DISTINCT Email FROM Email_List_NHAL"
Case "Retirement Plan"
strSQL = "SELECT DISTINCT Email FROM Email_List_Retirement"
Case "School"
strSQL = "SELECT DISTINCT Email FROM Email_List_School"
Case "Webinar"
strSQL = "SELECT DISTINCT Email FROM Email_List_Webinar"
Case Else
strSQL = "SELECT DISTINCT Email FROM Email_List"
End Select
'OPEND DATABASE
Set rst = DB.OpenRecordset(strSQL, dbOpenDynaset)
'GOTO FIRST RECORDS
rst.MoveFirst
Egrp = rst!Email
rst.MoveNext
Egrp1 = rst!Email
'CONTINUE SELECTING Egrp1 UNTIL DIFFERENT FROM Egrp
Do While Egrp = Egrp1
rst.MoveNext
Egrp1 = rst!Email
Loop
'CREATE EMAIL LISTING OF EMAILS SELECTED FROM FILTER QUERY
Do Until rst.EOF
Egrp2 = rst!Email
If Egrp1 <> Egrp2 Then
Egrp = Egrp & ";" & Egrp1
End If
Egrp1 = Egrp2
rst.MoveNext
Loop

Egrp = Egrp & ";" & Egrp2
'COPY Egrp TO BttmLn FIELD, THIS WILL BE USED TO CREATE EMAIL SEND TO LIST
Forms!Email_List_Selection!BttmLn = Egrp

rst.Close
Set rst = Nothing
Set DB = Nothing
'FINISHED WITH PROGRAM AND RETURNING TO ORIGINAL ROUTINE

Exit_cmdExit_Click:
DoCmd.OpenForm "Email_List_Selection", acNormal, "Email_List"
DoCmd.ShowAllRecords
Exit Sub
Err_Oops_Click:
msgbox Err.Description
Rtrn_Click
Resume Exit_cmdExit_Click

End Sub

The SetLook and HideButton call are used to modify the form slightly by hiding those fields that are no longer needed after selecting the filter query to use.

Thanks again for your help on this and my appologies for not including the full program.

Robert M