passing a value from an MS Access form sub to other form sub
<p>this is my 1st trial with access vba.</p>

<p>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.</p>

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

<p>this is my best after many hours and using call in one sub &amp; byVal in other didn't worked.</p>

<p>thanks in advance, best regards</p>

<p>my questions:</p>

<ol>
<li>why current code below fails?</li>
<li>how to pass <code>filepath</code> variable <strong>from</strong> <code>cmdBrowse_Click</code> sub <strong>to</strong> <code>cmdImportFunctions_Click</code> sub (<em>if possible</em>, I want to learn the <strong>without global variable way</strong>)</li>
</ol>

<p><strong>cmdBrowse_Click: to browse and get picked file's path</strong></p>

<pre><code>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
</code></pre>

<p><strong>cmdImportFunctions_Click: to import into database</strong></p>

<pre><code>Private Sub cmdImportFunctions_Click(ByVal filePath As String)

MsgBox filePath, vbOKOnly

End Sub
</code></pre>