|
|
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
|
|
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 wrote:
[Quoted Text] > 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
|
|
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" wrote:
[Quoted Text] > 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
>
>
>
>
|
|
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
"Mike Painter" wrote:
[Quoted Text] > 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 wrote:
> > 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
>
>
>
|
|
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 !!!
"Mike Painter" wrote:
[Quoted Text] > 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 wrote:
> > 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 wrote:
[Quoted Text] > 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.
|
|
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
"Mike Painter" wrote:
[Quoted Text] > 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.
>
>
>
|
|
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" wrote:
[Quoted Text] > 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.
>
>
>
|
|
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.
Joel wrote:
[Quoted Text] > 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" wrote:
>
>> 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.
|
|
thanks for your advice.
Joel
"Mike Painter" wrote:
[Quoted Text] > 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.
>
>
>
>
> Joel wrote:
> > 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" wrote:
> >
> >> 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.
>
>
>
|
|
|