CODEWITHSUNDEEP
vbams-access
SadMrFrown
SadMrFrown

Reputation: 157

Getting the user input of a variable as a string to use in vba access

I'm sure there is a really easy way around this. Say I have a query called query_1 and upon running this query the user has to input the two values which are labelled as q_month, q_year.

I am running a bit of code that exports this query, but I want to take the user input values as strings which I can then use further down the line in my code. How would one do this?

(Apologies I am new to syntax in Access)

See below my attempt (I open the query first as it then will prompt user to input value). I know the lines v_Month and v_year are incorrect but hopefully it shows what I want to do clearer.

Thanks!

Function ExportExcel()
Dim myQueryName As String, sFolderPath As String, v_Month As String, v_Year As String

myQueryName = "query_1"
sFolderPath = "C:\Folder1"



DoCmd.OpenQuery myQueryName
v_Month = [query_1].[q_month]
v_Year = [query_1].[q_year]
myExportFileNameExcel = sFolderPath & "\" & v_Month & "\Test.xlsx"
DoCmd.OutputTo acOutputQuery, myQueryName, "ExcelWorkbook(*.xlsx)", myExportFileNameExcel, False, "", , acExportQualityPrint


End Function

Upvotes: 0

Views: 4091

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

You haven't given the SQL for the query so I wrote a basic query showing how to use parameters: 

PARAMETERS  q_month Long, q_year Long;
SELECT      *
FROM        Table1
WHERE       YEAR(DateField) = q_year AND MONTH(DateField) = q_month

You can then use this code to export the query data to Excel: 

Sub Test()

    Dim MonthNumber As Long, YearNumber As Long

    'Get the details from the user.
    MonthNumber = InputBox("Enter month number:")
    YearNumber = InputBox("Enter full year:")

    'Pass the details to the Export procedure.
    ExportToExcel MonthNumber, YearNumber

End Sub

Public Function ExportToExcel(lMonth As Long, lYear As Long)

    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    Dim oXL As Object, oWB As Object, oWS As Object

    'Open the query as a recordset.
    Set qdf = CurrentDb.QueryDefs("Query1")
    With qdf
        .Parameters("q_Month") = lMonth
        .Parameters("q_Year") = lYear
        Set rst = .OpenRecordset
    End With

    Set oXL = CreateXL 'Create an instance of Excel.
    Set oWB = oXL.WorkBooks.Add 'Create workbook.
    Set oWS = oWB.Worksheets(1) 'Reference to first sheet.

    'Copy the data over to row 2.
    oWS.Range("A2").CopyFromRecordset rst

    'Add the field headings to row 1
    For Each fld In rst.Fields
        oWS.cells(1, fld.OrdinalPosition + 1) = fld.Name
    Next fld

    'Using the passed values again.
    MsgBox "Data exported for " & Format(DateSerial(lYear, lMonth, 1), "mmmm 'yy")

    'Assumes the month folder already exists.
    'Names folders as "01_January_18" to "12_December_18"
    oWB.SaveAs "C:\Folder1\" & Format(DateSerial(lYear, lMonth, 1), "mm_mmmm_yy") & "\Test.xlsx", 51

    rst.Close
    qdf.Close

    Set rst = Nothing
    Set qdf = Nothing

End Function

Public Function CreateXL(Optional bVisible As Boolean = True) As Object

    Dim oTmpXL As Object

    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Defer error trapping in case Excel is not running. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set oTmpXL = GetObject(, "Excel.Application")

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If an error occurs then create an instance of Excel. '
    'Reinstate error handling.                            '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Err.Number <> 0 Then
        Err.Clear
        Set oTmpXL = CreateObject("Excel.Application")
    End If

    oTmpXL.Visible = bVisible
    Set CreateXL = oTmpXL

End Function

Upvotes: 0

Gustav
Gustav

Reputation: 56016

You can use InputBox:

SomeStringVariable = InputBox("Please enter value:")

To set the parameters before running the query, use DoCmd.SetParameter:

DoCmd.SetParameter method (Access)

Upvotes: 2

Related Questions