Return value from each run-through of a loop

[stickers]

I have stuck my code at the end. I'm sure there's a simple way to do this. I am using the code to replicate a record a given number of times, changing the value of one field so that you end up with the original plus 1, 2 or more copies, all with different names: ***Temp1, ***Temp2 etc. In the table that I am adding these copies to, I have an autonumber field. What I want the code to do is get the Autonumber of each of the new records as it is created, and store this in a different table (so that I can open a data entry form showing only the newly created records)
I have asterisked the code that I think ought to work but doesn't - just returns zeros.

PS. Beetee if you are reading this - I tried your solution on the other thread I posted, but I have so many fields and records that the form with subforms was just getting really messy. So I wrote this code which does more or less what I want.

PPS. If anyone looks at my code and thinks that there must be an easier way around this (I'm sure there is!! I'm pretty new!!) then feel free to let me know.

PPPS. Thank you to this forum - if it hadn't been for my frequent wanderings around here I would never have got this far - only started programming 2 months ago!!!




Private Sub cmdDefineSites_Click()
'subroutine variables
Dim intSiteID As Integer
Dim strNewSiteName As String 'the new site name that will be given
Dim cnCurrent As ADODB.Connection
Dim rsSiteDetails As ADODB.Recordset
Dim rsProjSite As ADODB.Recordset
Dim rsSiteRoom As ADODB.Recordset
Dim counter As Integer
Dim intProjSiteID As Integer
Dim strDelProjSite As String
Dim strRecordNewSiteID As String
Dim intProjID As Integer


'variables relating to values to be copied into new records
'in table
Dim strCurSiteName As String
Dim lngSiteArea As Long
Dim lngPopulation As Long
'Dim datFFEStartDate As Date can be added back in once a
'default date is set
Dim strFunction As String
Dim intPercentNewBuild As Integer
'Dim bolIsDefault As Boolean
Dim intNewSiteID As Integer
'values for using to append tblSiteRoom
Dim intRoomID As Integer
Dim strDept As String
Dim intRmNum As Integer

On Error GoTo Err_cmdDefineSites_Click

intProjID = Me!cboProjID.Value


If IsNotDefault(cboSiteID) Then
MsgBox "This site has already been specified, please choose another", , "Jarvis FFE Database"
Exit Sub

Else
'instanciate connection variables
Set cnCurrent = CurrentProject.Connection
Set rsSiteDetails = New ADODB.Recordset
Set rsProjSite = New ADODB.Recordset
Set rsSiteRoom = New ADODB.Recordset

intSiteID = Me!cboSiteID.Value
intProjSiteID = Me!txtProjSiteID.Value

'populate recordsets
rsSiteDetails.Open "SELECT * FROM tblSiteDetails WHERE SiteID = " & intSiteID, cnCurrent, adOpenDynamic, adLockPessimistic
rsProjSite.Open "SELECT * FROM tblProjSite WHERE ProjSiteID = " & intProjSiteID, cnCurrent, adOpenDynamic, adLockPessimistic
rsSiteRoom.Open "SELECT * FROM tblSiteRoom WHERE SiteID = " & intSiteID, cnCurrent, adOpenDynamic, adLockPessimistic


'fill other variables
strCurSiteName = rsSiteDetails!SiteName
lngSiteArea = rsSiteDetails!SiteArea
lngPopulation = rsSiteDetails!Population
'datFFEStartDate = rsSiteDetails!FFEStartDate
strFunction = rsSiteDetails!Function
intPercentNewBuild = rsSiteDetails!PercentNewBuild

'SQL string to delete default site from tblProjSite
strDelProjSite = "DELETE * FROM tblProjSite WHERE ProjSiteID = " & intProjSiteID
*******************************************************
'strRecordNewSiteID = "INSERT INTO tblSiteDetailsTemp (NewSiteID) VALUES ('" & intNewSiteID & "')"
******************************************************
'create a new Site name

For counter = 1 To Me!txtQuantity.Value

strNewSiteName = strCurSiteName & "Temp" & counter

With rsSiteDetails
.AddNew
!SiteName = strNewSiteName
!SiteArea = lngSiteArea
!Population = lngPopulation
'!FFEStartDate = datFFEStartDate
!Function = strFunction
!PercentNewBuild = intPercentNewBuild
!IsDefault = False
.Update
End With
intNewSiteID = rsSiteDetails!SiteID.Value
*************************************************
'cnCurrent.Execute strRecordNewSiteID
*************************************************

With rsProjSite
.AddNew
!SiteID = intNewSiteID
!ProjID = Me!cboProjID.Value
!Quantity = 1
.Update
End With
'loop to add all rooms in the original default site to the
'new ones

rsSiteRoom.MoveFirst
While Not rsSiteRoom.EOF

If rsSiteRoom!SiteID = intSiteID Then
intRoomID = rsSiteRoom!RoomID.Value
strDept = rsSiteRoom!Dept.Value
intRmNum = rsSiteRoom!Quantity
With rsSiteRoom
.AddNew
!SiteID = intNewSiteID
!RoomID = intRoomID
!Dept = strDept
!Quantity = intRmNum
.Update
End With

Else: rsSiteRoom.MoveNext

End If
rsSiteRoom.MoveNext
Wend


Next counter

cnCurrent.Execute strDelProjSite
**************************************************
'put some kind of query in the next line telling it to only
'show records that have matching record in
'tblSiteDetailsTemp (i.e. newly created records)
DoCmd.OpenForm "frmSiteDetails",,,
****************************************************
DoCmd.GoToRecord , "frmSiteDetails", acLast
Forms!frmSiteDetails!txtSiteName.SetFocus


rsSiteDetails.Close
cnCurrent.Close
Set rsSiteDetails = Nothing
Set cnCurrent = Nothing

End If

DoCmd.Close acForm, "frmProjSite"

Exit Sub
Err_cmdDefineSites_Click:

If Err.Number = 3021 Then
Resume Next
Else

MsgBox Err.Description & Err.Number, , "Jarvis FFE Database"
Exit Sub
End If

End Sub

 

[stickers]

I've now found another way around this - but it would still be useful to know how to get a loop to throw out an Autonumber every time it creates a new record...