SQL To Copy Data To Holding Table

[Moonshine]

Hi All

I have written this SQL statement, in VBA to copy data from one table, into a temp holding table. While it copies, it also changes two fields to different reference numbers:

    CurrentDb.Execute "INSERT INTO [TEMP_VISITS] ( [HH1_MV_VISITS_ID], [HH1_MV_VISIT_DET_ID], [HH1_MV_VISITS_DAY], [HH1_MV_VISITS_START], [HH1_MV_VISITS_END], [HH1_MV_VISITS_ADDED_ON], [HH1_MV_VISITS_ADDED_BY])" & _
    " SELECT GETSEQ(""HH1_MV_VISITS_ID""), " & intVisitDet_ID & ", [dbo_HH1_MV_VISITS].[HH1_MV_VISITS_DAY], [dbo_HH1_MV_VISITS].[HH1_MV_VISITS_START], [dbo_HH1_MV_VISITS].[HH1_MV_VISITS_END], [dbo_HH1_MV_VISITS].[HH1_MV_VISITS_ADDED_ON], [dbo_HH1_MV_VISITS].[HH1_MV_VISITS_ADDED_BY]" & _
    " FROM [dbo_HH1_MV_VISITS]" & _
    " WHERE [HH1_MV_VISIT_DET_ID] = " & Me.DetailsRef

Im having problems with the first part of the "SELECT GETSEQ(""HH1_MV_VISITS_ID"")"

All the "GETSEQ" function does, is increment a value by one, and assign it to each record. Creating a unique reference number for the record in the table. However, when it inserts the records (There can be anything from 1 to 20 records) it only ever assigns the first number, and wont increment it, the code then fails as the Field is Unique insert cannot continue...

How would i be able to make it go though each record, when it inserts and call the Function each time so it will add a different number?

 

[Moonshine]

Sorted it, after 5 hours in total doing it!

   With rstVisits
        .MoveFirst
        Do
    
        CurrentDb.Execute "INSERT INTO [TEMP_VISITS] ( [HH1_MV_VISITS_ID], [HH1_MV_VISIT_DET_ID], [HH1_MV_VISITS_DAY], [HH1_MV_VISITS_START], [HH1_MV_VISITS_END], [HH1_MV_VISITS_ADDED_ON], [HH1_MV_VISITS_ADDED_BY])" & _
        " SELECT GETSEQ(""HH1_MV_VISITS_ID"")," & intVisitDet_ID & "," & rstVisits.Fields![HH1_MV_VISITS_DAY] & ",'" & rstVisits.Fields![HH1_MV_VISITS_START] & "','" & rstVisits.Fields![HH1_MV_VISITS_END] & "','" & rstVisits.Fields![HH1_MV_VISITS_ADDED_ON] & "','" & GetUserName & "'"
        
        .MoveNext
        Loop Until .EOF = True
    End With