Subforms....Advise Please...........

[Guest]

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?

Thanks

 

[Allen Browne]

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.

 

[Guest]

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?

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).

Question 2
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.

Thanks
Dermot

 

[Allen Browne]

Answers embedded in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Guest]

Hi Allen
Thanks for the reply and explanation of the different possibilities to
consider with respect to the related table posibilities.

On this occasion I was referring to you explanation a) below.. and all one
to many.
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.

I do take you suggestion wrt everything looking to cluttered.....and keeping
it simple....Using your example above.....creating the main form bound to the
customers table.....I then placed 3 subforms in the other 3 quarters of the
form and created 3 comand buttons with code on click event to change the
visible property of each form so at any one time only the main form controls
and the controls on each subform would show on click event of the appropiate
command button.....I am not sure I like this.....a bit odd!!!

The other option I considered was to create a Main form with the most used
info subform in it and create two separate data entry forms for the other two
tables acessing all from a switchboard form.....and drop the additional
subforms.

I think from all this ......I have learned that subfroms are not the best
choice for overall data entry...they are more useful for viewing / editing
further details relevant to a main form.

I will take your advise and have a look at Northwind....if you have any
further comments you would care to part with before I move on from this
posting it would be very much appreciated.
Thanks
Dermot

Answers embedded in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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]

You might like to try the tab control, with 4 tabs:
- The first tab page contains the main table's fields;
- Tab pages 2 - 4 contains a subform for a related table.

I use that all the time. The only issue is that the user can't see which
customer this is when they are on a secondary tab page. To solve that, add
some code to the Current event of the form, to place that info in the form's
caption. Something like this:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Caption = "Customer: New Record"
Else
Me.Caption = "Customer: " & Me.[FirstName] & " " & Me.[Surname]
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
Thanks for the reply and explanation of the different possibilities to
consider with respect to the related table posibilities.

On this occasion I was referring to you explanation a) below.. and all one
to many.
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.

I do take you suggestion wrt everything looking to cluttered.....and
keeping
it simple....Using your example above.....creating the main form bound to
the
customers table.....I then placed 3 subforms in the other 3 quarters of
the
form and created 3 comand buttons with code on click event to change the
visible property of each form so at any one time only the main form
controls
and the controls on each subform would show on click event of the
appropiate
command button.....I am not sure I like this.....a bit odd!!!

The other option I considered was to create a Main form with the most used
info subform in it and create two separate data entry forms for the other
two
tables acessing all from a switchboard form.....and drop the additional
subforms.

I think from all this ......I have learned that subfroms are not the best
choice for overall data entry...they are more useful for viewing / editing
further details relevant to a main form.

I will take your advise and have a look at Northwind....if you have any
further comments you would care to part with before I move on from this
posting it would be very much appreciated.
Thanks
Dermot

Answers embedded in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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?

 

[Guest]

Hi Allen

Many thanks for the suggestions. I will try this out.

Kind regards

Dermot

You might like to try the tab control, with 4 tabs:
- The first tab page contains the main table's fields;
- Tab pages 2 - 4 contains a subform for a related table.

I use that all the time. The only issue is that the user can't see which
customer this is when they are on a secondary tab page. To solve that, add
some code to the Current event of the form, to place that info in the form's
caption. Something like this:

Private Sub Form_Current()
If Me.NewRecord Then
Me.Caption = "Customer: New Record"
Else
Me.Caption = "Customer: " & Me.[FirstName] & " " & Me.[Surname]
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
Thanks for the reply and explanation of the different possibilities to
consider with respect to the related table posibilities.

On this occasion I was referring to you explanation a) below.. and all one
to many.
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.

I do take you suggestion wrt everything looking to cluttered.....and
keeping
it simple....Using your example above.....creating the main form bound to
the
customers table.....I then placed 3 subforms in the other 3 quarters of
the
form and created 3 comand buttons with code on click event to change the
visible property of each form so at any one time only the main form
controls
and the controls on each subform would show on click event of the
appropiate
command button.....I am not sure I like this.....a bit odd!!!

The other option I considered was to create a Main form with the most used
info subform in it and create two separate data entry forms for the other
two
tables acessing all from a switchboard form.....and drop the additional
subforms.

I think from all this ......I have learned that subfroms are not the best
choice for overall data entry...they are more useful for viewing / editing
further details relevant to a main form.

I will take your advise and have a look at Northwind....if you have any
further comments you would care to part with before I move on from this
posting it would be very much appreciated.
Thanks
Dermot

Answers embedded in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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?