Help please with VBA function - passing multiple variables

Question

Hi all:

I am trying to write a function in VBA for Access 2016 and am getting the following error:

Complie ERROR: Syntax error

I code to call the function is:

FunPortalPath (stRptPDFName, stPortalGrower)

The variable stRptPDFName is a string that contains the name of the report I will be writting to as a PDF file to a harddrive.  The variable stPortalGrower is the name of the grower the report is being ran for.

The funciton below determines the path the PDF file is to be written to and should use the two above variables.

Function code:

Public Function FunPortalPath(strReportName As String, stGrower As String) As String

On Error GoTo ErrorHandler

    stPathPdfName = "K:\Grower Portal" & stPortalGrower ' & "\Pool\Intake"

    If Dir(stPathPdfName, vbDirectory) = "" Then

        'Set up fodlers for grower (new folder in 1NewGrower)

        stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower

        If Dir(stPathPdfName, vbDirectory) = "" Then

            MkDir stPathPdfName

            stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool"

            MkDir stPathPdfName

            stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool\Payouts"

            MkDir stPathPdfName

            stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool\GradeSize"

            MkDir stPathPdfName

            stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool\Defects"

            MkDir stPathPdfName

            stPathPdfName = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool\Intake"

            MkDir stPathPdfName

        End If

        'set path and name for report

        PortalPath = "K:\Grower Portal\1NewGrower" & stPortalGrower & "\Pool\Intake" & stRptPDFName & ".Pdf"

    Else

        'set path and name for report

        PortalPath = "K:\Grower Portal" & stPortalGrower & "\Pool\Intake" & stRptPDFName & ".Pdf"

    End If

        Debug.Print "Path at md time:  " & stPathPdfName

ExitProdcedure:

    Exit Function

ErrorHandler:

    MsgBox "Error: Function FunPortalPath - " & Err.Number & " : " & Err.Description & "  Please record this message and contact IT.", vbOKOnly + vbInformation

    Debug.Print "Error: Function FunPortalPath  - " & Err.Number & " : " & Err.Description & "  Please record this message and contact IT."

Resume ExitProdcedure

End Function

Can anyone tell me what I am doing wrong?  I just want to pass the two variables to the function but apparently I can not get the syntax correct.

All help is greatly appreciated.

Answer 1

So let me see if I understand. It is the calling procedure that crashes and it crashes on the line that calls the FunPortalPath function? Have you tried running that function from the Immediate window? Try this from the immediate Window:

? FunPortalPath("2019_01_Grower_Intake_Statement","GERALD_GOUCHER")

Then see what happens.

Answer 2

it looks like to me you should assign a value to some variable in the call of your function,

so something like

dim junk as string

junk =  FunPortalPath (stRptPDFName, stPortalGrower)

Answer 3

Hi Who, I'm an independent adviser and will try to help.

Have you tried to determine what line of code throws the error? You can do this by stepping through the code and see where the error occurs.

Do you have Option Explicit at the top of the code module? You don't specifically dimension the variable stPathPDFName. Some other things that come to mind is it possible that one of the variables you pass is Null?

I don't see anything obvious in your code so you need to determine what line is throwing the error.

If you need further clarification on this please feel free to ask.

Answer 4

Thanks for your reply.   Yes, I have stepped through the code and have determined it is the line of code:

FunPortalPath (stRptPDFName, stPortalGrower)

That is throwing the syntax error.   It also throws the error when I compile the code using Debug -> Compile.

At the top of the module that contains the function is Option Compare Database as well as it being at the top of the module that contains all the procedures for the form the code is written for.

The snip of code in the procedure that calls the function is as follow:

'setup recordset

    Set db = CurrentDb()

    Set rec = db.OpenRecordset("tblGrowerEmailRecSet")

    intRecCount = rec.RecordCount

    'initialize counter

    intCount = 1

    'define report name

    ReportName

    'move to frist record in record set

    rec.MoveFirst

    'loop through selected records

    Do While Not rec.EOF

        'if email exist than email if not then skip grower

        If VBA.Len(rec!GrowerEmail) > 3 Then

            'populate fields and variables

'            Debug.Print intCount & "  -  " & rec!GrowerFarmName & "  -  " & rec!GrowerEmail

            Me.comGrowerFrom = rec!GrowerFarmName

            stPortalGrower = rec!GrowerFarmName

            Me.comGrowerTo = Me.comGrowerFrom

Debug.Print "stRptPDFName = " & stRptPDFName

Debug.Print "stPortalGrower = " & stPortalGrower

            'setup path - convert to function

            If UCase(objNetwork.UserName) = "DARRENLIGHTFOOT" Then

                FunPortalPath (stRptPDFName, stPortalGrower)

            Else

                PortalPath

            End If

            'save report as PDF

            modEmailReports.SaveRptPDF stRptName, stPathPdfName

            intCount = intCount + 1

        End If

    'move to the next record

    rec.MoveNext

    Loop

The procedure called (ReportName) determines the proper name for the report and writes it to the globally declared variable stRptPDFName.  The variable stPortalGrower gets it value from the record set (rec). 

When I comment out the line

FunPortalPath (stRptPDFName, stPortalGrower)

and set a break point at 

If UCase(objNetwork.UserName) = "DARRENLIGHTFOOT" Then

The results of the two debug.print statements are:

stRptPDFName = 2019_01_Grower_Intake_Statement

stPortalGrower = GERALD_GOUCHER

So they shouldn't be null when the function is called.

Do you have any ideas?   I really appreciate the help.

Thanks.

Answer 5

You use neither strReportName nor stGrower in the body of the function.

Perhaps stRptPDFName should be strReportName and stPortalGrower should be stGrower