VBA Access-Copy from one record to another-different field

[Kayte]

I need to copy data from one field of the last complete record, to another field of a new record, using VBA for Access 2000. It should be easy, but it is not working.

I have tried this two ways: One using plain VBA and a cheat:
Sub
Dim OldDateValue As Date

DoCmd.GoToRecord , , acNewRec
Me![NewTime].SetFocus
SendKeys (^')
OldDateValue = Me![NewTime].Value
Me![NewTime].Value = Time()
Me![OldTime].Value = OldDateValue
End Sub

On that one, the send keys does not work. I am trying to use the ctrl" to copy the value from the record immediately above it..

A more elegant solution would use DAO, but my DAO is several years out of date (I last wrote DAO code for Office 97)

Here is as far as I've gotten:

Dim w As Workspace
Dim db As Database
Dim rs As Recordset
Dim OldField As Field
Dim NewField As Field
Dim OldDateValue As Date

Set w = DBEngine.Workspaces(0)
Set db = w.databases(0)
Set rs = db.OpenRecordset("tblTest")
Set fld1 = rs![tblTest]![OldTime]
Set fld2 = rs![tblTest]![NewTime]
rs.MoveLast
OldDateValue = fld2.Value

I would then need to set the field 1 of a new record to the OldDateValue.

On that one, it is giving me a type mismatch for rs=db.openrecordset("tblTest")

Basically, on each new record, the old time field should equal the new time field of the previous record.

Any ideas?

 

[mdmilner]

Re: VBA Access-Copy from one record to another-different fie

Workspace is unneccessary, so are field variables.
I'd also recommend explictly declaring the objects as DAO to avoid referencing issues.

To play with fields, usually you also work with tabledefs at the same time.
This way below tends to be much easier.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strVal As String

Set db = Currentdb()

Set rs = db.OpenRecordset("tblTest")
With rs
  strVal = .Fields(1).Value
  .AddNew
  .fields(1).value = strVAl
  .Update
End with

 

[Kayte]

Thanks, that helped.

K