Results 1 to 2 of 2
  1. #1
    draganmpls is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Posts
    1

    Updating table value based on user input and creating save as file name from the same input

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put the strPayDate OUTSIDE of the quotes

    strSQL = "UPDATE tblMain_output SET tblMain_output.[Pay Date] = " & strPayDate



    JUST FYI: all this could be done with query and macro, zero lines of code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A query based on user input in form
    By drhassan in forum Queries
    Replies: 17
    Last Post: 01-20-2014, 06:00 PM
  2. SELECT Records based on user input
    By Mattrob in forum Queries
    Replies: 1
    Last Post: 10-10-2013, 12:28 AM
  3. Criteria based on user input
    By Alsail77 in forum Queries
    Replies: 5
    Last Post: 08-16-2012, 02:19 PM
  4. How do you select a field based on user input?
    By technesurge in forum Queries
    Replies: 5
    Last Post: 06-20-2012, 02:04 PM
  5. User Input/Updating Tables Help
    By hawkins in forum Access
    Replies: 3
    Last Post: 06-07-2011, 04:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums