Exporting a table using a variable file name

[tallman21]

Apologies if this is a duplicate thread, didn't come across the answer after searching.

As part of the database closing process, I'd like to add in code that automatically exports my main data table (tbl_aggregated ata) as an Excel file onto the users C: drive. My hope is that I could have the file name use the current date so that a series of saved data points could exist should the user do something to the data in the database. My goal would be to have the file save as something like 112006databkup. xls. Is this possible as an event to be run from a command button?

Thanks

[NeoPa]

I believe so.

You can also trigger it with the OnClose of your main form if you prefer.
Look in help for 'DoCmd.Transfer Spreadsheet' for how to export your data.

[MMcCarthy]

Apologies if this is a duplicate thread, didn't come across the answer after searching.

As part of the database closing process, I'd like to add in code that automatically exports my main data table (tbl_aggregated ata) as an Excel file onto the users C: drive. My hope is that I could have the file name use the current date so that a series of saved data points could exist should the user do something to the data in the database. My goal would be to have the file save as something like 112006databkup. xls. Is this possible as an event to be run from a command button?

Thanks

Okay there are a couple of issues here. The more complicated one first.

In Access there is no way to automatically trigger an event on application close. However, there is a workaround on this. Create an unbound form lets call it frmStartup. Set this as the startup form rather than your current form (e.g. Switchboard). Set the properties of this form to invisible so the user never sees it.

Now you have to code three form events.

1. The On Load event

 Private sub Form_Load() 
 
  ' open the normal startup form
  DoCmd.OpenForm "Switchboard"
 
End Sub

2. The On Unload event

 Private sub Form_UnLoad(Cancel As Integer) 
Dim rslt As Integer
 
  'prompt the user that the application is closing
  rslt = Msgbox("The database is closing down", vbOKCancel)
  If rslt = vbCancel Then
	Cancel = True
  Else
	' code to export main table
	 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "tbl_aggregatedata", & _
	"C:\" & Month(Date) & Year(Date) & "databkup.xls", True
  End If
 
End Sub

3. Form On Close event (in case form is accidently closed)

 Private Sub Form_Close(Cancel As Integer) 
 
  Form_UnLoad
 
End Sub

[NeoPa]

Nice answer.
You also have the option of cancelling the close if you prefer by setting

Cancel = True

[MMcCarthy]

Nice answer.
You also have the option of cancelling the close if you prefer by setting

Cancel = True

Actually that is a better idea.

tallman21

replace my line in the On Close event

Form_Unload

with Adrian's

 Cancel=True

[tallman21]

Thank you, thank you. A thousand times thank you. That worked like a charm and does exactly what I need.

[MMcCarthy]

Thank you, thank you. A thousand times thank you. That worked like a charm and does exactly what I need.

That's great.

I'm glad we could help.

Mary