I'm new to VBA but what I am trying to do in Access is
1. For user to enter a value such as date and that same value to be used to update all rows in the existing table.
2. Export that table using query and have file name contain value user entered (strPayDate)
Here is what I have for input the value and update. It is prompting user to enter value twice and I am not sure how to solve it. Thank you for your help!
Dim strPayDate As String
Dim strSQL As String
strPayDate = InputBox("Enter Date as MM/DD/YYYY", "Date")
strSQL = "UPDATE tblMain_output SET tblMain_output.[Pay Date] = strPayDate"
DoCmd.RunSQL strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qseltblMain_output_export", "I DON'T KNOW HOW TO SET FILE NAME BASED ON USER INPUT.xls", True