Re: Subforms....Advise Please...........

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Answers embedded in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dermot" <Dermot@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EDBBF960-928F-49C7-8285-2324772982A7@xxxxxxxxxxxxxxxx
Hi Allen

Thanks for the clear explanation.

To Quote You:
The normal usage is 2 tables with a one-to-many relation.

Situation
If I have say 4 tables and they all relate to each other with One-to-Many
relationships.
If I want to enter data into all tables using one main form based on the
main table.

Question 1
Can I place the other 3 tables on the form as subforms to enter the data?

How do these 4 tables relate to each other?
a) One table has 3 related tables, e.g.:
Customer => Order
Customer => Payment
Customer => Address
In this case, you could bind the main form to the Customer table, and use 3 subforms for the 3 related tables.

b) Each table has a related table of its own, e.g.:
Customer => Order
Order => OrderDetail
OrderDetail => ItemsSent
In this case it is probably best not to try to use one form to handle it all. You might get away iwth Orders as the main form, with 2 subforms - one for the OrderDetails and the other for the ItemsSent. There's an example of how to do that in Northwind - the Customer Orders form from memory.

It would possible to place that whole thing on the Customers form, but I think that's getting messy.

Question 2
What property would I set to make the subform frames merge with the main
form to give the appearance that all controls belong to the main form (ie
hide the subform frame).

Assuming structure (b) above, see the Northwind form for how to set the LinkMasterFields. There are actually 2 ways to do this. The other way is a hidden text box on the main form with properties:
Control Source =[OrderDetailSubform].[Form].[OrderDetailID]
Name txtOrderDetailID
so you can use txtOrderDetailID in LinkMasterFields for the ItemsSent subform.

Question 3
I would like on this occasion to use the above method but.......
What would be considered the "best practice" method of entering the data
into the 4 table in this hypothetical example.

Best practice is subjective. You have several goals. After reliability (it works without fail), simplicitiy and comprehendability for the end user are high priorities. That means keeping the interface as uncluttered as possible, and I suspect trying to handle all 4 levels in a single form will be too much.

If you really need to handle them all in one form, a tab control might save your bacon. The first tab could handle all the customer details, and the 2nd tab could be filled with the form constructed above (as a subform.) Can't say I really recommend that, but I can't see your data.

Thanks
Dermot

"Allen Browne" wrote:

It depends how the data is connected.

The normal usage is 2 tables with a one-to-many relation, such as Invoice
(the one side of the relation) and InvoiceDetail (the many side, i.e. the
line items for the invoice.) The Invoice table has InvoiceID as primary key.
The InvoiceDetail table has an InvoiceID as foreign key (i.e. you created
the relation from Invoice.InvoiceID to InvoiceDetail.InvoiceID.)

Then when you create the main form bound to Invoice, and the subform bound
to InvoiceDetail, Access will look at the matching fields, discover the
relation, and in most cases it will fill in the LinkMasterFields and
LinkChildFields for you.

If it fails to recognise the match, or if it gets the match wrong, you can
set them yourself in form design view. If you leave it blank, the subform
will show *all* rows from InvoiceDetail, instead of just the rows for the
invoice in the main form.

IME, Access gets it right most of the time for numeric fields with defined
relations, but often doesn't figure it out for text based key fields, or if
there are multiple relations between the tables to choose from.

"Dermot" <Dermot@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5BE6AD83-DBCA-4881-A9BF-DABEBA2AB382@xxxxxxxxxxxxxxxx
>I want to understand how to create a subform on a main form and in the
> process observe it's linking properties to the main form....please
> advise....
>
> If I create a from in design view.
> Then drag a form from the databse window onto the detail section to > create
> a
> subform.....then open the subform properties (subfrom selected)......
>
> The Source Object property shows the name of the subform.
>
> Question 1
> The link Field property is blank.......should it not refer to the > linking
> fields in the subform?
>
> Question2
> The Master Link Field is blank also.....should it refer to theLinking
> Fields
> in the Main form?
>
> Can anyone explain to me what I have overlooked and let me know what I
> should expect here?

.

Relevant Pages

  • Re: Subforms....Advise Please...........
    ... Customer => Payment ... visible property of each form so at any one time only the main form controls ... info subform in it and create two separate data entry forms for the other two ... The normal usage is 2 tables with a one-to-many relation, such as Invoice ...
    (microsoft.public.access.forms)
  • Re: Subforms....Advise Please...........
    ... customer this is when they are on a secondary tab page. ... info subform in it and create two separate data entry forms for the other ... line items for the invoice.) ...
    (microsoft.public.access.forms)
  • Re: Subforms....Advise Please...........
    ... You might like to try the tab control, ... The only issue is that the user can't see which customer this is when they are on a secondary tab page. ... info subform in it and create two separate data entry forms for the other two ... >> The normal usage is 2 tables with a one-to-many relation, such as>> Invoice ...
    (microsoft.public.access.forms)
  • Re: Closing an Empty Form
    ... What I was attemtping to do with the subform was extract the open invoices ... then calculate and display the original invoice amount, ... current balance due for each open invoice other than through the unweildy ... Using a subsubform was the only way I could see doing this while maintaining ...
    (microsoft.public.access.queries)
  • Re: Product in main form into subform
    ... employee to Group By instead of first. ... The problem is I want to summarize the invoice file(from ... InvoicePrint query) and also a DenverTax table. ... The subform is bound to a table called DenverTax. ...
    (microsoft.public.access.formscoding)