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

Question

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

Gustav · Accepted Answer · 2018-11-08 16:33:14Z

2

You can use InputBox:

SomeStringVariable = InputBox("Please enter value:")

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

DoCmd.SetParameter method (Access)

edited Nov 8, 2018 at 16:33

answered Nov 8, 2018 at 16:18

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SadMrFrown Over a year ago

I know this is a way to do it but if the user has to enter this already then they are entering the same input twice. When the query runs it prompts the user for the variables q_month and q_year, having inputboxes would require them to type the same thing twice, no?

Gustav Over a year ago

Ah, right. There is a way to set the parameters (from the InputBoxes) before the query will be opened by DoCmd.OutputTo and then it will not ask for them again - but I can't recall how. Will try to find out.

SadMrFrown Over a year ago

Thanks @Gustav, that would be exactly what I want!

Gustav Over a year ago

Found it. I've included the link above.

Darren Bartrup-Cook · Accepted Answer · 2018-11-08 16:46:28Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related