Looping between a MainForm and a Linked SubForm

[Glynn]

My Receipts are processed through two linked forms - with heading data in the
MainFrm and allocations in the SubFrm - which is a Continuous form.
Selected MainFrm data is copied to the Allocations SubFrm by SETVALUE
functions which are triggered by the SAVE command - which is clicked after
all entries (and corrections) have been made. I need to flick backwards and
forwards between the two forms while entering the data. The number of
allocations is variable.

I have attempted to LOOP the SetValue Macros by including the following code
in the Save Command eg., for the Recpt No.:

Me.AllocSubFrm
DoCmd.RunCommand acSelectAllRecords

Do
DoCmd.RunMacro.SetVal_RcptNo
Loop While AllocSubFrm.[RcptNo] > 0

(I tried using .EOF but couldn't work out the code needed)

Above is not working - it updates only the very first record.

Other combinations I have tried resulted in the field being upddated in
every record - but the code kept running, out of control, requiring
Alt+Ctrl+Delete to stop.

I am not confident that the above code is anywhere near correct - and would
appreciate some guidance or example of how a Loop statement should be
constructed.
Glynn

 

[BruceM via AccessMonster.com]

For what reason do you need to copy data? If the tables are related the main
form's data are available to the subform.

This assumes your main form and subform record source tables are joined in a
typical one-to-many relationship, with the linking field defined in the
subform control's Link Child and Link Master properties.

Regarding your specific question, I suppose you could devise Update SQL in
code, and update the subform's records to insert information from the main
form's record. I don't know what you would do if the data in the main form's
recordset changes (that is, one of the copied fields). If the data in the
subform is to remain unchanged you wouldn't need to do anything; otherwise
you would need something like After Update code for controls in the main form
to run the Update again.

Your question is more about devising a Loop statement than about the best way
to solve a problem. If you provide some description of the situation and the
way in which you want to present the data it may be possible to steer you
toward the most efficient route to your destination.

My Receipts are processed through two linked forms - with heading data in the
MainFrm and allocations in the SubFrm - which is a Continuous form.
Selected MainFrm data is copied to the Allocations SubFrm by SETVALUE
functions which are triggered by the SAVE command - which is clicked after
all entries (and corrections) have been made. I need to flick backwards and
forwards between the two forms while entering the data. The number of
allocations is variable.

I have attempted to LOOP the SetValue Macros by including the following code
in the Save Command eg., for the Recpt No.:

Me.AllocSubFrm
DoCmd.RunCommand acSelectAllRecords

Do
DoCmd.RunMacro.SetVal_RcptNo
Loop While AllocSubFrm.[RcptNo] > 0

(I tried using .EOF but couldn't work out the code needed)

Above is not working - it updates only the very first record.

Other combinations I have tried resulted in the field being upddated in
every record - but the code kept running, out of control, requiring
Alt+Ctrl+Delete to stop.

I am not confident that the above code is anywhere near correct - and would
appreciate some guidance or example of how a Loop statement should be
constructed.
Glynn

 

[Glynn]

Thank you Bruce - As you suggest, lets address the problem, not a wild
attempt at a solution.
Yes, my MainFrm and SubFrm are properly linked, and what I'm attempting to
do, is to update the SubFrm fields from the MainFrm, which works fine for
all the initial entries in the SubFrm. The SubFrm records nicely pick up all
the related values from the MainFrm - BUT - I need also the go back to the
MainFrm and correct data input errors there, after I have completed input in
the SubFrm - say, for example the Date of the entry.
If I change the Date in the MainFrm, it inserts the NEW Date for any NEW
SubFrm record - BUT - not for all the previous records. This, I believe, is
because when I move out of the SubFrm back to the MainFrm, the SubFrm closes
and thus saves all the records todate.
I was contemplating putting a SETVALUE macro in my SAVE COMMAND to Loop
through all the SubFrm records - updating each fileld from the MainFrm when I
SAVED - but I think this creates more other problems than it solves, and my
Looping knowledge is pathetically meager.

Can you think of any way to update ALL records in a SubFrm from changes made
to the MainFrm data.


--
Glynn

For what reason do you need to copy data? If the tables are related the main
form's data are available to the subform.

This assumes your main form and subform record source tables are joined in a
typical one-to-many relationship, with the linking field defined in the
subform control's Link Child and Link Master properties.

Regarding your specific question, I suppose you could devise Update SQL in
code, and update the subform's records to insert information from the main
form's record. I don't know what you would do if the data in the main form's
recordset changes (that is, one of the copied fields). If the data in the
subform is to remain unchanged you wouldn't need to do anything; otherwise
you would need something like After Update code for controls in the main form
to run the Update again.

Your question is more about devising a Loop statement than about the best way
to solve a problem. If you provide some description of the situation and the
way in which you want to present the data it may be possible to steer you
toward the most efficient route to your destination.

My Receipts are processed through two linked forms - with heading data in the
MainFrm and allocations in the SubFrm - which is a Continuous form.
Selected MainFrm data is copied to the Allocations SubFrm by SETVALUE
functions which are triggered by the SAVE command - which is clicked after
all entries (and corrections) have been made. I need to flick backwards and
forwards between the two forms while entering the data. The number of
allocations is variable.

I have attempted to LOOP the SetValue Macros by including the following code
in the Save Command eg., for the Recpt No.:

Me.AllocSubFrm
DoCmd.RunCommand acSelectAllRecords

Do
DoCmd.RunMacro.SetVal_RcptNo
Loop While AllocSubFrm.[RcptNo] > 0

(I tried using .EOF but couldn't work out the code needed)

Above is not working - it updates only the very first record.

Other combinations I have tried resulted in the field being upddated in
every record - but the code kept running, out of control, requiring
Alt+Ctrl+Delete to stop.

I am not confident that the above code is anywhere near correct - and would
appreciate some guidance or example of how a Loop statement should be
constructed.
Glynn

--



.

 

[Glynn]

Bruce - Following my last message to you - I again reverted to my attempt to
use SETVALUE functions to overcome the problem - and - it appears to now work
(in a fashion).

Heres what I've done:
1. I removed the Parent/Child linkage between the MainFrm and the SubFrm,
which
allows me to move back and forth freely between the Main and the Sub,
without the
SubFrm SAVING its records.
Both Forms just remain open, while I make alterations to previously
entered data.
2. In my Form SAVE command 'OnClick' property - I do RunMacro events to run
SETVALUE macros, to update the SubFrm with any changes to MainFrm data.
3. The Macros would normally only update the CURRENT record in the SuBFrm,
not all
the records, which is the problem.
To overcome this problem, I have simply expanded the Macro by repeating
the
SETVALUE action, ad nauseum, say 30 times - to ensure that all the
records in the
SubFrm are updated. Too bad, however, if there are 31 records in the
SubFrm !
4. This Mickey Mouse solution works - BUT has drawbacks:
a) How many repeats of the SETVAL action should I do - normally I
would
expect the User to have only 10 - 15 SubForm records - so is 30
repeats
adequate, 50 ?, 200 ? (my problem).
b) When I do SAVE, the SubFrm flickers convulsively for a second or
so, as it
digests all the repeat actions (not a pretty sight).

Therefore I come back to my LOOPING question - I would like the SETVAL Macro
to loop through all the records in the SubFrm - but stop on the last record,
instead of going on and on 30 - 50 times when there are only one or two
records.

This is where I come unstuck - I can't devise a Looping and EOF to be
triggered by my SAVE command. Is it possible to build a Loop proceedure into
my SAVE Command instead of the awkward 30 or 50 repeats of a SETVAL action.

Appreciate being pointed in the right direction or an example of how a LOOP
may be written.
-- -Glynn

Thank you Bruce - As you suggest, lets address the problem, not a wild
attempt at a solution.
Yes, my MainFrm and SubFrm are properly linked, and what I'm attempting to
do, is to update the SubFrm fields from the MainFrm, which works fine for
all the initial entries in the SubFrm. The SubFrm records nicely pick up all
the related values from the MainFrm - BUT - I need also the go back to the
MainFrm and correct data input errors there, after I have completed input in
the SubFrm - say, for example the Date of the entry.
If I change the Date in the MainFrm, it inserts the NEW Date for any NEW
SubFrm record - BUT - not for all the previous records. This, I believe, is
because when I move out of the SubFrm back to the MainFrm, the SubFrm closes
and thus saves all the records todate.
I was contemplating putting a SETVALUE macro in my SAVE COMMAND to Loop
through all the SubFrm records - updating each fileld from the MainFrm when I
SAVED - but I think this creates more other problems than it solves, and my
Looping knowledge is pathetically meager.

Can you think of any way to update ALL records in a SubFrm from changes made
to the MainFrm data.


--
Glynn

For what reason do you need to copy data? If the tables are related the main
form's data are available to the subform.

This assumes your main form and subform record source tables are joined in a
typical one-to-many relationship, with the linking field defined in the
subform control's Link Child and Link Master properties.

Regarding your specific question, I suppose you could devise Update SQL in
code, and update the subform's records to insert information from the main
form's record. I don't know what you would do if the data in the main form's
recordset changes (that is, one of the copied fields). If the data in the
subform is to remain unchanged you wouldn't need to do anything; otherwise
you would need something like After Update code for controls in the main form
to run the Update again.

Your question is more about devising a Loop statement than about the best way
to solve a problem. If you provide some description of the situation and the
way in which you want to present the data it may be possible to steer you
toward the most efficient route to your destination.

My Receipts are processed through two linked forms - with heading data in the
MainFrm and allocations in the SubFrm - which is a Continuous form.
Selected MainFrm data is copied to the Allocations SubFrm by SETVALUE
functions which are triggered by the SAVE command - which is clicked after
all entries (and corrections) have been made. I need to flick backwards and
forwards between the two forms while entering the data. The number of
allocations is variable.

I have attempted to LOOP the SetValue Macros by including the following code
in the Save Command eg., for the Recpt No.:

Me.AllocSubFrm
DoCmd.RunCommand acSelectAllRecords

Do
DoCmd.RunMacro.SetVal_RcptNo
Loop While AllocSubFrm.[RcptNo] > 0

(I tried using .EOF but couldn't work out the code needed)

Above is not working - it updates only the very first record.

Other combinations I have tried resulted in the field being upddated in
every record - but the code kept running, out of control, requiring
Alt+Ctrl+Delete to stop.

I am not confident that the above code is anywhere near correct - and would
appreciate some guidance or example of how a Loop statement should be
constructed.
Glynn

--



.

 

[BruceM via AccessMonster.com]

If every subform record, including "old" records, is to show the same date as
the main form record, there is no need to store the date in the subform at
all. The fact the records are linked associates the parent table's data with
each child record.

However, if there is a need to update the subform records you could have
something like this in, say, a command button Click event on the main form
(untested air code):

Dim datMain as Date
Dim lngID as Long
Dim strSQL as String

datMain = Me.[DateField]
lngID = Me.[ID]

strSQL = "UPDATE tblChild SET [ChildDate] = " & _
Format(datMain, "\#mm/dd/yyyy\#") & _
"WHERE [ID] = " & lngID

CurrentDb.Execute strSQL, dbFailOnError

This assumes ID is the linking field in both tables, and that it is Long
Integer in both (or autonumber in the parent table and Long Integer in the
child). I have use tblChild as the name of the child table, ChildDate as the
date field in the child table, and DateField as the date field in the parent
table.

There are other ways to update a recordset, but again you should not need to
if each child record is to have the same date as the main record. In any
case, as you have surmised selecting an arbitrary number of times to run
through the code is not a workable solution.

Bruce - Following my last message to you - I again reverted to my attempt to
use SETVALUE functions to overcome the problem - and - it appears to now work
(in a fashion).

Heres what I've done:
1. I removed the Parent/Child linkage between the MainFrm and the SubFrm,
which
allows me to move back and forth freely between the Main and the Sub,
without the
SubFrm SAVING its records.
Both Forms just remain open, while I make alterations to previously
entered data.
2. In my Form SAVE command 'OnClick' property - I do RunMacro events to run
SETVALUE macros, to update the SubFrm with any changes to MainFrm data.
3. The Macros would normally only update the CURRENT record in the SuBFrm,
not all
the records, which is the problem.
To overcome this problem, I have simply expanded the Macro by repeating
the
SETVALUE action, ad nauseum, say 30 times - to ensure that all the
records in the
SubFrm are updated. Too bad, however, if there are 31 records in the
SubFrm !
4. This Mickey Mouse solution works - BUT has drawbacks:
a) How many repeats of the SETVAL action should I do - normally I
would
expect the User to have only 10 - 15 SubForm records - so is 30
repeats
adequate, 50 ?, 200 ? (my problem).
b) When I do SAVE, the SubFrm flickers convulsively for a second or
so, as it
digests all the repeat actions (not a pretty sight).

Therefore I come back to my LOOPING question - I would like the SETVAL Macro
to loop through all the records in the SubFrm - but stop on the last record,
instead of going on and on 30 - 50 times when there are only one or two
records.

This is where I come unstuck - I can't devise a Looping and EOF to be
triggered by my SAVE command. Is it possible to build a Loop proceedure into
my SAVE Command instead of the awkward 30 or 50 repeats of a SETVAL action.

Appreciate being pointed in the right direction or an example of how a LOOP
may be written.
-- -Glynn

Thank you Bruce - As you suggest, lets address the problem, not a wild
attempt at a solution.

[quoted text clipped - 66 lines]

 

[Glynn]

Code is not working for me. Can you see where I may be wrong ?

Dim DateMain As Date
Dim IngID As Long
Dim strSQL As String

DateMain = Me.[Dateof Trnsactn]
IngID = Me.[ID]

strSQL = "UPDATE Transactions SET [Dateof Trnsactn] = " & Format (DateMain,
"\#mm/dd/yyyy\#") & WHERE [ID] = IngID"

Current Db.Execute strSQL, dbFailOnError

I get a Run-Tine Ettor 3144
Syntax err in Update statement.

[Dateof Trnsactn] is the date name for both the Main Form and the SubFrm -
both drawing from the same Table 'Transactions'.
The ID field is the Linking field, being the same field in the common
'Transactions' Table for both Forms.

