Using a Function in a Sub? 2

[quest4]

Good afternoon, folks. I have been trying to figure this out for the last couple of days and to no avail, maybe someone here can straighten me out. To start I have a function, which collects e-mail addresses from a table and sends a message to everyone who has a certain checkbox checked. Here is the function:
Function EMailUsers()
Dim rs As Object
Dim strSQL As String
Dim strERecipient As String
Set rs = CreateObject("ADODB.Recordset&quot
strSQL = MystrSQL
rs.Open strSQL, CurrentProject.Connection
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
strERecipient = strERecipient & rs(0) & ";"
rs.MoveNext
Loop
If strERecipient <> &quot;&quot; Then
strERecipient = Left(strERecipient, Len(strERecipient) - 1)
End If
DoCmd.SendObject , , , strERecipient, , , &quot;ECN &quot; & Forms!frmECNMasterData200!!ECNNumber & &quot; is ready for Assembly/Purchasing Approval.&quot;, , False 'formats and sends email to Authorized users
End If
End Function
Sense I have a dozen or so cmdButtons I figure this would be the way to go, and just call it in the sub to run it. MystrSQL has thre different variations, depending on which checkbox if checked on the main form. Here is the sub:
Dim MystrSQL As String
If Me.Check208 = True Or Me.Check210 = True Then
MystrSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdAsy] = True&quot;
Call EMailUsers
End If
When I attempt to run it all I get is:
-2147217908 Command text is not set to command object.
Anyone one have any suggestions? Thank you in advance to anyone redering assistance.

 

[jwhjr]

It looks as if you need to pass in the SQL statement as a parameter in the function:

Function EMailUsers(strSQL as String)
then it would be:

call EMailUsers(MystrSQL) in your Sub.

HTH
JR

 

[quest4]

Thank you for the response, jwhjr. I made the changes in the function and in the procedure, same error. I didn't think it would be that easy. Thank you again for the assistance.

 

[jwhjr]

I used the following modification of your function and it worked fine for me:

Function EMailUsers(strSQL As String)
Dim rs As Object
Dim strERecipient As String
Set rs = CreateObject(&quot;ADODB.Recordset&quot
rs.Open strSQL, CurrentProject.Connection
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
strERecipient = strERecipient & rs(0) & &quot;;&quot;
rs.MoveNext
Loop
If strERecipient <> &quot;&quot; Then
strERecipient = Left(strERecipient, Len(strERecipient) - 1)
End If
DoCmd.SendObject , , , strERecipient, , , &quot;ECN is ready for Assembly/Purchasing Approval.&quot;, &quot;test&quot;, True 'formats and sends email to Authorized users
End If
End Function

Here is the code for the form I created:

Private Sub Command0_Click()
Dim MystrSQL As String

MystrSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers&quot;
Call EMailUsers(MystrSQL)

End Sub

I just made a table with one field to hold my e-mail address in, pushed the button and it sent me an e-mail. I don't know if this will help or not.

JR

 

[quest4]

Major improvement, kind of. I remove the strSQL=MystrSQL line from the function and add a line to the procedure, strSQL = MystrSQL at the top. I got a strange message when I clicked the YES of the Outlook window, I get an error 424 Object required, but the e-mail message was sent and it arrived. Then I unchecked a couple of boxes so I could test the next checkbox and the second I click the cmdButton I get the error 424 Object required message. What am I doing wrong here? Thank you again for your assistance.

 

[jwhjr]

Can you post the updated code for the function and the sub for me to take a look at?

 

[quest4]

Thank you again, jwhjr. No problem, here is the function:
Function EMailUsers(strSQL As String)
Dim rs As Object
Dim strERecipient As String
Set rs = CreateObject(&quot;ADODB.Recordset&quot

'strSQL = MystrSQL
rs.Open strSQL, CurrentProject.Connection
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
strERecipient = strERecipient & rs(0) & &quot;;&quot;
rs.MoveNext
Loop
If strERecipient <> &quot;&quot; Then
strERecipient = Left(strERecipient, Len(strERecipient) - 1)
End If
MsgBox strERecipient 'Just checking who is being e-mailed
DoCmd.SendObject , , , strERecipient, , , &quot;ECN is ready for Assembly/Purchasing Approval.&quot;, &quot;test&quot;, False
End If
End Function
This is the entire procedure:
Private Sub CCDApproved_Click()
On Error GoTo Err_CCDApproved_Click

Dim MystrSQL As String
strSQL = MystrSQL

If DCount(&quot;[LogOn]&quot;, &quot;tblAuthorizedUsers&quot;, &quot;[LogOn]='&quot; & fOSUserName & &quot;' And [ApdCCD] = True&quot = 0 Then
MsgBox &quot;You Are Not Authorized To Approve This Section.&quot;
Me!CCDApproved.SetFocus 'Re-Enter Approval, same field
Else 'The User is Authorized
Me.CCDApprovedBy = fOSUserName 'Enter LogOn in Approved By textbox
Me.CCDDate = Date 'Enter Today's Date in the Date textbox
Me.CCDComments.SetFocus 'Set Focus on Comment textbox
Me.CCDApproved.Visible = False 'Make CmdButton Invisible
If Me.Check208 = True Or Me.Check210 = True Then
MystrSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdAsy] = True&quot;
Call EMailUsers(MystrSQL)
ElseIf Me.Check227 = True Then
MystrSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdBrz] = True&quot;
Call EMailUsers(MystrSQL)
ElseIf Me.Check206 = True Then
MystrSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdMfg] = True&quot;
Call EMailUsers(MystrSQL)
End If
End If

