Tabbed form - "Cannot open any more tables" error

[D-Angle]

I have a tabbed form in my database, with 21 tabs. Each tab has its own subform. When I open the form I get the error "Cannot open any more tables" several times until it opens, and when it does the subforms on tabs 1-14 are loaded, but the subforms on 15-21 are not.

Each of the subforms is of identical design, apart from referencing different fields in the database. Is there a workaround or will I need to split it into 2 separate tabbed forms?

 

[Ranman256]

There's a memory limit.
You don't need that many tables open. Users can only work on 1 table.
Use a single form,rotate out the tables.

 

[D-Angle]

Not an option, the data being captured has had to be spread across 3 tables, Access wouldn't let me save it all in a single table as there were too many fields (63 questions, with 3 answers for each, plus a comments box for each question, plus the option to attach documents as evidence for each question)

Is it possible to close and open a subform within a form, so I could just have one subform open at a time?

 

[Minty]

Set your tabs up so that the sub forms have no sourceobject and therefore are empty on load except the first one.
Use the OnChange event of the tab control to load the subform on demand for each page.

It sounds like you have dumped an excel sheet into access without normalising your data, your 63 questions should be 63 rows in a questions table, not 63 fields.

 

[D-Angle]

Set your tabs up so that the sub forms have no sourceobject and therefore are empty on load except the first one.
Use the OnChange event of the tab control to load the subform on demand for each page.

Thanks, I'll try that.

It sounds like you have dumped an excel sheet into access without normalising your data, your 63 questions should be 63 rows in a questions table, not 63 fields.

You're not far off. The information is currently held in an excel spreadsheet and I have been asked to move it to an Access format. The problem is the 63 questions need to be asked of about 15 different teams, and each question will have 3 answers, a self-assessment answer, a peer review answer, and a 6 month review answer. It also needs to have the facility to add extra teams later on, so it's very hard to come up with a single table that will do that. I have had to create tables with a row for each team, then columns for the scores, comments, evidence etc.

I'll try what you suggest though, thank you.

 

[D-Angle]

Took a little bit of work to figure out the coding, but it works beautifully now, it loads each subform as the tab is clicked, closes the one that was open before it, and no more errors. Runs faster too.

Thanks for your help guys.

 

[Minty]

You're not far off. The information is currently held in an excel spreadsheet and I have been asked to move it to an Access format. The problem is the 63 questions need to be asked of about 15 different teams, and each question will have 3 answers, a self-assessment answer, a peer review answer, and a 6 month review answer. It also needs to have the facility to add extra teams later on, so it's very hard to come up with a single table that will do that. I have had to create tables with a row for each team, then columns for the scores, comments, evidence etc.

I'll try what you suggest though, thank you.

Glad you got it working!

Don't try and get it into one table, that's not normally how to store the information. Each entity in your data should probably be in a separate table. You then create joining tables to set up the usage.

So you would have a teams table. And probably an answers table, linked to a table containing question ID, the team that participated, who answered, and a date. This is a vertical structure that means you can add as many teams , questions , and answers as required.