Cant open any more tables....

[rmacleod]

I am getting this error on a Database that I am creating. I have found that it has to do with the Jet Database.

It seems to be happening because of one form that I am working on.

In this form I have about 40 List Boxs that display Information

I have a combo box that after updating changes the Rowsource for each List Box depending on the value in the Combo Box.

I am fairly new to Access programming and am not sure if there is a betterway of doing this. Each List boxes just returns one numeric value.

I am taking that all these Listboxs are using up table references and causing the cant open any more tables error.

 

[KenHigg]

Based on:

'Each List boxes just returns one numeric value.'

I would say you do need to consider your overall design. Could you elaborate on your tables and what your db actually does?

 

[The_Doc_Man]

I'm not sure about the numbers this one, but I'll tell you where you can look.

Each list box pulling data from a table is an implied query, which is also an implied OPEN of the underlying table.

Look in Help for the topic Specifications or maybe it is Limits. Which one it is depends on your version of Access, they changed the Help files between A97 and A2K. One of the sub-pages under the appropriate heading will tell you the maximum number of simultaneously open tables or queries. That is the limit you ran into.

My advise is to simplify your page if you can. But if you can't, then you will have a serious problem.

 

[rmacleod]

the database is a Repair Tracking system for pagers. what I have been attempting to do is create a form that the Managers can look at at any time to see totals of pager repaired for different categories.

all of the List boxes reference the same Query. the rowsource of each list box changes depending on what customer is chosen.


an example is like this

Customer XYZ is Chosen.

the List boxes are then loaded with the rowsource specified for that customer. which displays information such as

total number of Pagers repaired
total number of Numeric Pagers
total number of alpha numeric pagers
total number of Refurbs
total number of Un repairable

etc etc.

the Error does not come up right away. it comes up after working with the form for a short period of time.

 

[KenHigg]

Hum... I'm not sure why you are using the list boxes, could you just use text box controls?

Also, couldn't this information be represented with a form based on a query that totals/counts the info?

??

 

[rmacleod]

as I said..I am Rather new...and if there is a better/easier way of doing it, i would like to know.

correct me if I am wrong ...but to use Text boxes...I would have to create all my query for each total that I need..then I would have to create one master query that contains all the other queries. and then assign that query to the form.

By using a list box and assigning a Query to its rowsource...I can use an unbound form...and I dont have Save a ton of queries. the Query is saved in the Code that is used to dynamically assign the rowsource.

 

[KenHigg]

Well, there are numerous ways to do this. The first thing I would do is seperate the columns that need to be counted form the ones that need to be summed. Then you can do one query to run the sums. Next, I would do a seprate query for ea count. Then, like you said, tie it all together with master query that the form is base on. Other options includes domain functions like dlookup and dcount. They would work too...

If I pondered for a while I could probably come up with a slicker, faster solution. I would be intersted to see how Mile-0-phile would handle this...

 

[The_Doc_Man]

Sounds to me like you need to read the following Help topics carefully:

sub-forms
totals queries
parent/child links in sub-forms

You can have an unbound text box if you wish and just fill it dynamically, but from your discussion, that might be a bit advanced yet.

Here is how you might wish to do this...

Make a summation query that takes the totals and counts of the fields you want. You can have sums and counts in the same summation query, no problemo. Build a combo box to capture your customer identification. Use this customer ID as the parent and have the totals query include this customer ID.

When you link the sub-form to the parent form, if you use the sub-form wizard to do it, it will ask you the names of the fields in the parent and child form that are related to each other. Specify the customer ID. The wizard should build the appropriate event code for you.

You might also search this forum for articles that mention combo boxes and sub-forms in the same article.