CODEWITHSUNDEEP
ms-accessvbams-access-2013
Andre Chenier
Andre Chenier

Reputation: 1186

passing a value from an MS Access form sub to other form sub

this is my 1st trial with access vba.

in a form, I have 2 buttons. One is for browsing, picking the file and the other is will be to import excel into a table.

I tried code below. It's not working. What I need is to pass the value of filepath variable from cmdBrowse_Click sub to cmdImportFunctions_Click sub.

this is my best after many hours and using call in one sub & byVal in other didn't worked.

thanks in advance, best regards

my questions:

  1. why current code below fails?
  2. how to pass filepath variable from cmdBrowse_Click sub to cmdImportFunctions_Click sub (if possible, I want to learn the without global variable way)

cmdBrowse_Click: to browse and get picked file's path

Private Sub cmdBrowse_Click()

Dim dialog As Object

Dim filePath As String

Set dialog = Application.FileDialog(msoFileDialogFilePicker)

With dialog

    .AllowMultiSelect = False

    .Title = "Please select the functions excel to import"

    .Filters.Clear

    .Filters.Add "Excel Newer", "*.XLSX"

    .Filters.Add "Excel Older", "*.XLS"

    If .Show = True Then

        filePath = .SelectedItems.Item(1)

        txtExcelFile.Value = filePath

        Call cmdImportFunctions_Click(filePath)

    Else

        MsgBox "No file was selected", vbOKOnly

        txtExcelFile.Value = ""

    End If

End With

End Sub

cmdImportFunctions_Click: to import into database

Private Sub cmdImportFunctions_Click(ByVal filePath As String)

MsgBox filePath, vbOKOnly

End Sub

Upvotes: 1

Views: 1844

Answers (1)

Andre
Andre

Reputation: 27634

As the error message says, a On Click event procedure must be declared like this:

Private Sub cmdButton_Click()

without any parameters. You cannot change this declaration (or you will get the compile error).

If you want to initiate the Import directly from cmdBrowse, you don't need the "Import" button. Create a "standard" private procedure in the form module, not an event procedure.

Private Sub DoImport(ByVal filePath As String)
    MsgBox filePath, vbOKOnly
End Sub

and call that in cmdBrowse_Click().

Otherwise, you can store the file path in a module variable, or in a textbox on the form - this would be the usual design if the Browse button only selects the file.

Edit: I see you have that already: txtExcelFile.Value = filePath

I suggest using Me!txtExcelFile.Value = filePath to denote that this is a form control.

Then simply:

Private Sub cmdImportFunctions_Click()
    Dim filePath As String
    filePath = Nz(Me!txtExcelFile.Value, "")

    If filePath = "" Then 
        MsgBox "Please select a file first"
    Else
        MsgBox filePath, vbOKOnly
    End If
End Sub

Upvotes: 1

Related Questions