How to create a subform from a subform 1

[DrButterfingers]

I have 3 or 4 tables with a one to many relationship and I want to show the 3 child tables on the same parent form. The thing is that the information in one subform would be dependant on information in the parent subform.

Here is an outline of the tables shown in their parent-child hierarchy

Contracts 1-->8 orders 1-->8 lot numbers 1-->8 transactions

It is easy to set up a subform of orders based on the contracts that they relate to, but is it possible to set up another subform for lot numbers table where the lot numbers to be shown are dependant on the orders in the orders sub-form?

 

[docmeizie]

You can only nest up to twice in Access. Goes like: Main --> sub --> sub.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[docmeizie]

Lot Numbers can have many transactions??? Hmmm. but the answer is yes to your question you can get that Lot Numbers in the Orders as a subform but you won't be able to get Transactions in there. But that will be the limit, UNLESSSSSS, you want to make Contracts one form and have a button that opens up your Main/sub/sub form, linked together by a common long integer of course!

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[DrButterfingers]

Each parent can have an unlimited number of children...but on average there are about 30 transactions for each lot number.

Moving the transactions to a different page isn't a big deal since they usually show detail that most users don't need to see.

So the main-->sub-->sub should be ok. Then I will just open a new form for transactions.

How do I make one subform dependant on the other?

 

[docmeizie]

When you add a form to another form it becomes a sub to that master form. What has to happen to link them properly is you have to define which field links them together. That is usually a record ID number or some unique identifying number that is in both tables. Like with my databases, I have Accounts, Account Details, and Payments. Payments and Account Details are linked to Accounts by an Account Number that is created in form Accounts anytime a new record is created. The subforms automatically get passed the Account Number because that is what they are linked on. You can edit the link properties of a subform when you first create one(using the wizard), or by clicking on the subform once and going to its data properties and editting the child and master link fields.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[DrButterfingers]

But in this situation the main form would be contracts and the first sub form would be orders. I can link those together no problem on a primary or secondary key.

The problem is that the lot numbers will be dependant on the order that you are currently viewing. So in contract 1001 there will be two orders. Each order will have different lot numbers and I only want to see each lot number that applies to the specific order.

So I can't simply link the lot numbers with the contracts table because it will then show all the lot numbers that exist for all orders.

I couldn't find a way to link the lot numbers sub-form to keys in the orders table...can it be done, or do I still need to explain the situation a little bit more?

 

[docmeizie]

Ahhh that's why that seemed so odd. Okay then what you want to do is have the subforms nested once like I with my Accounts. The two subforms will be on a form (Orders), but they will link something like this: Lot Numbers(Sub) <-- Contracts(Main) --> Orders(Sub). This way each subform gets to link to the main. And yet they are still independant of each other.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[DrButterfingers]

If I understand your solution correctly I don't understand how I will link lot numbers subform to the orders subform on the main contracts form

If I link the subforms in the manner that you suggest will it not show me all the orders in a specific contract and all the lot numbers in a specific contract?

I only want to show the specific lot numbers for a specific order in a specific contract.

 

[docmeizie]

Okay let me get this straight then:

Each Contract can have many Orders;
Each Order can have many Lot Numbers;
Each Lot Number can have many Transactions.

Now if this is the case you should go with the Contracts(mainform) --> Orders(nested within Contracts) --> Lot Numbers(nested within Orders)

If this case is true:

Each Contract can have many Orders;
Each Contract can have many Lot Numbers;
Each Lot Number can have many Transactions.

then go with this; Orders(nested within Contracts) <-- Contracts(mainform) --> Lot Numbers(nested within Contracts) --> Transactions(nested within Lot Numbers0.

The latter of the two gives an example of how to fit all 4 tables into a cohesive sub/master/sub/sub config. I hope this helps a little more, let me know if you have any more questions, I am here to help.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[DrButterfingers]

the first scenerio is correct:

Each Contract can have many Orders;
Each Order can have many Lot Numbers;
Each Lot Number can have many Transactions.

Now if this is the case you should go with the Contracts(mainform) --> Orders(nested within Contracts) --> Lot Numbers(nested within Orders)

How do I do that!

 

[docmeizie]

Before you do anything else, make sure that your table relationships convey what you are going to do in the steps below. That means that you should have tables Contracts with a many side link on table Orders and Orders with a many side link on table Lot Numbers. This will help when they ask for a link condition between the forms. Access will automatically recognize it and you can just moveon to the next step.

Create a form with the data source being that of your table Contracts. Add the fields to the form that you want to convey information about the contract. Next go to your toolbox and find the subform/subreport tool. Make sure the little wand is selected at the top of the toolbox. Click subform/subreport tool. Click a spot on the form where you want the subform to go. The Wizard should initiate and take you through the steps of creating the subform. When it asks you what fields you want on the subform you can select the table(Orders) out of the drop down box and go from there. After you get done with that, put the subform in design mode. Make some space at the bottom of that subform. Go back to the toolbox and create another subform on that subform you just made. This subform with be tied to table Lot Numbers.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[docmeizie]

Contracts Orders
ContractID (Primary Key) ---> ContractNumber (Foreign Key)

Orders Lot Numbers
OrderId (Primary Key) ------> OrderNumber (Foreign Key)

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[DrButterfingers]

Yup!

That got it. Thanks so much for your help...

but on an interesting side note I tried to nest the 4th table (transactions) and it worked!

So right now I have it nested 3 subforms (two of which are nested inside other subforms)

Again,

Thanks for your help

 

[docmeizie]

Hmmm. I tried that method with the exact same relationship and it told me you could only nest twice. Hmmm, interesting. No problem

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv