INSERT INTO Table2 from Table1 - maintain sequential index in Table2?

[Tomkat53]

Hello, I'm not sure if this has been asked here before (wouldn't know what to search for).

Here's my situation...

I have records in Table1 that I need to insert into Table2.
However, there is a field in Table2 that is sequential (like an index) that I need to maintain...
Therefore I would need my query to lookup the max value for that index, then add one for each new record added from Table1

Illustration

Table1:
ID
5
6
7
8

Table2:
ID-------TranKey
1 56000
2 56001
3 56002
4 56003

I need to insert the records from Table1 into Table2 (based on ID), and then have TranKey remain sequential...

Table2:
ID-------TranKey
1 56001
2 56002
3 56003
4 56004
5 56005
6 56006
7 56007
8 56008

I would imagine I'd have to use the MAX function to determine the last used value for TranKey in Table2, and a WHILE loop to insert the records... but I'm not sure how to implement everything into one procedure.

Can anyone help me?
Any assistance would be greatly appreciated!

[TheSmileyCoder]

Some example code I just wrote, you just need to modify it with some selection code on WHICH records to append/Transfer (If you don't want all records transferred). To do that, add a Where clause to the OpenRecordset statement.

Public Sub AppendWithMax()

'Open a recordset
    Dim myRS As DAO.Recordset
    Set myRS = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY ID", dbOpenDynaset, dbReadOnly)
    
    
'Set warnings to false, otherwise we get warnings when doing append query
    DoCmd.SetWarnings (False)
    
'Loop through records appending data.
    Dim strSQL As String
    Dim lngTranKey As Long
    
    Do While Not myRS.EOF
        lngTranKey = DMax("TranKey", "Table2") + 1
        strSQL = "INSERT INTO Table2 ( ID, Field1, TranKey )" & _
                " SELECT Table1.ID, Table1.Field1, " & lngTranKey & " AS Expr1" & _
                " FROM Table1 " & _
                " WHERE ID=" & myRS!ID
        
        DoCmd.RunSQL strSQL
        
    
        myRS.MoveNext
    Loop
    
    
'Return to normal warning level
    DoCmd.SetWarnings (True)
    
End Sub

[TheSmileyCoder]

Im also guessing that simply making hte TranKey into a autonumber column is not the solution you were looking for, which would be somewhat simpler :P

[Tomkat53]

This worked perfectly, Thanks!