Opening tables from a list box

[NSchulman]

I'm trying to create a customer database and am having some trouble.

We have our customers organized by list numbers, and my goal is to use a list box on a form to be able to select the list number that you want, click/double click, and have it open the selected table/query. I am only able to make the entire list box clickable to open a table, not individual records.

For instance, you click on List 75 in the list box, and it opens the query that corresponds to all the people in list 75.

I know how to do this by using simple buttons on a form, but we have too many different list options for this to be a reality. Thank you in advance for any help you all can give me.

 

[Remou]

I suggest you use forms. Continuous forms may be best. A little code will allow you to attach the appropriate query, or more simply, use code to create a Where statement for DoCmd.OpenForm

 

[NSchulman]

So, there is no way for values in a list/combo box to act as "links" to open tables/forms/queries? It seems like this would be an obvious use, but I can't find it anywhere.

 

[Remou]

You can use DoCmd.OpenTable and DoCmd.OpenQuery, but it is _always_ inadvisable to let users at table or queries.

 

[NSchulman]

How do I use DoCmd.OpenTable and DoCmd.OpenQuery,?

And the users opening tables and queries isn't a problem, we're a small company, I'm just trying to get away from using 8000 different excel spreadsheets and know how to do just about everything in access except for this.

 

[NSchulman]

By "Except for this" I mean except for coding and stuff....

 

[bubba100]

What Remou is saying to access the information using a form with controls. Some of these controls are called text, list or combo. It is never a good idea to access the information directly by opening the table.

 

[NSchulman]

I know it's generally a bad idea to give users access to tables, but I'm working for a VERY small business where only 3 of us will be using the file anyway, myself, my marketing director, and the owner, all of us are more than competent users, no worries about someone deleting info they shouldn't.

 

[fneily]

I am only able to make the entire list box clickable to open a table, not individual records". Yeah, how would access know which individual record to show when you're selecting a whole list?
You could cascade listboxes. The user selects which list from the first box, which populates the next listbox with whose on the list. The the user selects an individuals that will bring up the specific info.

 

[NSchulman]

I'm not looking to open an individual record, I'm looking to open an individual query.

Say I have 10 seperate customer lists (it's more like 100, but for arguments sake lets say 10)

I have all the customers in a table, I run 10 queries, 1 for each seperate list, labeled 1-10

I create a form, I put a list box in the form with 3 columns labeled "Number" "Name" and "Count" the info in the list box is all taken from another table I have, a list key.

So I have my list box where I can scroll to all 10 lists. I want to be able to double click on any of these lists and have it open the query I ran. I double click on list 5, it opens query5, I doublt click on list 7, it opens query7.

Simply entering "open queryx" on the double click event means that double clicking anywhere will only open queryx, it won't let me do 10 seperate events, one for each line of the list box.

 

[Remou]

The listbox shows the names of the queries yesno? If so:

DoCmd.OpenQuery Me.lstList