VBA ByVal vs ByRef Passing Arguments

[ylatodd]

Hello,

I am having some issues with a couple of Import routines I created.

The code below works just fine:


Public Function ImportUserResponsibilities() As Boolean



On Error GoTo ErrorHandler

SetFolders

FileName = ReportFolder & "UserResponsibilities.csv"

If FileName = "" Then
ImportUserResponsibilities = False
Exit Function
End If

CurrentDb.Execute "delete from tblUserResponsibilities"
DoEvents


SysCmd acSysCmdSetStatus, "Importing UserResponsibilities..."


DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "UserResponsibilities", "tblUserResponsibilities", FileName, True

CurrentDb.Execute "Update tblReportImportDates set [ImportDate] = NOW() WHERE ReportName = 'UserResponsibilities'"

DoCmd.SetWarnings True

SysCmd acSysCmdClearStatus

ImportUserResponsibilities = True


Exit Function

ExitDoor:
DoCmd.SetWarnings True
SysCmd acSysCmdClearStatus
Exit Function

ErrorHandler:
MsgBox Err.Description, vbCritical

ImportUserResponsibilities = False
Resume
End Function



********************************

The problem comes when I want to make the following line into its on function so I can call it from multiple Import routines for various files:

CurrentDb.Execute "Update tblReportImportDates set [ImportDate] = NOW() WHERE ReportName = 'UserResponsibilities'"


So I created a function and here is the code for both functions:


Public Function ImportUserResponsibilities() As Boolean

On Error GoTo ErrorHandler

SetFolders

FileName = ReportFolder & "UserResponsibilities.csv"

If FileName = "" Then
ImportUserResponsibilities = False
Exit Function
End If

ImportReportName = "UserResponsibilities"

CurrentDb.Execute "delete from tblUserResponsibilities"
DoEvents


SysCmd acSysCmdSetStatus, "Importing UserResponsibilities..."


DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "UserResponsibilities", "tblUserResponsibilities", FileName, True

UpdateImportReportDates (ImportReportName)

DoCmd.SetWarnings True



SysCmd acSysCmdClearStatus

ImportUserResponsibilities = True


Exit Function

ExitDoor:
DoCmd.SetWarnings True
SysCmd acSysCmdClearStatus
Exit Function

ErrorHandler:
MsgBox Err.Description, vbCritical

ImportUserResponsibilities = False
Resume
End Function






Public Function UpdateImportReportDates(ByRef strReportName As String) As Boolean


CurrentDb.Execute "Update tblReportImportDates SET [ImportDate] = NOW() WHERE [ReportName] = " & strReportName

End Function


The problem I am having is the table is not getting the ImportDates correctly updating the report import date.



I have tried ByVal, I have tried:

CurrentDb.Execute "Update tblReportImportDates SET [ImportDate] = NOW() WHERE [ReportName] = strReportName"

and
CurrentDb.Execute "Update tblReportImportDates SET [ImportDate] = NOW() WHERE [ReportName] = " & """strReportName"""




I dont know if the issue is ByVAl vs ByRef, or the fact that I have strReportName as String. I also got Too few parameters error on one of these.



Please advise, any help on this would be great. Thanks

[ylatodd]

Ideally I would like to create a Function to Import csv where I can pass through the FileName, saved Import Spec Name, tableName, SysCmdSetStatus Message and ReportName to update the date of import in tblReporImportDates. Thanks in advance for help.

[ylatodd]

Got an answer to the question. I was missing some ' and " :

CurrentDb.Execute "Update tblReportImportDates SET [ImportDate] = NOW() WHERE [ReportName] = '" & strReportName & "'"

This solved the Too few parameters error.

Any help on the Import function still welcomed. Thanks in advance.

[ssanfu]

Not sure I understand, but here is my best guess.

There are no comments in the code, so this is air code.

The first two lines of every module should be these two lines:

Option Compare Database
Option Explicit

So here is the code... Warning: AIR CODE

Public Function ImportUserResponsibilities() As Boolean
    On Error GoTo ErrorHandler

    Dim FileName As String
    '    Dim ImportReportName As String  'see below ***

    'What is this?
    SetFolders

    FileName = ReportFolder & "UserResponsibilities.csv"

    If FileName = "" Then
        ImportUserResponsibilities = False
        Exit Function
    End If

    'clear table
    CurrentDb.Execute "DELETE * FROM tblUserResponsibilities"
    DoEvents

    'set status bar message
    SysCmd acSysCmdSetStatus, "Importing UserResponsibilities..."


    'set warnings is not needed
    '    DoCmd.SetWarnings False
    DoCmd.TransferText acImportDelim, "UserResponsibilities", "tblUserResponsibilities", FileName, True

    'update date/time of import
    '*** since the report name is hard coded, no need for the variable
    '    ImportReportName = "UserResponsibilities"
    UpdateImportReportDates "UserResponsibilities"

    '    DoCmd.SetWarnings True


    'clear status bar message
    SysCmd acSysCmdClearStatus


    ImportUserResponsibilities = True

    '    Exit Function

ExitDoor:
    '    DoCmd.SetWarnings True
    'clear status bar message
    SysCmd acSysCmdClearStatus
    Exit Function

ErrorHandler:
    MsgBox Err.Description, vbCritical

    ImportUserResponsibilities = False
    Resume ExitDoor
End Function

'------------------------

Public Sub UpdateImportReportDates(strReportName As String)
    CurrentDb.Execute "Update tblReportImportDates SET [ImportDate] = NOW() WHERE [ReportName] = '" & strReportName & "'"
End Sub

[ylatodd]

Hello Steve,

Thanks for the help. I should have showed my comments to make things clearer.

The routine above works, but I am trying to import various files, calling each from different buttons, so I thought the easiest thing to do would be to create an import routine where I can pass some values and use it to import any of the various files I need to import, all import, all using transfertext.

Again I want to be able to pass in various parameters so that I have a function for each of the file types but only one import function.

Ideally I would like to create a Function to Import csv where I can pass through the FileName, saved Import Spec Name, tableName, SysCmdSetStatus Message and ReportName to update the date of import in tblReporImportDates. Thanks in advance for help.

Please let me know if this helps.

[ssanfu]

SetFolders

What does this do? What is the code?

What is "ReportFolder "? It looks like a path to the CSV file?