Again, with the Linked Forms, there is no problem with the 'Date' in the
MainFrm being faithfully recorded in the SubFrm - in the initial input. It
is a problem, if the User goes back and alters the original input date.

Glynn

If every subform record, including "old" records, is to show the same date as
the main form record, there is no need to store the date in the subform at
all. The fact the records are linked associates the parent table's data with
each child record.

However, if there is a need to update the subform records you could have
something like this in, say, a command button Click event on the main form
(untested air code):

Dim datMain as Date
Dim lngID as Long
Dim strSQL as String

datMain = Me.[DateField]
lngID = Me.[ID]

strSQL = "UPDATE tblChild SET [ChildDate] = " & _
Format(datMain, "\#mm/dd/yyyy\#") & _
"WHERE [ID] = " & lngID

CurrentDb.Execute strSQL, dbFailOnError

This assumes ID is the linking field in both tables, and that it is Long
Integer in both (or autonumber in the parent table and Long Integer in the
child). I have use tblChild as the name of the child table, ChildDate as the
date field in the child table, and DateField as the date field in the parent
table.

There are other ways to update a recordset, but again you should not need to
if each child record is to have the same date as the main record. In any
case, as you have surmised selecting an arbitrary number of times to run
through the code is not a workable solution.

Bruce - Following my last message to you - I again reverted to my attempt to
use SETVALUE functions to overcome the problem - and - it appears to now work
(in a fashion).

