getting data from a main form to records in a subform

[Joel]

TIA:
I have an form with two tabs. One tab is a main form and the other tab is a
related subform. Data is entered into the main form. Some of this data
needs to be copied to one or two records in the related subform.

I would like to have a command button on the main form that would switch to
the subform, start a new record and copy data to the record and if
([field]=not null) start another new record and copy data from the main form
to it. There only would be one or two records in the subform.

I know how to create the command button, capture the data on the main form
in code, but looking for help on the code to switch from the main form to the
subform and start a new record.

Thanks again,
Joel

 

[Mike Painter]

Why. Good relational design makes every effort to avoid such duplication.

If you have to do this, will any additional information be added to the
related records?
What determines if there is one or two records.

In any event you would create an append query and run it from the main form.
You could do this by addig the whole query to an event on the button or
with "Forms!YourFormName!YourFieldName" in the query.

 

[Joel]

Let me be more specific:

I have a main form "addresses" and a related subform "Members_subform" .
They are setup on separate tabs. The "Members_subform" shows continous forms.

The main form has several fields such as "lastname" and "spousename" that
needs to be copied to a new record(s) in the subform. If a field
"spousename" exists, a second new record needs to be added and "spousename"
copied from the main form to the second record in Members_subform.

so far:

Private sub copy_data()
dim ln as var
me.lastname=ln
me.Members_subform.setfocus
' what next??
end sub

I create a main record.

I run the code and notice that I have a cursor in the new record of the
subform.

But not sure what code should follow to be able to refer to a control in the
new record of the subform

and if "spousename" on the main form is not null then
need to start a new record
set "spousename" in new record of Members_subform

Thanks for your help.
Joel

 

[Joel]

Thanks Mike:

I completely agree with "Good relational design makes every effort to avoid
such duplication".

Here's the bottom line. Senior Citizens.

Senior citizens enter data from one sheet of paper into the main form in the
access database. Some of the data needs to be added to a subform for
relational purposes. I have added another post with a better explanation of
the issue.

Let me know if you have additional comments and thank you.

Joel

Why. Good relational design makes every effort to avoid such duplication.

If you have to do this, will any additional information be added to the
related records?
What determines if there is one or two records.

In any event you would create an append query and run it from the main form.
You could do this by addig the whole query to an event on the button or
with "Forms!YourFormName!YourFieldName" in the query.

TIA:
I have an form with two tabs. One tab is a main form and the other
tab is a related subform. Data is entered into the main form. Some
of this data needs to be copied to one or two records in the related
subform.

I would like to have a command button on the main form that would
switch to the subform, start a new record and copy data to the record
and if ([field]=not null) start another new record and copy data from
the main form to it. There only would be one or two records in the
subform.

I know how to create the command button, capture the data on the main
form in code, but looking for help on the code to switch from the
main form to the subform and start a new record.

Thanks again,
Joel

 

[Joel]

Hello Mike:
another comment, i don't think your suggestion would work in the event there
needs to be 2 separate records in teh subform as explained in my additional
post.

Thanks !!!

Why. Good relational design makes every effort to avoid such duplication.

If you have to do this, will any additional information be added to the
related records?
What determines if there is one or two records.

In any event you would create an append query and run it from the main form.
You could do this by addig the whole query to an event on the button or
with "Forms!YourFormName!YourFieldName" in the query.

TIA:
I have an form with two tabs. One tab is a main form and the other
tab is a related subform. Data is entered into the main form. Some
of this data needs to be copied to one or two records in the related
subform.

I would like to have a command button on the main form that would
switch to the subform, start a new record and copy data to the record
and if ([field]=not null) start another new record and copy data from
the main form to it. There only would be one or two records in the
subform.

I know how to create the command button, capture the data on the main
form in code, but looking for help on the code to switch from the
main form to the subform and start a new record.

Thanks again,
Joel

 

[Mike Painter]

Thanks Mike:

I completely agree with "Good relational design makes every effort to
avoid such duplication".

Here's the bottom line. Senior Citizens.

Senior citizens enter data from one sheet of paper into the main form
in the access database. Some of the data needs to be added to a
subform for relational purposes. I have added another post with a
better explanation of the issue.

Let me know if you have additional comments and thank you.

None. There is no need to not use a form and subform and as soon as they
hit one more than you allow for on your main form it will be back to the
drawing board.

Senior citizens follow normal curves and I've worked with any number of
people who have always believed that the monitor is the computer.
Even if the paper form they use does not look like the computer form most
will quickly learn that you fill in the spouses name in this little box
here.

 

[Joel]

