Too many buttons

[Dani]

I have a database that holds about 3500 records. On the main form, I have several queries for people to run reports and download spreadsheets. My problem is that for every query (we are now up to about 10), I have separate buttons-- one to view the data in access, one to save the data in excel. Obviously, this is really crowding up the form.

I would really, really like to have all the queries there, but to have only one button to view the information in access. I would like the user to be able to select whatever field(s) they are running in the report (these fields are either typed into or chosen by a drop-down box). Ideally, I would like to have only one button to save whichever query they chose to pull up in excel, also, but considering I had to do a macro for each different query to save in excel, I'm not sure if that's possible.

My problem is that I don't know VBA at all. Barely know sql, but otherwise, my access knowledge is pretty complete.

[OTWarrior]

you could use a combo box to select different options, and test the value of the combobox when you press the button in order to select the query to generate.

Another way is to use option groups, but these can take up some space on the screen.

You could even store the SQL for the query in the code, which I have found to be faster in certain scenarios.

to be honest, so long as you have criteria to test for, you can hide quite alot in the background of the form.

[Dani]

I'm not sure what you mean by "test the value". I do have several combo boxes (and test boxes) already to select the value. I just want one "submit" button, ideally, to run all the queries.

I tried to upload a picture of what the database currently looks like, but alas, my post count is not 5 or greater!

[OTWarrior]

What code is behind one of your "submit" buttons?

When I said test the value, I meant testing the value in a combo box: (in peusdo code)

public sub cmdSubmit()
if me.cboList1 = "value 1" then
'run queries or reports here
elseif me.cboList1 = "value 2" then
'run queries or reports here
end if
end sub

using this you could select which query to run without having to duplicate the button. The code above is the most basic version, and you can even make it more dynamic (depending on what you require and what you have on your form).

If you could submit a picture (You may be able to PM me directly, I am unsure) I would be happy to give you a head start :)

[Dani]

Ok, I was able to submit a pic via pm, so be on the look out for it... thanks!

[OTWarrior]

Here is an example of one solution to your problem.

Enjoy :)