Auto input date for macros

[ph00086]

Hi,

I'm using 2010 and have a macro that runs three reports (just in the basic macro builder), when i run my macro it asks me to input the date i want teh reports to display for, however it asks me to this three times for each one.

is there any code/way around this so i only have to input the date once??

Thanks

 

[Trevor G]

Re: Auto inout date for macros

Have you thought of using a form for the date and then run the macro using the form textbox as the criteria that would work for the 3 reports if they use the criteria of the textbox.

Have you created the reports from Queries? Then the form idea would work, as you set the criteria for each query to look at the form and the text field.

 

[ph00086]

Re: Auto inout date for macros

i'll try that, how do i change the criteria of the query to use the txtbox??

Thanks

 

[Trevor G]

Re: Auto inout date for macros

Have the form open first which will have the textbox on it and adjust the textbox from the properties naming it something like txtReportDate and then in the format change it to a DateFormat you are using and save the form. Keep the form open and then go into your first query, in the criteria for the dateField click in the criteria which should be empty at this point, then look for the Build Wizard Icon and click on the left double click the forms option and then loaded forms, in the centre look for the textbox and double click it, click on OK from the Build Wizard and then save your query, repeat this for the other queries.

 

[ph00086]

Re: Auto inout date for macros

Thanks, that worked a treat.

 

[Trevor G]

Re: Auto inout date for macros

Happy to help and thanks for letting me know you have a working solution.

 

[wood4623]

I'm using Access 2003 and need to export a table to a .csv file with today's date as the last part of the file name in mmddyy format. My macro exports a table to the csv file; how do I include today's date in the file name?

 

[Trevor G]

Convert the macro into VBA. So create a new form in design view then press F11 this will display your database window, look on the left for the macro and drag it into the form, this will create a button.

Next go to the Tools Menu and search down the list for Macro then extend the list and select Convert Form Macros To VBA then click it. You will get a message saying it will add comments and error handler accept this and click OK.

Next Click the button and open the properties (right mouse button) then in the properties select the Event Tab in the list go down to On Click and this will show you Event Procedure and click the button with 3 stops on it and it will take you into the VBA screen.

In the code you will see where it is showing you the file name and what you do is add the following after the file name but before the .CSV

" & Format(Date,"mmddyy") & ".CSV"

Then save and close the VBA screen down then change the view of the form and click the button and it should work.

 

[wood4623]

Thanks Trevor; I got an error message "Cannot Update - Database or Object is Read Only"...

Must have done something wrong.

I'm using an Export function in my macro...

 

[Trevor G]

Do you mean you are using TransferSpreadsheet an in the arguments below you have selected Export?

If you can't convert the macro then you will need to look at creating a VBA Module. Please try this. Use Alt + F11 on the keyboard this will take you into VBA, then select the Insert Menu and select Module you then need to write the code

Sub trans()
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "TABLE NAME", "Full Path\FileName" & Format(Date, "MMDDYY") & ".CSV", True

End Sub

Then look to save what you have done, stay in the code and look to run it (Press F5) that should output the file. Check the folder location to see if it has worked.

 

[wood4623]

TransferType: Export Delimited

 

[Trevor G]

If it is going into a CSV file then the TransferSpreadsheet will work as well, otherwise you need to add the specification name if you have created one.

 

[wood4623]

What is "acSpreadsheetTypeExcel9" for? I'm exporting an Access table to a .csv file; it must be named "SDN_mmddyy.csv" - my macro is using the Import/Export Text function and the Transfer Type is Export Delimited. I hope this helps...

 

[wood4623]

BTW, I'm getting a Run-time error '3274': External table is not in the expected format. error when I run your last suggestion.

here is what I'm trying to run:

Sub trans()
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "DailySilverPopEnrollmentFile", "O:\File Location Files\FILENAME_" & Format(Date, "MMDDYY") & ".CSV", True
End Sub

Actual names are omitted...

 

[wood4623]

Trevor, I'll be signing off in a few minutes, and will start back up in the morning... thanks for your patience....

 

[Trevor G]

I've been off site. Have you managed to get this working?

 

[wood4623]

Thanks for asking; not yet. We're still working on it as time permits.

Thanks again.

 

[Trevor G]

Can you do this for me so I can take a look.

Create a new temp database copy over your table and take out all the data. Copy over the MACRO and then upload to the Forum and I will take a look for you and convert the macro to VBA and add the date etc.

 

[wood4623]

Thanks for the offer; we're still trying different options. If we keep running into a brick wall, I'll get back to you.