Problem with Optional Arguments

[PNGBill]

Hi Forum, Access 2000
I have a Procedure that constructs and sends an email and I have added a MsgBox and Response to allow the option to print a Statement to be attached to the email.

This works fine except... in the Statement Printing Function, it also has the option to send an email which of course is not needed this time.

I have added an optional Argument to the Statement Printing Function but the email Function won't allow me to add the option when calling the Statement.

here are some code extracts.

This is on the Email Function and allows the Statement to be called.

Response = MsgBox("Do you want to Print a Loan Statement to attach to this email? Click Yes or No", 68)
        If Response = vbYes Then
            LoanStatementSingle (CStr(LoanRef))
        End If

This works

but this doesn't:

LoanStatementSingle (CStr(LoanRef), "NoEmail")

Where am I going wrong??

The idea is that the code will print the statement and exit before asking if to email as of course, we are already send an email.
But I can't get the code to accept the argument


Here is the Statement Function

Public Function LoanStatementSingle(LoanRef As String, Optional DoNotEmail As String) As String

and in the code is the If then for the optional Argument

If DoNotEmail = "NoEmail" Then
            'Close database variables
        rst.Close
        dbs.Close
        Exit Function
    End If

 

[RuralGuy]

Have you put a breakpoint in the function and looked at the incoming values?

 

[DCrake]

Why bother using the Optional command? I would change this to a boolean argument and act accordingly.

 

[RuralGuy]

David is right but it still begs the question: "Why isn't it working?".

 

[PNGBill]

The Statement Function works in Immediate Window with this

?LoanStatementSingle(7934, "NoEmail")

But in the Email Function it just won't accept a 2nd argument and goes all red on me

LoanStatementSingle (CStr(LoanRef), "NoEmail")

I had a thought that it may have been the function name ie with As string on the end but this hasn't made any difference.

Public Function LoanStatementSingle(LoanRef As String, Optional DoNotEmail As String)

Public Function LoanStatementSingle(LoanRef As String, Optional DoNotEmail As String) As String

 

[PNGBill]

Have you put a breakpoint in the function and looked at the incoming values?

Thankd RuralGuy but not sure how to test incoming values when the code actually works fine in the immediate window

 

[PNGBill]

Why bother using the Optional command? I would change this to a boolean argument and act accordingly.

Sorry David, I am lost on this issue. My book tells me Boolean is true or false. I have to have "LoanRef" in the argument for the 2nd function to know what data to collect.

 

[boblarson]

If you are just calling it like this:

LoanStatementSingle (CStr(LoanRef), "NoEmail")

You need to get rid of the parens:

LoanStatementSingle CStr(LoanRef), "NoEmail"

Otherwise you can use

Call LoanStatementSingle (CStr(LoanRef), "NoEmail")

 

[PNGBill]

Thanks Bob, never would have thought of that.

 

[RuralGuy]

Way to go Bob! Nice catch.

 

[boblarson]

Way to go Bob! Nice catch.

 

[gemma-the-husky]

is this sorted? - the code for your optional argument is incorrect

An optional argument must be UNTYPED. and you test whether it is included with ISMISSING

SO

function header
Public Function LoanStatementSingle(LoanRef As String, Optional DoNotEmail) As String

function body
if not ismissing(donotemail) then
   ....
else
  select case donotemail
  etc
end if

 

[boblarson]

An optional argument must be UNTYPED.

Dave:

That is a completely false statement. I just thought you ought to know that optional arguments can and SHOULD be strong typed. There is no programming language out there that I know of where that statement would be true. It isn't for VB6, VBA, VB.NET, C# etc.

 

[RuralGuy]

Bob,
I can't find it right now but I remember reading that Optional arguments *must* be a variant type or they may not be Optional. Am I wrong here?

 

[boblarson]

Bob,
I can't find it right now but I remember reading that Optional arguments *must* be a variant type or they may not be Optional. Am I wrong here?

Yep, you are incorrect. In fact, I use strongly typed optionals quite frequently myself so I know for 100% certainty that they work.

 

[boblarson]

Oh and one example of my code which uses a strongly typed optional is my Export to Excel code here.

 

[RuralGuy]

*And* they are allowed to be missing?

 

[boblarson]

*And* they are allowed to be missing?

That is exactly the definition of OPTIONAL. If it is OPTIONAL you do not have to pass anything to the procedure for it. However, you still need to follow the same rules as any prebuilt procedure in that

All Optionals must be listed AFTER any required inputs. You can't intersperse them - so you can't do

Function TestF(lngID As Long, OPTIONAL strName As String, intTest As Integer) As String

You would need to use:

Function TestF(lngID As Long, intTest As Integer, OPTIONAL strName As String) As String

instead.

Then you can pass the parameters like

TestF 23342, 2, "MyName"
or
TestF 23342, 2

Or if you have multiple Optionals:

Function TestF(lngID As Long, intTest As Integer, OPTIONAL strName As String, OPTIONAL strDescription As String, OPTIONAL blnSave As Boolean) As String

You can call it with commas:

TestF 23342, 2, "MyName", , False

or as named arguments

TestF 23342, 2, blnSave:= False


So hopefully that helps.

 

[RuralGuy]

I've been looking through ac2002 VBA help and it supports just what you said. I have no idea how I got side tracked on that subject. Thanks for the update.

 

[boblarson]

Oh, you and Dave might have been thinking of Allen Browne's page here which states that you should use variants for optionals. I disagree with his assertion in that it isn't always a problem if you do the right test based on your datatype (he later does go into how you need to test correctly if you don't use variants). If you have a boolean, as he suggests, IsMissing won't return a true for it if it isn't provided, it will return a FALSE as a default. But that is exactly how most Microsoft Optionals are used - they have a default set and most booleans have a default of False.