rs.Close
Set rs = Nothing

Exit_CCDApproved_Click:
Exit Sub

Err_CCDApproved_Click:
MsgBox Err.Number & &quot; &quot; & Err.Description
Resume Exit_CCDApproved_Click

End Sub
The error 424 is still coming up everytime I click YES in the Outlook Security Popup Window, while send the e-mail. Also I would like to to alter the e-mail message, the Assembly/Purchasing I would like to be a string where I can change it like MystrSQL, maybe call it ApprovalType, besides this one there is Brazing and Manufacturing, also. Thank you again for everything I really appreaciate your help.

 

[jwhjr]

Take this line out of your sub:

strSQL = MystrSQL

Other than the code referencing your form, there is nothing different from what I tried. You are not actually using the strSQL variable in your sub so it is not necessary. Let me know if you have any luck once you remove it.

JR

 

[quest4]

jwhjr, it works about the same. I still get the error 424 object required, right after I click the YES in the Outlook Popup Security Window, but the message is being sent. Am I missing something here? I would still like to be able to use use a string variable instead of Assembly/Purchasing, like I was saying with the ApprovalType and then setting that to Assembly/Purchasing or Brazing or Manufacturing, depending on which section I am in. Can that be done? Thank you again for everything.

 

[jwhjr]

You should be able to pass those in the same as the SQL statement, just add them as parameters in the Function. As to the other issue, try replacing this line in your Function:

