Repeat Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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??
 
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.
 
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
 
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.
 
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
 
Back
Top