drop-down menus on forms

Question

There is a switchboard form I am using to bring up all the stats we regularly use.  I would like to create a button that gives a drop down list of tables or queries, so I can group a bunch of "open" commands into one button, instead of half dozen buttons on the switchboard for that type of stat.  How can I create a drop down to choose from multiple tables to open?  Call the tables A, B, C and D.  I know you have to use VBA, but I don't know that yet.

Answer 1

I am a total newbie to VBA and I can't find what you are talking about. 

... 

I want to click a button on my switchboard form, view a drop-down list of available tables/queries, decide which one I want and click the choice to open the table or query.

 

What do I do to be able to do that?

Yes, you can do that. But John has a valid point. My question to you is whether anyone else uses this application except you. If it is only for your own use, the prohibition against direct access to tables is not as imperative. You know your data so you are less likely to mess it up. However, if you are building this app for others, then you should be opening forms, not tables. 

What, I'm concerned with here is your first sentence. You don't need ANY VBA to set up a combobox. There is a Wizard that walks you through the process. You simple select the Combobox control on your ribbon and draw the box on your form and let the Wizard guide you. Unless you plan on adding more tables to your app, you can select the option to type in the values.

VBA enters into things to make the combo launch the table. To do this you would use the After Update event of the combo. Select the Code builder and enter one line of code:

DoCmd.OpenTable Me.comboboxname

You need to substitute the actual name of the combobox control.

Answer 2

Well, I'd like to "unask" the question!

It's VERY rarely a good idea to have end users opening table - or even query - datasheets. Table datasheets both give the user too much power (deleting data, even changing the structure of the table) and too little (table datasheets are very limited compared to forms for most useful things). If your application design depends on having the users open table datasheets it needs to be reconsidered! It would be MUCH better to open a properly designed Form or Report to display the "stats", whatever they are.

That said - a "Dropdown box" is a tool called a "Combo Box" in Access. You can open your switchboard form in design view and use the Toolbox to create a new Combo Box on the form. The "Row Source" property of this combo box could be something like

"Table A";"Table B";"Table C";"Table D"

and the RowSourceType property would be set to "Value List". You would then indeed need just a little easy VBA or a Macro in the combo box's AfterUpdate event to open the table. I almost hate suggesting this because you can very easily get yourself painted into a corner if you try to develop an application without a better understanding of what you're getting into!!

Answer 3

The topic of your post is "drop-down menus on forms" so that's what I am assuming: you are designing a form and would like to know how to pick from a list of table names and query names.

If the above is totally foreign to you, you may need a bit more foundation in Access development before proceeding. There are plenty of tutorials on the web, including this one: https://www.youtube.com/user/LearnAccessByCrystal

Answer 4

I am a total newbie to VBA and I can't find what you are talking about. 

Where do I use a regular drop-down?  The only place I know of is by creating a table.  In the properties there, I found everything you listed above, but that only gave me a new table with a drop-down choice in the record field.  When I chose a table, I got a new record with the name of the table.  It was in no way associated with the actual table that I can see.

I want to click a button on my switchboard form, view a drop-down list of available tables/queries, decide which one I want and click the choice to open the table or query.

What do I do to be able to do that?

Answer 5

Use a regular dropdown, set the RowSourceType to "Value List" and in the RowSource property enter your values like this:

"table 1";"table 2";"query 1"