Set rs = CreateObject(&quot;ADODB.Recordset&quot

With this line.
Set rs = New ADODB.Recordset

This should create a new instance of the recordset object. Let me know if you have any better luck with this.

JR

 

[quest4]

jwhjr, I tried the change and nothing different, still get the error 424, for some reason. I then add the ApprovalType, like this:
DoCmd.SendObject , , , strERecipient, , , &quot;ECN &quot; & Forms!frmECNMasterData200!ECNNumber & &quot; is ready for &quot; & ApprovalType & &quot; Approval.&quot;, , False
Then in each section of the procedure, I put this:
ApprovalType = &quot;Brazing&quot;
My message had the ECN Number in it but not the ApprovalType. What did I do wrong in the function statement? Thank you again for everything.

 

[jwhjr]

What version of Access are you using, and which references do you have checked?

 

[quest4]

Good morning, jwhjr. I am using Access 2000 with service pack 3 installed. I have the following references turned on, Virtual Basic for Applications, MS Access 9.0 Objects Library, OLE Automation, MS ActiveX Direct Objects 2.1 Library, MS ActiveX Plugin, and MS DAO 3.6 Object Library. I hope this is everything that you need, thank you again for the response.

 

[PeteJohnston]

Try taking out the error handling temporarily (comment out the &quot;On Error GoTo Err_CCDApproved_Click&quot; line) and let Access hit you with the message at the actual line which is in error. It pops up a magbox with the error message, End and Debug buttons so you can then go into debug and check it out some more. Maybe that will give us another clue? You can put the error handling back when you get it working.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[quest4]

Thank you for the response, Pete. I commented out the Error lines of code, all four of them, and ran it. The debugger did finally rear it ugly head and it was for that Error 424 bug and I was surprised at what was hi-lited, it was the line:
rs.Close
I don't understand why that would be a problem. Also it found no problems with my strApprovalType string, and I added a line right before the Call statement:
Msgbox strApprovalType
The correct string appeared there, but did not transfer into the Call statement. I also tried changeing the Call statement to:
Call EMailUsers(MystrSQL, strApprovalType )
I errored out on that , too many variables or something like that. Any suggestions on this? Thank you again for the assistance.

 

[quest4]

Never mind, I stumbled into the answer, for the error problem and the missing string. Thank you one and all and have a great day.

 

[jwhjr]

I'm glad you got it, for my own curiousity could you elaborate on what finally fixed it as I wasn't able to duplicate the error. Thanks.

JR

 

[quest4]

My stupidity was causing the error. This originally started as a sub-procedure on one cmdButton, but I could never get it to work beyond the If part of what is now the function. So I copied it over to the module section, which is where you came in. Do to the length of everything, I failed to see that I left behind the rs.Close line and guess what, with Pete's little change the hi-liter got it. It took me a minute or two to realize what it was doing, then I made the change and bingo, it worked. I do have start down the list of cmdButtons now and I just got to the buttom and I was wondering how to bring in a cmdButton which is not controled by the checkboxes. This is in the procedure, and I don't really have anything for it to check. It would be something like:
If Me.Check227 = True Then
strSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdBrz] = True&quot;
strApprovalType = &quot;Brazing&quot;
MsgBox strApprovalType
Call EMailUsers(strSQL, strApprovalType)
ElseIf Me.Check206 = True Then
strSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdMfg] = True&quot;
strApprovalType = &quot;Manufacturing&quot;
MsgBox strApprovalType
Call EMailUsers(strSQL, strApprovalType)
ElseIf Me.Check208 = True Or Me.Check210 = True Then <-- ?
strSQL = &quot;SELECT [EMailAddress] FROM tblAuthorizedUsers WHERE [ApdPC] = True&quot;
strApprovalType = &quot;Production Control&quot;
MsgBox strApprovalType
Call EMailUsers(strSQL, strApprovalType)
End If
The last elseif I don't know exactly what to say there, it basically is if not the first two then this. Should it be a else instead of and elseif? Thank you again for all of the assiatnace and time you have given me, I really appreciate it.

 

[PeteJohnston]

Totally of the top of my head

Select Case True
Case Is = Me.Check227
    strWHERE = "[ApdBrz] = True"
    strApprovalType = "Brazing"
Case Is = Me.Check206
    strWHERE = "[ApdMfg] = True"
    strApprovalType = "Manufacturing"
Case Else
    strWHERE = "[ApdPC] = True"
    strApprovalType = "Production Control"
End Select

strSQL = "SELECT [EMailAddress] FROM " & _
   "tblAuthorizedUsers WHERE " & strWHERE
MsgBox strApprovalType
Call EMailUsers(strSQL, strApprovalType)

simplifies it a little and should catch the last bit if none of the others are true.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[quest4]

Thank you for your input Pete. I changed it from a procedure to a function and add a couple of variables to it and the dange thing started to work like a champ. I am not quite sure why the change from sub to function got it to start working correctly, but it did. Then I added a couple of variables and evetything started to click right, except that virus meesage from Outlook. I have heard that CDO should cure that, but I just haven't had the time to check it out. Thank you again for the assistance.