Hello Mike:

Thanks for your comments:

I'm just trying to accomodate the request of a Senior Citizens group with an
existing database.

There is a main form where one person or a person and spouse information
(and other info) is entered. There is a subform where personal information
for one or both of these records is created and data is added in the subform
record(s) subsequently.

I'm just trying to automate the process of creating the subform records as
I've described.

I know it's not ideal but it is what I have to work with.

If you or anyone else wants to help solve the problem as it is I sure
appreciate it.

Thanks.

Joel

 

[Joel]

Mike:

Here's an approach I've taken and maybe you can help from here

I have a button on the main form that opens the related subform.
If there is no related record I want to start a new record and set one of
the fields to a variable that has been captured from the main form.

dim var as variant
var=[control on mainform]
var1=[control1 on mainform]
DoCmd.OpenForm "subform", acNormal, , "[FamilyID] = " & [FamilyID]
' how to test if new record in subform and if true then
[control on subform] = var

also if var1=not null then
'how to start a new record in subform and set var2
[control on subform]= var2 ' this would be the second related record.

Thanks for your help.
Joel
Joel

 

[Mike Painter]

You will want to open the recordset based on family ID and see if there
are any records then use an append query to add them as needed before
opening up the subform.
Make sure you use NZ rather than check for Null.
This will only work properly the first time around.
If they push the button and then fill in the fields it will not work.

If a button opens a sub form and they make a change without opening the
subform you will have different information in two tables.
You will have to account for the possibility that they will open the sub
form to make the change, add a second name at a later time, or just to see
the additional information.
New duplicate records will be added each time.

I think this is very bad design and even if they mandated this approach, it
will be your fault and they will go back to Word, Excel, or a paper system
in very short order.

Mike:

Here's an approach I've taken and maybe you can help from here

I have a button on the main form that opens the related subform.
If there is no related record I want to start a new record and set
one of the fields to a variable that has been captured from the main
form.

dim var as variant
var=[control on mainform]
var1=[control1 on mainform]
DoCmd.OpenForm "subform", acNormal, , "[FamilyID] = " & [FamilyID]
' how to test if new record in subform and if true then
[control on subform] = var

also if var1=not null then
'how to start a new record in subform and set var2
[control on subform]= var2 ' this would be the second related
record.

Thanks for your help.
Joel
Joel

None. There is no need to not use a form and subform and as soon
as they hit one more than you allow for on your main form it will be
back to the drawing board.

Senior citizens follow normal curves and I've worked with any number
of people who have always believed that the monitor is the computer.
Even if the paper form they use does not look like the computer
form most will quickly learn that you fill in the spouses name in
this little box here.

 

[Joel]

thanks for your advice.

Joel

You will want to open the recordset based on family ID and see if there
are any records then use an append query to add them as needed before
opening up the subform.
Make sure you use NZ rather than check for Null.
This will only work properly the first time around.
If they push the button and then fill in the fields it will not work.

If a button opens a sub form and they make a change without opening the
subform you will have different information in two tables.
You will have to account for the possibility that they will open the sub
form to make the change, add a second name at a later time, or just to see
the additional information.
New duplicate records will be added each time.

I think this is very bad design and even if they mandated this approach, it
will be your fault and they will go back to Word, Excel, or a paper system
in very short order.

Mike:

Here's an approach I've taken and maybe you can help from here

I have a button on the main form that opens the related subform.
If there is no related record I want to start a new record and set
one of the fields to a variable that has been captured from the main
form.

dim var as variant
var=[control on mainform]
var1=[control1 on mainform]
DoCmd.OpenForm "subform", acNormal, , "[FamilyID] = " & [FamilyID]
' how to test if new record in subform and if true then
[control on subform] = var

also if var1=not null then
'how to start a new record in subform and set var2
[control on subform]= var2 ' this would be the second related
record.

Thanks for your help.
Joel
Joel

Joel wrote:
Thanks Mike:

I completely agree with "Good relational design makes every effort
to avoid such duplication".

Here's the bottom line. Senior Citizens.

Senior citizens enter data from one sheet of paper into the main
form in the access database. Some of the data needs to be added to
a subform for relational purposes. I have added another post with a
better explanation of the issue.

Let me know if you have additional comments and thank you.

None. There is no need to not use a form and subform and as soon
as they hit one more than you allow for on your main form it will be
back to the drawing board.

Senior citizens follow normal curves and I've worked with any number
of people who have always believed that the monitor is the computer.
Even if the paper form they use does not look like the computer
form most will quickly learn that you fill in the spouses name in
this little box here.