How to filter records in an append query by choosing field in a form

[JHNielson]

I have a function that copies records from a table called ETL to a table called EXPORT then it exports the records in EXPORT as an excel.

The record are all marked as coming from a set of data owners.

What I'm trying to do now, is to filter the records that get copied into the EXPORT table by having the users choose which "DATA_OWNER" they want to export the records for. I would like to have them choose on a form which data owner they want and then have a button on the form which then copies the records to the export table (filtered by the selected data owner) and then exports the spreadsheet.

I can get through the whole process, except for how to get the users to select the field and thenuse that to filter the records taht get copied.

Any help would be greatly appreciated.

[pks00]

What is the code you have now that copies from ETL to EXPORT
Is it just a append query?

If so then u could add filters to that
by doing the copying via code
or creating a temp query and using that

but I need to know what u currently do to perform the copy from ETL to EXPORT

[nico5038]

You can create an unbound form (not linked to a table) with a combobox showing the distinct DATA_OWNER's. Name this form frmSelectDataOwner and the combobox cmbDataOwner.

Just use as source for the combobox:
select distinct DATA_OWNER from EXPORT

Next create a query named "qryDataOwner" referring to the combobox on the form as a criteria for the DATA_OWNER like:
forms!frmSelectDataOwner!cmbDataOwner
This query will only work when the form is active and a DataOwner has been chosen.

Now add a button to trigger the export to excel with the command:
docmd.transferspreadsheet acExport, "qryDataOwner",....

In the additional parameters the excel version and the target file are set.
When creating the filename just add the combobox value to get an excel sheet "named" like:
"C:\Datasheet_" & me.cmbDataOwner & ".xls"

Getting the idea ?

Nic;o)

[JHNielson]

What is the code you have now that copies from ETL to EXPORT
Is it just a append query?

If so then u could add filters to that
by doing the copying via code
or creating a temp query and using that

but I need to know what u currently do to perform the copy from ETL to EXPORT

The code is just a simple append query - the two tables are nearly identical

[NeoPa]

I have a function that copies records from a table called ETL to a table called EXPORT then it exports the records in EXPORT as an excel.

The record are all marked as coming from a set of data owners.

What I'm trying to do now, is to filter the records that get copied into the EXPORT table by having the users choose which "DATA_OWNER" they want to export the records for. I would like to have them choose on a form which data owner they want and then have a button on the form which then copies the records to the export table (filtered by the selected data owner) and then exports the spreadsheet.

I can get through the whole process, except for how to get the users to select the field and thenuse that to filter the records taht get copied.

Any help would be greatly appreciated.

In the code behind a button on your form...

1. Get the SQL out of your current APPEND query.
2. Update your SQL to include a WHERE clause designed to select the item(s) from your Owners list (ComboBox or Multi-Select ListBox depending on requirements).
3. Execute the new updated SQL (DoCmd.RunSQL ...).
4. Execute the TransferSpreadsheet command as before.