get most recent records

[mauken]

Sub CopyToTemp()

Dim rstfres As DAO.Recordset
Dim strSql As String

' make our temp1 table
On Error Resume Next
CurrentDb.Execute "drop table temp1", dbFailOnError
On Error GoTo 0
CurrentDb.Execute "SELECT * INTO temp1 FROM fres where id = 0;"

Set rstfres = CurrentDb.OpenRecordset("fres")

Do While rstfres.EOF = False

strSql = "insert into temp1 " & _
"select top 4 * from fresults where horse = '" & _
rstfres!horse & "' order by id DESC"

CurrentDb.Execute strSql

rstfres.MoveNext
Loop

rstfres.Close
Set rstfres = Nothing

End Sub

Mr. Kallal.
The code you gave me works perfect except when there is an apostrophy in the
name. Some of the names have 1 even 2 apostrophies. Is there an easy fix.

 

[Van T. Dinh]

If you are using A2000 or later, try:

strSql = "insert into temp1 " & _
"select top 4 * from fresults where horse = '" & _
Replace(rstfres!horse, "'", "''") & "' order by id DESC"

To clarify, the 2nd and 3rd argument of the Replace() is:

Double-Quote + Single-Quote + Double-Quote

and

Double-Quote + Single-Quote + Single-Quote + Double-Quote

respectively.

 

[mauken]

program working fine now. Thanks for your prompt assistance.