CODEWITHSUNDEEP
vbaformsms-accessms-access-2013
salisboss
salisboss

Reputation: 139

How do I pass value in textbox on form to subroutine?

I have a simple form with a few combo boxes, a textbox where the user enters the file name they want to save as (will be a spreadsheet), and a few buttons to run a few queries. When a button is pressed it opens the file dialog to select the path but the file name itself is hard coded in the subroutine. How do I take the value in the text box and pass it to the button event routine that runs the appropriate query and exports a spreadsheet? In the routine I called it fileName and the text box on the form is called FileName.

Option Compare Database
Option Explicit

Private Sub AllPaybacks_Click()
Dim getFolder As Object
Dim sLoc As String
Dim fileName As String

Set getFolder = Application.FileDialog(msoFileDialogFolderPicker)
With getFolder
    .AllowMultiSelect = False
    If .Show = True Then
        sLoc = getFolder.SelectedItems(1) & "\"
    End If
End With

DoCmd.OpenQuery "PaybackQ"
DoCmd.TransferSpreadsheet acExport, , "PaybackQ", sLoc & fileName & ".xlsx", True
End Sub

Upvotes: 0

Views: 40

Answers (1)

CraigD
CraigD

Reputation: 111

I'm a little uncertain what your exact question is.

If you want to populate the file name dialog then you can set the InitialFileName property of the getFolder object. So, inside the "with" block and before the "If" statement:

getFolder.InitialFileName = FileName.Value

Upvotes: 1

Related Questions