OK, I've got myself a pretty tough problem that I'm not sure is able to be done in Access, so I thought I'd ask here before spending a few hours coding something that might be impossible.

I have a number of part records, approximately 230k, and each of these part records is used in one or more systems, and within that system is used in one or more cabinets. Each part record contains one system, and one cabinet within that system.

I.E.
Part No | System | Cabinet
000124 | SysA | CabA
000124 | SysA | CabB
000124 | SysB | CabC

What I want to do is create a subform that will have a separate record for each system, and on the form it displays for each system, dynamically create a series of checkboxes for each cabinet within that system. Here's a rough picture of what the overall form will look like:

__________________________
|Part Information |
|____________________[u][<|>]</u>|
|System_Name |
|o CabA o CabC |
|o CabB o CabD |
|____________________[u][<|>]</u>|

The top nav buttons change the parts which will then refresh the system query, the bottom nav buttons scroll through the systems the part is used in.

If the dynamic form creation is not possible, any pointers on the best way to apply the information to a static version of the same would be appreciated. I'm thinking it will require VB and RecordSets but I may be wrong.

Kindler,

Definitely possible. What's in your main form? Do you have a table with the part number and all possible systems and cabs, whether they are used there or not?

Kevin

dartcoach

The tables weren't designed by someone with DB experience so what I've been given to work with is one huge table (230k rows, 97 columns). So far I've written a couple queries just for planning purposes, one retrieves the system names (9 at the moment, a 10th is slated for Q2 '06 hence my desire for dynamic generation), another retrieves all the cabinets used within each system (there's 21 cabs, 105 sys:cab records). Each part record contains one instance of system/cab that it is used in, plus the common part info (lots of fields, none relevant to this task). Right now the query to pull up all the info sorts by the common fields.

Kindler,
What you might try is creating a static form that has:

System 1
 Cab 1: Cab 2: Cab 3: Cab 4: Cab 5: Cab 6: Cab 7:
 Cab 8:
  etc:

System 2
same

Then use recordsets to load each. If you add a system it would be a small amount of work to add it to the static form and change the code to allow for it.

I've got to go to work now, but if I come up with a better idea, I'll get back to you later.

Kevin

dartcoach

Well, what I've done so far is create a static form for each system listing the cabinets in it. Those were then placed on the main form as subforms in a tab control. Now I just need to figure out a way to get the data into them.

I'm curious though, right now it's displaying 9 tabs which is a bit cumbersome since most parts aren't in more than 2 systems. Is there a way I could have it only display the subforms relevant to each part? Doesn't have to use tabs, if there's a way to just set up something almost like a recordset of forms that would work even better.

I.e. Part 001 is used in SysA, SysB, and SysE. Instead of displaying an empty Cab form for SysC and SysD, the navigate buttons scroll from FormA->FormB->FormE, where each Form displays a static list of cabinets and checks the ones it is used in.