Most updated record to excel spreadsheet (please continue) 1

[jaminewok]

Does anyone know how to transfer only the most recently updated record from access to excel? i can only get the whole recordsheet to transfer over.
---------------------------------------------------
Have you got something that identifies it as the most recent record (e.g. a date/time field)?

Thank SiJP for this valuable post!
---------------------------------------------------
jaminewok (Programmer) Aug 6, 2004
sorry for the lack of clarification. i have a justified form, and i need to transfer only the most recent entry in the form from access to the first line of an excel sheet.
---------------------------------------------------
SiJP (Programmer) Aug 8, 2004
So, like when you'r user clicks on 'next' you want the entry he's just done to be thrown into excel yeah?

Have you got a primary key for your recordset?

Is it a blank spreadsheet or a new spreadsheet you need the line transfered over to?

Thank SiJP for this valuable post!
---------------------------------------------------
jaminewok (Programmer) Aug 10, 2004
I do not have a primary key, but I can add one if necessary. I hope to transfer to a blank spreadsheet that already exists.

"So, like when you'r user clicks on 'next' you want the entry he's just done to be thrown into excel yeah?"

That is exactly what I want to do.

Thanks
----------------------------------------------------

 

[PeasNCarrots]

If you have a primary Key, you can query against the last updated record.

Private Sub Command11_Click()
Dim sNewRec As Integer
Dim rs As Recordset

Set rs = Me.RecordsetClone

With rs
.AddNew or .Edit
.Fields("Fieldname") = "some Value"
.Update
.Bookmark = .LastModified
sNewRec = .Fields("FieldNameofPrimaryKey").Value

End With

Create Query of last updated record

strSQL = "SELECT fieldname1, fieldnames2 Where (primaryfieldname) =" & sNewRec

Lookup TransferSpreadsheet command.

End Sub



If there is no primary key then you can pull various values from the last modified record and use that in your query.

I have not tested the code b/c my pc is running a program for the next 10 min.