Carry over values from previous record

[Kurt Heisler]

I'm trying to use Allen Browne's carry over function (http://
allenbrowne.com/ser-24.html) to carry over (i.e., copy) data from the
previous record to a new record. In my case, however, the data I need
to carry over are spread across *three* tables (instead of one table,
which is what I think his code was designed to handle).

Can his code be adjusted to make this work?

Here's the situation:

One patient can have many daily records, so there's a one:to:many
between tblPatients and tblDaily. I exceeded the field limit for
tblDaily, so I've split it into three tables linked together via a
1:1. (Long story, but it's as normalized as possible given some of the
restrictions on how the users want the data handled.)

The three tables look like this:

tblDailyOne
--------
*DailyDataID (AutoNumber)
PtID
HospitalDay
etc.

tblDailyTwo
--------
*DailyDataID (Number)
PtID
FieldABC
etc.

tblDailyThree
--------
*DailyDataID (Number)
PtID
FieldXYZ
etc.

When the user enters a record in tblDailyOne (via frmDaily), an update
query runs and populates the other two tables with the corresponding
DailyDataID and PtID. The user finishes entering the rest of the
record (i.e., the fields in tblDailyTwo & tblDailyThree) using several
subforms which are based on select queries from either tblDailyTwo or
tblDailyThree. The subforms are linked to frmDaily via DailyDataID.

So far so good.

Now I'm trying to use Allen Browne's carry over function to carry over
the previous record (which is spread across three tables) to the next
record (which will also be spread across the three tables). The
carryover function works fine for data entered on frmDaily (i.e., in
tblDailyOne), but not for data entered via the subforms (i.e., in the
other 2 tables). For these subforms (and tables), nothing is carried
over and there is no error message. This is true even though I've
applied the BeforeInsert property (=CarryOver([Form], "") to *each*
subform. I suspect the problem is that Browne's code assumes that data
is being carried over from one table, not three.

Any idea how I can edit his code to make this happen?

Thank you.

 

[Jeff Boyce]

Kurt

It is rarely necessary to use more than, say, 30 fields in a well-normalized
relational database table. If your [tblDaily] needs more fields than the
255 field limit in Access, consider stepping back from the keyboard. You
won't get good (or easy) use of Access' relationally-oriented
features/functions if the data you try to feed it isn't
relational/normalized.

If you've gone so far as to create three tables to hold all the tblDaily
data, turn off your PC now! If you'll post a bit more specific information
about what kind of data you are trying to store in all those fields, folks
here may be able to offer alternate approaches that will make it much easier
on both you and Access!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to use Allen Browne's carry over function (http://
allenbrowne.com/ser-24.html) to carry over (i.e., copy) data from the
previous record to a new record. In my case, however, the data I need
to carry over are spread across *three* tables (instead of one table,
which is what I think his code was designed to handle).

Can his code be adjusted to make this work?

Here's the situation:

One patient can have many daily records, so there's a one:to:many
between tblPatients and tblDaily. I exceeded the field limit for
tblDaily, so I've split it into three tables linked together via a
1:1. (Long story, but it's as normalized as possible given some of the
restrictions on how the users want the data handled.)

The three tables look like this:

tblDailyOne
--------
*DailyDataID (AutoNumber)
PtID
HospitalDay
etc.

tblDailyTwo
--------
*DailyDataID (Number)
PtID
FieldABC
etc.

tblDailyThree
--------
*DailyDataID (Number)
PtID
FieldXYZ
etc.

When the user enters a record in tblDailyOne (via frmDaily), an update
query runs and populates the other two tables with the corresponding
DailyDataID and PtID. The user finishes entering the rest of the
record (i.e., the fields in tblDailyTwo & tblDailyThree) using several
subforms which are based on select queries from either tblDailyTwo or
tblDailyThree. The subforms are linked to frmDaily via DailyDataID.

So far so good.

