Exporting data to Excel using a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS Access 2K, Windows XP
====================
Hi,

I have a form to search for data (specifying date range, keywords etc.) and
the results are displayed in a list on the form.

I also have a button to export the results to Excel, using a query that has
"[Forms]![frmName]![controlName]" specified in the criteria, so that it will
export results that match the search criteria specified on the form. I'm
using DoCmd.TransferSpreadSheet command to transfer data.

Now, I have to modify the form so that a user can select displayed records
from the list and export only those records that have been selected in the
list.

I'm at somewhat of a loss on how to approach this, and will appreciate any
pointers/ideas. One approach that I can think of is to create a temp table
containing the IDs for the records selected from the list, and add another
criteria to the query for the ID field - something like "select * from
tmpTable".

If you know of a better idea to accomplish this, please let me know.

Thanks!

-Amit
 
There are a number of ways this can be done. The easiest is to add a Yes/No
field to the table. Allow the users to select the records they want to
export using the checkbox. The use a query for the TransferSpreadsheet that
filters out unchecked records. When that is complete, run an update query
that unchecks the field.

If you are not able to add the field to the table (sometimes we don't have
total control of our data), then you could create a temp table with the
Yes/No field, copy the regular table data into in, use the same approach as
above, and delete the data out of the table when you are done. The down side
to this is it is a performance hit because you have to take the time to copy
the data from one table to another. The degradation will depend on how large
the table is.
 
Klatuu said:
There are a number of ways this can be done. The easiest is to add a Yes/No
field to the table. Allow the users to select the records they want to
export using the checkbox. The use a query for the TransferSpreadsheet that
filters out unchecked records. When that is complete, run an update query
that unchecks the field.
Click to expand...

Hi Klatuu,

Thanks for your response! That's a neat idea and I do have control over
table design.

But, I have a couple of follow-up questions/comments.

1. If I'm using a list to display the records, obviously I can't include the
checkbox in the list. Is that correct?

2. That means display the results using a (sub)form. And, I've done that
before. But, when I display data (result of search) in a form, I make the
form read-only to avoid anyone making any inadvertent changes to the data.
So, in that situation, adding a check-box won't work as the user won't be
able to check-uncheck it.

I guess I could make the form editable, and lock all controls except the
check-box....

Thanks.

-Amit
 
Oh, you did say list! Sorry, that did not translate in my 4K brain to List
Box. You observation is correct regarding the List Box. It can be done, but
would take some coding to accomplish. Not hard, but my reservation is how
many rows are in the table and how many might the user select. I actually do
something very similar to this. What I am doing is showing a list of all
possible values in a field using a Totals query based on the field in the
table. The technique I use is to loop throught the ItemsSelected collection
and build an IN statement that I then include in an SQL statment.

If your table is not too large, you could take a similar approeach. I am
still apprehensive; however. I think in your case, using a sub form would be
better. I would set the Enabled property to No and the Locked property to
Yes for all fields except the check box.

OH! I just thought of a combination approach, but it may not be good from a
performance perspective. You could still use the check box in the table and
the list box. Then write a function that would loop through the
ItemsSelected collection, and programmatically check selected records.

Just a thought. My original programming instructor always demanded 3
solutions for every problem :)
 
Klatuu said:
Oh, you did say list! Sorry, that did not translate in my 4K brain to List
Box. You observation is correct regarding the List Box. It can be done, but
would take some coding to accomplish. Not hard, but my reservation is how
many rows are in the table and how many might the user select. I actually do
something very similar to this. What I am doing is showing a list of all
possible values in a field using a Totals query based on the field in the
table. The technique I use is to loop throught the ItemsSelected collection
and build an IN statement that I then include in an SQL statment.

If your table is not too large, you could take a similar approeach. I am
still apprehensive; however. I think in your case, using a sub form would be
better. I would set the Enabled property to No and the Locked property to
Yes for all fields except the check box.

OH! I just thought of a combination approach, but it may not be good from a
performance perspective. You could still use the check box in the table and
the list box. Then write a function that would loop through the
ItemsSelected collection, and programmatically check selected records.

Just a thought. My original programming instructor always demanded 3
solutions for every problem :)
Click to expand...

Hi Klaatu,

Hopefully you'll see this.

I tried your suggestion of including a Yes/No field in the table, and then
including that field in the subform. The problem I'm running into is that the
record source for the subform is a query that combines some other queries and
tables, including one where data from multiple rows is combined into a single
row. (e.g. (1,A), (1,B), (2,A), (2,C) is changed to (1, "A, B"), (1, "A, C").
Net result being that the subform is not updateable.

So, I guess I'll have to go with a list, and insert the selected record ID
in a temp table, and then include that in the query using IN. The number of
records is in the range of 20-80 (could/will grow), so creating a temp table
and adding IDs to it is the best way to go, as SQL string has an upper limit,
I think.

Too bad the subform idea didn't work, as I really do like displaying data in
a subform rather than in a list.

Thanks for your ideas.

Cheers,

-Amit
 
Back
Top