Correct Syntax

[bidbud68]

Hi what would be the correct syntax for the Call line. Thanks

Option Compare Database

   Sub Send_Monthly_Invoices()
   
   Dim dbsReservations As DAO.Database
   Dim rstInvoices As DAO.Recordset
   Dim strSQL As String
   Dim rdate As Date
   Dim strCondition1 As Variant
   Dim strEmailRecipient As String
   
   'On Error GoTo ErrorHandler
   
   Set dbsReservations = CurrentDb
   
   rdate = InputBox("Enter Date")
   
   strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT Reservations.ReservationID, Accounts.Item, Customers.EmailAddress, * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE (((Accounts.Date) = #19/09/2012#)) ORDER BY Accounts.Date;"
   Set rstInvoices = dbsReservations.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rstInvoices
     Do Until .EOF
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         DoCmd.OpenReport "Booking Confirmation", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         'DoCmd.OpenReport "Send Invoice Email", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         Call SendInvoiceEmail(strEmailRecipient = & rstInvoices![EmailAddress])
         .MoveNext
     Loop
   End With

   rstInvoices.Close
   dbsReservations.Close
   
   Set rstInvoices = Nothing
   Set dbsReservations = Nothing

   Exit Sub

   'ErrorHandler:
   '  MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

   End Sub

Option Explicit

  Sub SendInvoiceEmail(strEmailRecipient As String, Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
      
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
      If IsNull(strEmailRecipient) Then
        Set objOutlookRecip = .Recipients.Add("emailhere")
        Else
        Set objOutlookRecip = .Recipients.Add(strEmailRecipient)
      End If
      objOutlookRecip.Type = olTo
  .....................

[June7]

Call SendInvoiceEmail(rstInvoices![EmailAddress])

[Rod]

In my experience the 'Call' syntax is rarely used for VBA Sub and Function procedures. In fact I have never used it for such much preferring the 'Object.Method/Property' form of syntax. I suspect that programmers coming from other languages and environments find it hard to abandon the 'Call' type of syntax.

Reading the help regarding 'Call,' I find that the syntax is different from the usual way for VBA procedures. Moreover, if I have understood the help correctly, then if a 'Call' is made to a Function procedure, the returned value is ignored. Not exactly what you want.

I assume your post concerns the statement in the first procedure:

Call SendInvoiceEmail(strEmailRecipient = & rstInvoices![EmailAddress])

I would write the statement as:

SendInvoiceEmail strEmailRecipient:=rstInvoices!EmailAddress

Note:

1. The 'Call' keyword is not used.
2. Because the invoked procedure is a Sub procedure, parentheses are not needed around the argument list.
3. When specifying named arguments, the assignment operator is ':='
4. Because there are no special characters in the field name then the brackets are redundant.

I have, however, some concerns. I note that the second procedure is preceded by 'Option Explicit.' This indicates it is in a separate module from the first procedure. If this is the case then I suggest it is safer to extend my syntax by naming the module explicitly. (If SendInvoiceEmail is a unique name then VBA will find it wherever it is but why rely on defaults? However as soon as you have multiple instances of a class module, the explicit qualification is essential.)

ModuleName.SendInvoiceEmail strEmailRecipient:=rstInvoices!EmailAddress

On a similar topic, if the invoked Sub procedure is in another module then it must be a Public procedure. By default all unspecified procedures are Public but I would prefer to see the second procedure begin:

Public Sub SendInvoiceEmail(strEmailRecipient As String, Optional AttachmentPath)

[Rod]

Sorry June7, didn't see your post.

[bidbud68]

Hi Rod

Thanks for your detailed reply.

The Call does seem to work as the invoices get sent off to the email addresses in the database however what I am having problems with now is the selection of the strSQL as I cannot seem to get it to return only the records with strCondition 1 but the report is fine when I go to the underlying query and put the date into the Datasheet. See : https://www.accessforums.net/program...ion-29132.html

[Rod]

I'll get back to you re the SQL. Meanwhile some clarification.

- Using the 'Call' keyword you must enclose the arguments in parentheses as per June7's post. I'm not sure whether the Call syntax allows named arguments and if so, what the correct syntax is.

- Using the Module.Procedure syntax you must use parentheses for Function arguments but omit them for Sub arguments. For example: A = Mod1.Proc(Arg1, Arg2) is a Function call and Mod1.Proc Arg1,Arg2 is a Sub call.

Meanwhile try to copy and paste the strSQL into the Query Design SQL window and 'play' with it there using the design grid. You may solve your problem that way.

[Rod]

however what I am having problems with now is the selection of the strSQL as I cannot seem to get it to return only the records with strCondition 1 but the report is fine when I go to the underlying query and put the date into the Datasheet. See : https://www.accessforums.net/programm...ion-29132.html

I see there a lot of posts in the thread you quote. June7 and pbaldy are both more than capable of helping you out. It is not my practice to butt in uninvited unless I see an obvious error or believe I can add something to the discussion otherwise, as we all have different styles, it leads to confusion.

However I am concerned by

rdate = InputBox("Enter Date")
strCondition1 = "#" & rdate & "#"

Please read Allen Browne's excellent write up on dates in Access:

http://allenbrowne.com/ser-36.html