Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 13th, 2005, 11:50 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dynamic Subform Generation & Population

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.
Reply With Quote
  #2 (permalink)  
Old December 13th, 2005, 11:56 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old December 13th, 2005, 12:04 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old December 13th, 2005, 12:33 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old December 13th, 2005, 01:59 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic PDF Generation sandyg Classic ASP Components 12 June 15th, 2008 03:51 AM
Dynamic ListBox Population n Selection nkrust ASP.NET 2.0 Basics 1 January 9th, 2007 05:05 AM
Dynamic PDF Document Generation nialljpmurphy J2EE 4 March 27th, 2006 07:13 PM
Toolbar switching / dynamic population yamyam .NET Framework 1.x 0 February 14th, 2006 08:15 AM
Dynamic Button Generation LouMattera BOOK: Professional C#, 2nd and 3rd Editions 2 March 4th, 2005 04:26 PM



All times are GMT -4. The time now is 10:14 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.