SubForm Problem

[Terri Hdokins]

I posted a question yesterday in regards to linking 2 subforms. I figured out how to link the two but my second one only displays the first line from subform 1. I need it to display all lines from subform 1 that have the same invoice number.
Any suggestions?????

Thanks,
Terri Hodkins

 

[Carol]

There are a couple of things that could be wrong here. Is the second subform in continous form view or just single form? Are you using the correct field for linking? Is this a one-to-many relationship? Check out these things first. I suspect it could be that you have set your default view to single form instead of continuous.

 

[Terri Hdokins]

I checked my second subform out. It is set to continuous and my links are as follow:
Child:Invoice
Master:subDetailActual!Invoice

What is happening is there are invoice numbers that appear in the first subform. Then in the second subform there could be many records with those invoice numbers. Right now I am only getting the invoices to appear for the first record shown in the first subform.

My goal is to get all the records to show for everything in the first subform.

Thanks Again,
Terri Hodkins

 

[Carol]

If your first subform is displaying multiple invoice numbers, then your form is working correctly. Your second subform is displaying all the records related to the current invoice record selected, which in this case would be the first line on your first subform. To show the next set of records that relate to a separate invoice record, then set your Record Selectors to Yes in the properties of your first subform, which will enable you to click on any invoice record and your second subform will then requery to show the related records as it relates to this invoice.

If you are wanting to show all invoices and subdetails relating to a specific customer, then your Child/Master linking should be set to a CustomerID.

Report back if the above is not a solution.

 

[Terri Hdokins]

When I set my first subform record selectors to yes my second subform does not requery. Do you have any suggestions?

I would really just like all the records selected in the first subform to display on the second subform. The only records that are related to each other are the invoice numbers.

Thanks,
Terri Hodkins

 

[Alexandre]

Hi, Terry.
As I understand it you are in one of the following cases (my guess is the third):

You have a table storing invoices (One side of the relationship) related with another one keeping invoices details or some other related info (many side).

- Case 1
You want to have a form showing ONE invoice number and a subform in it showing ALL the related info.

The approach proposed by Carol is the good one (parent form/subform related through master field(s)/child field). It is generally meant to allow visualization and edition of data.

When you switch from one invoice to another one using the records selector of the first form ,records (details) are automatically updated in the subform. You CANNOT have various invoices on your first form and all the details related to each of them in the subform, since the form/subform is meant to show the results of a ONE (invoice) to MANY (details) relationship (you can eventually show various invoices, but only one is active and determines the children records shown in the subform).

An alternative to the record selector for this would be to make a select box on the first form which would allow the user to choose one invoice and have both forms update accordingly (ACCESS has a wizard to build such a select box)

- Case 2
What you may want to do is perhaps have a list box with invoices on a first form and based on what the user selects (eventually various invoices), show the related details. This is generally meant only of vizualization .

You do not need to link your form/subform for this, but a bit of programming. If this is what you want to do, re-post and i will give you further details.

- Case 3
You have a form, a subform showing various invoices, and you would like a sub-subform to show the related details. This is a bit too much to show and manage at the same time. Consider splitting into separte forms:
A form + subform showing the invoices. Then the user can select an invoice, and click a command button on the subform that opens a new form showing the related invoice details.

Again a bit of programming is needed in this case.


Note: Be carefull with terminology, so that we can understand and help you more easily (ie: what relates to sets of records or two tables are fields (in your case: the invoice number...))

Hope this helps.

 

[Terri Hdokins]

Thanks so much for the feed back. I know I can get it to work by doing Case 3 but I think I am going to try your other suggestions.
I will let you know.
Thanks Again,
Terri Hodkins