Repeat Records

R

Repeat records

In a field in a record how can I insert the value that is in the same field
in the previous record using code?

I know that I can achieve this by clicking in the field in which I want to
insert the value and pressing CTRL + '.

However I have many fields that are empty (null) that require the value in
the above record. How do I automate this??
 
J

Jeff Boyce

One approach is to set the default value for (each) control as you save the
record. In the AfterUpdate event, you could add code that changes the
default value for each control you are trying to do this for.
 
R

Repeat records

Dear Jeff

Thank you for your reply however I am manipulating thousands of records - it
will be rather a laborious process. Also I am not actuallly putting in any
new data.

Many Thanks

Maria
 
J

Jeff Boyce

Maria

Perhaps I wasn't as clear as I could be. I was suggesting that you use the
AfterUpdate event of the form to set the Default Value property of each/any
control for which you wanted to do this. It wouldn't matter if you had one
record or thousands, you would be modifying the form to hold a new default
value.
 
J

John Spencer (MVP)

First thing is to determine what is the PREVIOUS record. You must impose some
kind of order on the records.

YOu can use VB to do this on a record set and probably you can do it with SQL.

UNTESTED VBA with a problem (the first record in the recordset must have a value)

Public Sub sPopulateRecords ()
Dim dbAny as DAO.Database
Dim rsAny as DAO.Recordset
Dim strSQL as String
Dim strLastValue as String

strSQL = "SELECT TheField, thePrimaryKey FROM TheTable ORDER BY thePrimaryKey"

Set dbAny=Currentdb()
Set rsAny=dbAny.OpenRecordset(strSQL)

with rsAny
While Not .EOF
If IsNull(.Fields("TheField")) = True then
.Edit
.Fields("TheField") = strLastValue
.Update
End IF

.MoveNext
If IsNull(.Fields("TheField")) = False then
strLastValue = .Fields("TheField")
End if
Wend
End With

End Sub
 
Top