Date Parameter on export

[Guest]

I am using the below code to export table data from a button on a form to
excell:

Private Sub cmdExport_Click()
On Error GoTo Do_Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"ExpenseActt", txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Vendor",
txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"VendorInv", txtExportFile

MsgBox "The tables have been successfully exported to " & txtExportFile
& "."

Exit Sub

Do_Nothing:
MsgBox "Export has failed. An error occurred or the user terminated the
operation."

End Sub

Private Sub cmdExport_Enter()

End Sub

Private Sub txtExportFile_BeforeUpdate(Cancel As Integer)

End Sub
It works well on exporting but is there a way to place a parameter of date
that would prompt a user to enter begin date -end date so the whole data base
file is not exported?

 

[Allen Browne]

How about adding a text box to this form.
Then refer to it in the Criteria of the query, e.g.
[Forms].[Form1].[txtEndDate]

An alternative approach is to change the SQL property of the query before
export, e.g.:
CurrentDb.QueryDefs("Query1").SQL = "SELECT ...

 

[Guest]

Thanks Allen but i am at a loss on how to go about this since this is the
first time experimenting with code. I am at a average level and can create
edits forms and some small event procedure.Could you provide a few more steps
or direction?

How about adding a text box to this form.
Then refer to it in the Criteria of the query, e.g.
[Forms].[Form1].[txtEndDate]

An alternative approach is to change the SQL property of the query before
export, e.g.:
CurrentDb.QueryDefs("Query1").SQL = "SELECT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using the below code to export table data from a button on a form to
excell:

Private Sub cmdExport_Click()
On Error GoTo Do_Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"ExpenseActt", txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Vendor",
txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"VendorInv", txtExportFile

MsgBox "The tables have been successfully exported to " & txtExportFile
& "."

Exit Sub

Do_Nothing:
MsgBox "Export has failed. An error occurred or the user terminated
the
operation."

End Sub

Private Sub cmdExport_Enter()

End Sub

Private Sub txtExportFile_BeforeUpdate(Cancel As Integer)

End Sub
It works well on exporting but is there a way to place a parameter of date
that would prompt a user to enter begin date -end date so the whole data
base
file is not exported?

 

[Allen Browne]

The first suggestion needs almost no changes to the code.

1. Add a text box to your form, and set these properties:
Name txtStartDate
Format General Date

2. Add another text box, with properties:
Name txtEndDate
Format General Date

3. Create a query using the ExpenseActt table.

4. In query design, in the Criteria row under the date field, enter this
(it's one line):
Between [Forms].[frmExport].[txtStartDate] And
[Forms].[frmExport].[txtEnd Date]

5. (Optional.) Choose Parameters on the Query menu.
Enter 2 rows in the dialog, like this:
[Forms].[frmExport].[txtStartDate] Date/Time
[Forms].[frmExport].[txtEndDate] Date/Time
(Note: Use the name of your own form instead of frmExport at steps 4 and 5.)

6. Save the query with a name such as qryExpenseActt.

7. Change the line that exports the table so that it exports the query
instead:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, _
"qryExpenseActt", txtExportFile

The export query will then read the dates from the text boxes on your form.

Do the same for your other exports.

It is possible to do it with parameters in the query that do not refer to
text boxes on your form. However, you would then need to enter the dates
again and again for each export. By setting up the queries so they read the
dates from the form, you only have to enter them once.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen but i am at a loss on how to go about this since this is the
first time experimenting with code. I am at a average level and can create
edits forms and some small event procedure.Could you provide a few more
steps
or direction?

How about adding a text box to this form.
Then refer to it in the Criteria of the query, e.g.
[Forms].[Form1].[txtEndDate]

An alternative approach is to change the SQL property of the query before
export, e.g.:
CurrentDb.QueryDefs("Query1").SQL = "SELECT ...

I am using the below code to export table data from a button on a form
to
excell:

Private Sub cmdExport_Click()
On Error GoTo Do_Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"ExpenseActt", txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"Vendor",
txtExportFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"VendorInv", txtExportFile

MsgBox "The tables have been successfully exported to " &
txtExportFile
& "."

Exit Sub

Do_Nothing:
MsgBox "Export has failed. An error occurred or the user terminated
the
operation."

End Sub

Private Sub cmdExport_Enter()

End Sub

Private Sub txtExportFile_BeforeUpdate(Cancel As Integer)

End Sub
It works well on exporting but is there a way to place a parameter of
date
that would prompt a user to enter begin date -end date so the whole
data
base
file is not exported?