Using a pop up calendar in Access to create a Dim Value withn a Macro

Question

I'm really new to VBA and have spent a bit of time this week trying to figure out a macro that will automatically import several files each day into a self created banking database.

Much of the macro is working but the date on the files each day will change and I wanted to figure out a way to have a pop up calendar ask for a date prior to importing the files.

As of now Ive got the macro working with a hard coded value but I need to change it by hand each day.

Ive created a pop up calendar form but cant figure out how to have the result of the selection create a dim value to be used elsewhere in the macro

Sample without the calendar is below

Function Copy_Of_Import_Macro_TEST()

    On Error GoTo Copy_Of_Import_Macro_TEST_Err

    Dim bankDt As String
    bankDt = "20150818" 'August 18, 2015 formatted as YYYYMMDD
    'insert this into string including quote marks  " & bankDt & "

    Dim filePath As String
    filePath = "C:\myFolder\"

        DoCmd.TransferText acImportDelim, "Import_spec", "BankFile", "" & filePath & "" & bankDt & "filename.csv", True, ""

    Copy_Of_Import_Macro_TEST_Exit:
        Exit Function

    Copy_Of_Import_Macro_TEST_Err:
        MsgBox Error.Description
        Resume Copy_Of_Import_Macro_TEST_Exit

End Function

Answer 1

I've created the mock-up form shown in the screenshot below, which has a text field called txtBankDate and a command button called cmdExportCsv.

In Design View for the form, I've setup txtBankDate so that it is in the "short date" format...

...doing so means that when the form is switched to Form View, clicking in to txtBankDate gives you the option to use the in-built date picker:

Selecting a date with the date picker will put that date in to the text box:

Your function though, will need to be able to take the date that's in txtBankDate when it is called and then change it from "dd/mm/yyyy" format to "yyyymmdd" as per what you want for your filenames. This means your function needs the following:

1. An argument that will allow you to reference your text box because you need to be able to use that data in your function (think of this as a little funnel to get necessary data in to your function when it is called)
2. Once you have set up the funnel (argument) to allow data from your text box in to your function you will need your function to format the date to "yyyymmdd" instead of "dd/mm/yyyy"; this is done using the format() function.

Below I have re-written your function so that it does both of these things:

Function Copy_Of_Import_Macro_TEST(bankDate As Variant)

    On Error GoTo Copy_Of_Import_Macro_TEST_Err

    Dim filePath As String
    Dim fmtBankDate As String

    filePath = "C:\myFolder\"
    fmtBankDate = Format(bankDate, "yyyymmdd")

    DoCmd.TransferText acImportDelim, "Import_spec", "BankFile", "" & filePath & "" & fmtBankDate & " - filename.csv", True, ""


Copy_Of_Import_Macro_TEST_Exit:
        Exit Function

Copy_Of_Import_Macro_TEST_Err:
        MsgBox Err.Description
        Resume Copy_Of_Import_Macro_TEST_Exit

End Function

Here's some notes on what we're setting up in the above function:

The function above simply constructs a passage for the data before data is actually passed through it; it still has to be set in motion by calling the function -- and -- as we've declared an argument (bankDate), we must say what data will be passed through in to the function in the space where that argument resides. In our case, the data is going to whatever date is currently in txtBankDate.

So the function needs to be called like so:

    Copy_Of_Import_Macro_TEST Me.txtBankDate

So to illustrate what is the function and what is being passed in as the bankDate argument for that function:

You then need to decide what event you will run this function call from (something needs to trigger this call). In my mock-up I created a command button and ran the above function call on that command button's on-click event:

This is done by going in to Design View of your form, selecting the command button, going to the Event tab of the property sheet, clicking the [...] button of the On Click event and then selecting "Code Builder" to open the VBA editor.

Private Sub cmdExportCsv_Click()

    Copy_Of_Import_Macro_TEST Me.txtBankDate

End Sub

The order of what will happen is:

1. You'll pick a date using the calendar picker

2. The date will go in to txtBankDate text box

3. You'll click the export to csv command button

4. This will trigger any code on the button's on-click event, which is:

   Copy_Of_Import_Macro_TEST Me.txtBankDate

5. The function Copy_Of_Import_Macro_TEST is called with data from txtBankDate funneled-in as an argument.

6. The function will re-format the data funneled-in from txtBankDate so that it reads "yyyymmdd" rather than "dd/mm/yyyy"

7. This re-formatted date will then be added to the filename argument of the DoCmd.TransferText method so that your filename reads something like this:

C:\myFolder\20150821 - filename.csv

Hope this explains it better.

Other Comments

I've changed your MsgBox Error$ line to MsgBox Err.Description as I think Error$ has been deprecated.

Strictly speaking Copy_Of_Import_Macro_TEST should really be a subroutine rather than a function (functions are expected to return a value whereas subroutines do not).

You may also want to consider some validation on the txtBankDate field that first checks whether a date has been entered before running the function, otherwise the filename won't show a date.