Now I'm trying to use Allen Browne's carry over function to carry over
the previous record (which is spread across three tables) to the next
record (which will also be spread across the three tables). The
carryover function works fine for data entered on frmDaily (i.e., in
tblDailyOne), but not for data entered via the subforms (i.e., in the
other 2 tables). For these subforms (and tables), nothing is carried
over and there is no error message. This is true even though I've
applied the BeforeInsert property (=CarryOver([Form], "") to *each*
subform. I suspect the problem is that Browne's code assumes that data
is being carried over from one table, not three.

Any idea how I can edit his code to make this happen?

Thank you.

 

[Kurt Heisler]

Test reply.

 

[Kurt Heisler]

(First reply didn't show ... trying again.)

This database is primarily used to facilitate data entry. Most of the
data manipulation will be done in a statistical program, where data is
best handled as a flat file. So, for example, instead of having a
table for Diagnoses (which can easily take on new values), and a combo
box to assign diagnoses to a patient, I've hard coded a separate Y/N
variable for each diagnosis. This method requires that all possible
diagnoses be identified a priori, but doing that is a common practice
in a research environment.

Over the years, I've determined that the process of converting
properly normalized databases to flat files for analysis in stat
programs is often more trouble than its worth. So when the database is
used primarily for data entry, and most of the data manipulation will
occur in a stat program, I usually opt for a non-normalized design.
This is one of those times.

So, I was hoping to find a solution to my problem despite the non-
normal structure of my data.

Kurt

It is rarely necessary to use more than, say, 30 fields in a well-normalized
relational database table.  If your [tblDaily] needs more fields than the
255 field limit in Access, consider stepping back from the keyboard.  You
won't get good (or easy) use of Access' relationally-oriented
features/functions if the data you try to feed it isn't
relational/normalized.

If you've gone so far as to create three tables to hold all the tblDaily
data, turn off your PC now!  If you'll post a bit more specific information
about what kind of data you are trying to store in all those fields, folks
here may be able to offer alternate approaches that will make it much easier
on both you and Access!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to use Allen Browne's carry over function (http://
allenbrowne.com/ser-24.html) to carry over (i.e., copy) data from the
previous record to a new record. In my case, however, the data I need
to carry over are spread across *three* tables (instead of one table,
which is what I think his code was designed to handle).

Can his code be adjusted to make this work?

Here's the situation:

One patient can have many daily records, so there's a one:to:many
between tblPatients and tblDaily. I exceeded the field limit for
tblDaily, so I've split it into three tables linked together via a
1:1. (Long story, but it's as normalized as possible given some of the
restrictions on how the users want the data handled.)

The three tables look like this:

tblDailyOne
--------
*DailyDataID (AutoNumber)
PtID
HospitalDay
etc.

tblDailyTwo
--------
*DailyDataID (Number)
PtID
FieldABC
etc.

tblDailyThree
--------
*DailyDataID (Number)
PtID
FieldXYZ
etc.

When the user enters a record in tblDailyOne (via frmDaily), an update
query runs and populates the other two tables with the corresponding
DailyDataID and PtID. The user finishes entering the rest of the
record (i.e., the fields in tblDailyTwo & tblDailyThree) using several
subforms which are based on select queries from either tblDailyTwo or
tblDailyThree. The subforms are linked to frmDaily via DailyDataID.

So far so good.

Now I'm trying to use Allen Browne's carry over function to carry over
the previous record (which is spread across three tables) to the next
record (which will also be spread across the three tables). The
carryover function works fine for data entered on frmDaily (i.e., in
tblDailyOne), but not for data entered via the subforms (i.e., in the
other 2 tables). For these subforms (and tables), nothing is carried
over and there is no error message. This is true even though I've
applied the BeforeInsert property (=CarryOver([Form], "") to *each*
subform. I suspect the problem is that Browne's code assumes that data
is being carried over from one table, not three.

Any idea how I can edit his code to make this happen?

Thank you.

 

[Jeff Boyce]

I think you'll find that it is relatively easy to create a query that
"flattens" a well-normalized table structure.

If your table structure is designed "to facilitate data entry", perhaps your
situation doesn't require the additional features/functions that Access can
provide. Do you have other options for data entry?

If you want what Access can provide, you can either design to Access'
strengths, or you (and Access) can develop intricate work-arounds. Either
way, I suspect it will 'cost' you ... <g>!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


(First reply didn't show ... trying again.)

This database is primarily used to facilitate data entry. Most of the
data manipulation will be done in a statistical program, where data is
best handled as a flat file. So, for example, instead of having a
table for Diagnoses (which can easily take on new values), and a combo
box to assign diagnoses to a patient, I've hard coded a separate Y/N
variable for each diagnosis. This method requires that all possible
diagnoses be identified a priori, but doing that is a common practice
in a research environment.

Over the years, I've determined that the process of converting
properly normalized databases to flat files for analysis in stat
programs is often more trouble than its worth. So when the database is
used primarily for data entry, and most of the data manipulation will
occur in a stat program, I usually opt for a non-normalized design.
This is one of those times.

So, I was hoping to find a solution to my problem despite the non-
normal structure of my data.

Kurt

It is rarely necessary to use more than, say, 30 fields in a
well-normalized
relational database table. If your [tblDaily] needs more fields than the
255 field limit in Access, consider stepping back from the keyboard. You
won't get good (or easy) use of Access' relationally-oriented
features/functions if the data you try to feed it isn't
relational/normalized.

If you've gone so far as to create three tables to hold all the tblDaily
data, turn off your PC now! If you'll post a bit more specific information
about what kind of data you are trying to store in all those fields, folks
here may be able to offer alternate approaches that will make it much
easier
on both you and Access!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to use Allen Browne's carry over function (http://
allenbrowne.com/ser-24.html) to carry over (i.e., copy) data from the
previous record to a new record. In my case, however, the data I need
to carry over are spread across *three* tables (instead of one table,
which is what I think his code was designed to handle).

Can his code be adjusted to make this work?

Here's the situation:

One patient can have many daily records, so there's a one:to:many
between tblPatients and tblDaily. I exceeded the field limit for
tblDaily, so I've split it into three tables linked together via a
1:1. (Long story, but it's as normalized as possible given some of the
restrictions on how the users want the data handled.)

The three tables look like this:

tblDailyOne
--------
*DailyDataID (AutoNumber)
PtID
HospitalDay
etc.

tblDailyTwo
--------
*DailyDataID (Number)
PtID
FieldABC
etc.

tblDailyThree
--------
*DailyDataID (Number)
PtID
FieldXYZ
etc.

When the user enters a record in tblDailyOne (via frmDaily), an update
query runs and populates the other two tables with the corresponding
DailyDataID and PtID. The user finishes entering the rest of the
record (i.e., the fields in tblDailyTwo & tblDailyThree) using several
subforms which are based on select queries from either tblDailyTwo or
tblDailyThree. The subforms are linked to frmDaily via DailyDataID.

So far so good.

Now I'm trying to use Allen Browne's carry over function to carry over
the previous record (which is spread across three tables) to the next
record (which will also be spread across the three tables). The
carryover function works fine for data entered on frmDaily (i.e., in
tblDailyOne), but not for data entered via the subforms (i.e., in the
other 2 tables). For these subforms (and tables), nothing is carried
over and there is no error message. This is true even though I've
applied the BeforeInsert property (=CarryOver([Form], "") to *each*
subform. I suspect the problem is that Browne's code assumes that data
is being carried over from one table, not three.

Any idea how I can edit his code to make this happen?

Thank you.

 

[Clifford Bass]

Hi Kurt,

I have to agree with you. There are times it is better not to
"normalize". And your situation sounds like one to me.

I am thinking, without too much looking at Allen's code, that the issue
is that there is no "previous" record to copy from in the subforms. Try
putting a break point on the "Set rs = frm.RecordsetClone" line in the
CarryOver function. The first time it breaks, will most likely be the main
form. The second and third should be the subforms. Do a Shift-F8 through
the next few lines to see what happens. Then press F5 to continue on.

Here is why I think there is no "previous" record. Subforms are
filtered to show the child records only applicable to the parent table. When
you start out a new parent record, there are no child rows; hence, no prior
record to be found. So, for the children, you may want to implement the copy
in some other fashion, perhaps in the main form's after update event.
Perhaps just a couple of "insert into childtable select * from childtable
where keyfield(s) = [keyfield(s) from main form]" Then do a requery on each
subform.

Hope that helps,

Clifford Bass

I'm trying to use Allen Browne's carry over function (http://
allenbrowne.com/ser-24.html) to carry over (i.e., copy) data from the
previous record to a new record. In my case, however, the data I need
to carry over are spread across *three* tables (instead of one table,
which is what I think his code was designed to handle).

Can his code be adjusted to make this work?

Here's the situation:

One patient can have many daily records, so there's a one:to:many
between tblPatients and tblDaily. I exceeded the field limit for
tblDaily, so I've split it into three tables linked together via a
1:1. (Long story, but it's as normalized as possible given some of the
restrictions on how the users want the data handled.)

The three tables look like this:

tblDailyOne
--------
*DailyDataID (AutoNumber)
PtID
HospitalDay
etc.

tblDailyTwo
--------
*DailyDataID (Number)
PtID
FieldABC
etc.

tblDailyThree
--------
*DailyDataID (Number)
PtID
FieldXYZ
etc.

When the user enters a record in tblDailyOne (via frmDaily), an update
query runs and populates the other two tables with the corresponding
DailyDataID and PtID. The user finishes entering the rest of the
record (i.e., the fields in tblDailyTwo & tblDailyThree) using several
subforms which are based on select queries from either tblDailyTwo or
tblDailyThree. The subforms are linked to frmDaily via DailyDataID.

So far so good.

Now I'm trying to use Allen Browne's carry over function to carry over
the previous record (which is spread across three tables) to the next
record (which will also be spread across the three tables). The
carryover function works fine for data entered on frmDaily (i.e., in
tblDailyOne), but not for data entered via the subforms (i.e., in the
other 2 tables). For these subforms (and tables), nothing is carried
over and there is no error message. This is true even though I've
applied the BeforeInsert property (=CarryOver([Form], "") to *each*
subform. I suspect the problem is that Browne's code assumes that data
is being carried over from one table, not three.

Any idea how I can edit his code to make this happen?

Thank you.

 

[Clifford Bass]

Hi Kurt,

One small correction. It should use the key fields of the previous
main row, not the current one. You will need to figure out how to get that
value.

Clifford Bass