Heres what I've done:
1. I removed the Parent/Child linkage between the MainFrm and the SubFrm,
which
allows me to move back and forth freely between the Main and the Sub,
without the
SubFrm SAVING its records.
Both Forms just remain open, while I make alterations to previously
entered data.
2. In my Form SAVE command 'OnClick' property - I do RunMacro events to run
SETVALUE macros, to update the SubFrm with any changes to MainFrm data.
3. The Macros would normally only update the CURRENT record in the SuBFrm,
not all
the records, which is the problem.
To overcome this problem, I have simply expanded the Macro by repeating
the
SETVALUE action, ad nauseum, say 30 times - to ensure that all the
records in the
SubFrm are updated. Too bad, however, if there are 31 records in the
SubFrm !
4. This Mickey Mouse solution works - BUT has drawbacks:
a) How many repeats of the SETVAL action should I do - normally I
would
expect the User to have only 10 - 15 SubForm records - so is 30
repeats
adequate, 50 ?, 200 ? (my problem).
b) When I do SAVE, the SubFrm flickers convulsively for a second or
so, as it
digests all the repeat actions (not a pretty sight).

Therefore I come back to my LOOPING question - I would like the SETVAL Macro
to loop through all the records in the SubFrm - but stop on the last record,
instead of going on and on 30 - 50 times when there are only one or two
records.

This is where I come unstuck - I can't devise a Looping and EOF to be
triggered by my SAVE command. Is it possible to build a Loop proceedure into
my SAVE Command instead of the awkward 30 or 50 repeats of a SETVAL action.

Appreciate being pointed in the right direction or an example of how a LOOP
may be written.
-- -Glynn

Thank you Bruce - As you suggest, lets address the problem, not a wild
attempt at a solution.

[quoted text clipped - 66 lines]

constructed.
Glynn

--



.

 

[BruceM via AccessMonster.com]

I notice some things with quotes:

strSQL = "UPDATE Transactions SET [Dateof Trnsactn] = " & Format (DateMain,
"\#mm/dd/yyyy\#") & " WHERE [ID] = " & lngID

Note that ampersand space underscore is a line continuation character so the
string stays where you can see it without scrolling. They are optional, but
that is why I used them in my previous example.

Code is not working for me. Can you see where I may be wrong ?

Dim DateMain As Date
Dim IngID As Long
Dim strSQL As String

DateMain = Me.[Dateof Trnsactn]
IngID = Me.[ID]

strSQL = "UPDATE Transactions SET [Dateof Trnsactn] = " & Format (DateMain,
"\#mm/dd/yyyy\#") & WHERE [ID] = IngID"

Current Db.Execute strSQL, dbFailOnError

I get a Run-Tine Ettor 3144
Syntax err in Update statement.

[Dateof Trnsactn] is the date name for both the Main Form and the SubFrm -
both drawing from the same Table 'Transactions'.
The ID field is the Linking field, being the same field in the common
'Transactions' Table for both Forms.

Again, with the Linked Forms, there is no problem with the 'Date' in the
MainFrm being faithfully recorded in the SubFrm - in the initial input. It
is a problem, if the User goes back and alters the original input date.

Glynn

If every subform record, including "old" records, is to show the same date as
the main form record, there is no need to store the date in the subform at

[quoted text clipped - 80 lines]