It is not clear if you are creating this temp data for reporting purposes.
If it is, then you don't need any code at all, but just build a query in the
query builder.
Further, the fact that you have runners, and past runners hints that these
two tables should in fact be one table, but with the addition of a field
that
sets if they are active. Even better would be to have a runners table, and
then a table of results for each runner (that way, only ONE instance
of the runners name would exist in the database).
Further, I notice that you are doing the relation via aname
What will happen if you have two John Smiths here? Using a text name to
build this relation, and fish up past data is a not a good idea.
As mentioned, it is not at all clear if you need this data transferred to a
temp table for reporting, or for some other needs.
Last, but not least, trying to take the exact code and logic from a non
relational system and use that SAME approach when you got a FABULOUS
relational database engine is NOT the way to go. In other words, you need to
change your approach here. A small change in your approach will eliminate
the need to write ANY of this code.
I not trying to rain on you here, but you need to start thinking in terms of
a relational system.
Further, the fact that your code grab 4 records assumes some order of data.
This could be assumed in dbaseIII etc, but it can NOT be assumed in modern
database
systems. That means if you add 4 records to a table, and then ask the data
engine to return those record,
THEY DO NOT NECESSARY COME BACK IN THE SAME
ORDER. I am not going to repeat that last statement, but read it again! It
is
a VERY important consideration when using new database systems, be they the
JET engine in ms-access, or a company wide server running oracle that order
is NOT preserved during data writes. This is conceptual change in our
industry
we had to make since products like dbase/FoxPro/clipper etc. were
popular.
So, really, using the EXACT same approach as dbase here is not so good
(however, for the sake of learning..I will post a solution in code).
Having said the above, I going to assume that you also have added a id
column (autonumber) to all your tables.
Also, looking at that dbase code, I don't see where table temp2 is cleared
after each use. I did a good stint in FoxPro for dos, but that was 13 years.
ago, so my dbase skills are a bit rusty.
And, in fact, you likely can built a query that returns the results,a nd not
use ANY code.
further, use of temp tables in ms-access is frowned upon. In dbaseIII etc,
each table is in fact a separate file. In ms-access, that table is created
in one file. This means that when you delete the table, the disk space is
not re-claimed until do a compact an repair. So, another new rule for
ms-access developers is to avoid temp tables like the plague.
So, really, we could just fire up the report builder, and build report
that gives us the results we want.
However, having said as much as is gentlemanlike like here on this
issue, lets just go ahead and write the code anyway....
we will have a table called tblRunners, tblHistory, and temp1 will wind up
with the ending data.
Sub CopyToTemp()
Dim rstRunners 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 tblRunners where id = 0;"
Set rstRunners = CurrentDb.OpenRecordset("tblRunners")
Do While rstRunners.EOF = False
strSql = "insert into temp1 " & _
"select top 4 * from tblHistory where Aname = '" & _
rstRunners!Aname & "' order by id DESC"
CurrentDb.Execute strSql
rstRunners.MoveNext
Loop
rstRunners.Close
Set rstRunners = Nothing
End Sub
