Form with multiple attached items

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main form in which client details are recorded, and I would like to
have a subform attached to that which records each product line. So for
example, I would fill out my client details and then within the subform I
would select/type in details for each product I required - save and close.

The problem I am having is linking the main form and subform together so
that the "master" details (client details entered initially) recognise each
of those subform entries and being children of the parent entity. It seems
Access only wants to associate the first product line (if at all) with those
master details. I realise I need to in some way link the child and master
fields however I don't have anything to link them to each other. To compound
matters, I need to clear out this particular table each time it is closed and
transfer the "completed" record to another table (this I can do myself).

I did have a solution to this before when I was working on a previous
database however I no longer have access to those records and so I can't
check what I did...! Can anyone help?
 
I have a main form in which client
details are recorded, and I would like
to have a subform attached to that
which records each product line. So for
example, I would fill out my client details
and then within the subform I would
select/type in details for each product
I required - save and close.
Click to expand...

In database work, Forms are not the key item, it is the data, and the Tables
(and Relationships between Tables).
The problem I am having is linking the main
form and subform together so that the "master"
details (client details entered initially) recognise
each of those subform entries and being children
of the parent entity.
Click to expand...

Assuming your Client table has a one-to-many Relationship with a
ClientsProductLines table, the ClientsProductLinesTable would have a
"foreign key" (key of the associated Client Record). You would specify the
key (say, ClientID) of the Client Record as the LinkMasterFields property of
the Subform, and the foreign key (which might have the same name if that is
your convention... I often use a slightly different name for these) of the
ClientProductLines table as the LinkChildFields property of the Subform.
It seems Access only wants to associate
the first product line (if at all) with those
master details. I realise I need to in some
way link the child and master fields however
I don't have anything to link them to each
other. To compound matters, I need to
clear out this particular table each time it
is closed and transfer the "completed"
record to another table (this I can do myself).
Click to expand...

Almost certainly, a properly designed database should not require this step.
If you can clarify a bit what data you have, and how you have it stored,
perhaps someone can make better suggestions.

Larry Linson
Microsoft Access MVP
 
Larry Linson said:
In database work, Forms are not the key item, it is the data, and the Tables
(and Relationships between Tables).


Assuming your Client table has a one-to-many Relationship with a
ClientsProductLines table, the ClientsProductLinesTable would have a
"foreign key" (key of the associated Client Record). You would specify the
key (say, ClientID) of the Client Record as the LinkMasterFields property of
the Subform, and the foreign key (which might have the same name if that is
your convention... I often use a slightly different name for these) of the
ClientProductLines table as the LinkChildFields property of the Subform.


Almost certainly, a properly designed database should not require this step.
If you can clarify a bit what data you have, and how you have it stored,
perhaps someone can make better suggestions.

Larry Linson
Microsoft Access MVP
Click to expand...

Hi Larry,

Thanks for the response. What I am looking to do is to create a master form
where certain details can be added/selected (I have coding to do this
already). Once those details have been selected the user may then
select/enter a number of products into a subform or the form itself (doesn't
matter which way). Those details would then be entered into a "master" table
which compiles all of that data for later recollection. There are underlying
tables for the client details, but none yet for the products. Products are
not specific to a client, but I can if need be place the CltID field in the
products area per line and hide it, if that makes things easier. Indeed I
had done that, but when the table was populated, client details only went
into the first line.

Is there a simple way to achieve what I am trying to do? I would think
there was but I can't seem to find it. If anyone could point me at a decent
tutorial I would be most grateful!
 
Back
Top