G
Guest
Greetings, thank you all for looking. Here is the situation...I have a
database that works with transactions. Each transaction is assigned a group
and a catagory and is contained in a single table.
There are 9 groups and 10 catagories. What I need to do is export the
transactions to Excel based upon the group and catagory. Each Group needs to
be a Workbook, each catagory needs to be a worksheet in each workbook. So
the total will be 9 Workbooks each containg 10 worksheets.
I can currently accomplish this, but it is very awkward. I have created 10
queries, that pull information from the table with the transactions. As you
can guess the catagory criteria is static in each query. The group criteria
is based upon a drop down object on a form.
The user selects the group from the drop down menu and then needs to click a
button that corresponds to the drop down choice. A macro runs that uses the
transferspreadsheet action 10 times to export the 10 catagories to a workbook
titled with the group they have selected.
As you can see this works but it is very limiting. I had to create 10
catagory queries, 9 command buttons, 10 macros with 9 lines in each, and a
drop down menu with a query to populate it. Also if they select a command
button that does not corespond to the drop down they selected, it will export
data that doesn't match the workbook name.
My goal is to have the user be able to select a group from a drop down and
click one button and it will export the group they have selected. Also, as I
have recently found out, these group names change. This is what is driving
the change.
I am finding myself struggling with the best wat to go about this. Any
ideas or suggestions would be welcome. I am by no means an Access programmer
so simple is the preferred method. I just want to avoid manually creating 90
queries. Thank you in advance for any suggestions.
database that works with transactions. Each transaction is assigned a group
and a catagory and is contained in a single table.
There are 9 groups and 10 catagories. What I need to do is export the
transactions to Excel based upon the group and catagory. Each Group needs to
be a Workbook, each catagory needs to be a worksheet in each workbook. So
the total will be 9 Workbooks each containg 10 worksheets.
I can currently accomplish this, but it is very awkward. I have created 10
queries, that pull information from the table with the transactions. As you
can guess the catagory criteria is static in each query. The group criteria
is based upon a drop down object on a form.
The user selects the group from the drop down menu and then needs to click a
button that corresponds to the drop down choice. A macro runs that uses the
transferspreadsheet action 10 times to export the 10 catagories to a workbook
titled with the group they have selected.
As you can see this works but it is very limiting. I had to create 10
catagory queries, 9 command buttons, 10 macros with 9 lines in each, and a
drop down menu with a query to populate it. Also if they select a command
button that does not corespond to the drop down they selected, it will export
data that doesn't match the workbook name.
My goal is to have the user be able to select a group from a drop down and
click one button and it will export the group they have selected. Also, as I
have recently found out, these group names change. This is what is driving
the change.
I am finding myself struggling with the best wat to go about this. Any
ideas or suggestions would be welcome. I am by no means an Access programmer
so simple is the preferred method. I just want to avoid manually creating 90
queries. Thank you in advance for any suggestions.