How to automate copying data from previous record to the current

[Guest]

All, I have a table that has missing values for fields. I would like to know
how to copy the contents of fields from a previous record to the current one.
I attempted to do it in an update query but was unsuccessful. Also tried to
create a macro that invoke <CTRL> <'> but that didn't work either. Any
suggestions would be appreciated. The table has thousands of records so
manually copying the fields would be prohibitive.

 

[Bob Quintal]

All, I have a table that has missing values for fields. I would
like to know how to copy the contents of fields from a previous
record to the current one.
I attempted to do it in an update query but was unsuccessful.
Also tried to
create a macro that invoke <CTRL> <'> but that didn't work either.
Any suggestions would be appreciated. The table has thousands of
records so manually copying the fields would be prohibitive.

you could use a recordset,
storing each field to a variable if the field is not null, and
writing from the variable if the field is null
The code below is untested, and needs to be adjusted to your table


dim rs as dao.recordset
set rs= currentdb.openrecordset("SELECT * from table order by
?????")
dim FldCount as integer
dim FldPointer as integer
dim fieldvalues(FldCount)
do while not rs.eof
For fldPointer = 0 to FldCount - 1
if isnull(rs!fields(fldpointer)
fieldvalues(fldPointer) = rs!fields(fldpointer)
else
rs!fields(fldpointer) = fieldvalues(fldPointer)
end if
next fldpointer
loop

 

[Arvin Meyer [MVP]]

In Datasheet View, Ctrl+' should work. It does for me in all versions. I
cannot check Office 2007 right now.

 

[rquintal]

All, I have a table that has missing values for fields. I would like to know
how to copy the contents of fields from a previous record to the current one.
I attempted to do it in an update query but was unsuccessful. Also tried to
create a macro that invoke <CTRL> <'> but that didn't work either. Any
suggestions would be appreciated. The table has thousands of records so
manually copying the fields would be prohibitive.

you could use a recordset,
storing each field to a variable if the field is not null, and writing
from the variable if the field is null
The code below is untested, and needs to be adjusted to your table


dim rs as dao.recordset
set rs= currentdb.openrecordset("SELECT * from table order by ?????")
dim FldCount as integer
dim FldPointer as integer
FldCount = rs.fields.count
dim fieldvalues(FldCount)
do while not rs.eof
For fldPointer = 0 to FldCount - 1
if isnull(rs!fields(fldpointer)
fieldvalues(fldPointer) = rs!fields(fldpointer)
else
rs!fields(fldpointer) = fieldvalues(fldPointer)
end if
next fldpointer
loop

 

[tracy]

you could use a recordset,
storing each field to a variable if the field is not null, and
writing from the variable if the field is null
The code below is untested, and needs to be adjusted to your table


dim rs as dao.recordset
set rs= currentdb.openrecordset("SELECT * from table order by
?????")
dim FldCount as integer
dim FldPointer as integer
dim fieldvalues(FldCount)
do while not rs.eof
For fldPointer = 0 to FldCount - 1
if isnull(rs!fields(fldpointer)
fieldvalues(fldPointer) = rs!fields(fldpointer)
else
rs!fields(fldpointer) = fieldvalues(fldPointer)
end if
next fldpointer
loop