By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

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

100+
P: 121
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.
Apr 10 '07 #1
Share this Question
Share on Google+
4 Replies


pks00
Expert 100+
P: 280
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
Apr 12 '07 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Apr 13 '07 #3

100+
P: 121
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
Apr 14 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.