Replies

Thanks Hans. Unfortuneately, this doesn’t do what I had in mind. I’m so disappointed.

Yes!!! It is done. Attached to real tables, it does more than I was asked to do. Offers selection of accounts, selection of Department, has TWO export buttons, one for All expenses, one for a specific type of expenses.
I earned an ATTAGIRL, and I am happy to share.

Thanks again to Drew, Pat, and all others who helped.

Yes, that does make sense. But again, in the real world it won’t be a problem since the system does not allow an account to be created without being assigned to a Department.

Union Queries — Does that explain them? We-e-e-el, yes– and no. It gives me the basics. I could ask a dozen more questions, but they should probably be on another thread

My mission today is to attach the true tables to this, and add the query (really multiple queries) that actually adds up the transactions and generates a total. I will probably be back.

I noticed that Pat. It’s alright though, because in real life there won’t be a time when there isn’t a range, or at least one account selected.
Thanks

My server still wont allow me to send a file.
What I did, was add Pat’s VBA to export to Excel to the Form Drew provided which selects both Department and Accounts and has the results I needed. It works quite nicely. Now on to the real world, and the extra step.

Many thanks to Pat, Drew, and all others who responded.

Well, of course! Actually it is doing what I want, since I only need those accounts with transactions.

And– I believe this will answer both my questions. I will need another step in here, since this only selects the accounts and I will need some computations before exporting. I think I can do this is another query and just change the name in the code you so obligingly wrote for me. Stop me if I’m wrong.

This is indeed very clever. I have imported your Form and related queries into a Trial DB where I will attempt to link to my “real” tables for accounts. I have a couple of questions first.
There are 310 accounts in the Account table, but when “All” is selected, along with the entire range that is in this table, only 8 rows are displayed. Why? and how do I fix it?

Second — What is a Union Query? and how do I go about making one on my own? I don’t find any mention of it in Access Help.
Working away.

Hi Pat,
I’m answering here the question you asked on my combo box question, because it really relates to this question. After copying your Form (and related queries and tables) into a new database, to try and get your form to combine with the form to choose a dept. — even if I choose an account on the form, when I click the export button, it pops up a box for me to type in the account. Then a second for the last account.
Sigh—-.

I really can’t seem to incorporate these solutions into my working database. I made a new DB for the express purpose of getting them to work together before putting them in my “real” database.
Drew — How do I select a range of accounts in your solution? IE Between this and that? The table I am linked to has literally thousands of accounts, I will never want ‘all’.
Pat — I think your Macro solution (the command box ‘Export to Excel’ will eventually be the answer. Right now, it’s coming up as a dialog box when the query runs.
Take a look.
For some reason, my server here at work won’t let me send a file. Drat!

I just downloaded this. It looks promising. I’m going to try to recreate it in my working database, and combine it with Pat’s solution to my Macro question. If it all works, that should do it. I hope, I hope, I hope.

Yes! I see now how it works. I will build it in my working data base and see how it performs on the real thing.
Now – back to trying to get it to put out all selected accounts when no Department is chosen.
Thank you

Pat,
This is the same form/query that we are working on to get it to show all my accounts when I don’t chose a department. I just thought I’d truck on to the next step while trying to get that right.
The goal here, (per my supervisor) is to have a seamless operation. Choose a requirement (Maybe from a menu?) and Access opens the correct form, runs the correct Query, and exports the results to Excel.
He isn’t unhappy with what I’ve done so far. He just wants this to be usable by TRUE Access novices, who wouldn’t know a Query from a Grocery List.
Yes, you saw that right. Creating a menu – switchboard? – will be my next chore.

Could not have gotten as far as I have without the help of this list.

DoCmd.SetWarnings False
This looks like it will accomplish step one

Step 2 is to Open the Form and wait for input. A button on the form wont really work for this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “Your Query Name”, “Path and Fielname of your Excel
Does this run the Query before exporting?? If not, I need the code to run the query before exporting it

Looks like step one and step four may be solved, now — how do I run this module (Is that the right word?) Since a button on the form wont really do it.

Can’t tell you how much I appreciate your time.

Spreadsheet”, True
DoCmd.SetWarnings True

Well – here is the latest development.
I’m working with the Form that has a query which is referenced by the second box on the form.
I tried tieing my Select accounts query directly to this query rather than to the form.
It returned all accounts selected when no Department was selected. – I was so excited.
Soo – when I selected a Department – It still returned all accounts, with the Department number displayed only in the Department I selected. This is not the result I was expecting.

Well — I’m back to the slog.

OOOPS.
Try again.