Access 2007 with SQL Server 2008 Backend.
This is my first time building loops. I am trying to pull together the basic framework for the code.
I have several pieces of data from different forms and tables. I need to combine the data from these multiple sources using loops, and then Append the end results to a different table.
Get Data from: frm_PYD
PYDID PK
PropertyID FK
TaxYear
Get Data from Table: dbo_SPINs This would be outside loop Recordset1, grab all records until EOF.
spin ID PK
PropertyID FK
Several other fields
Get Data from Table: dbo_PropYRFilingNameLKUP This would be inside loop Recordset2, grab all records until EOF.
1 AO Prop
2 AO Initial
3 AO Final
4 BOR Initial
5 BOR Final
So For each SPIN it creates 5 records with values. (1 PYDID record X 60 SPIN Records x 5 All Lookup Values = 300) new records appended to dbo_SPYD for one TaxYear
Fields appended to Table dbo_SPYD would look like:
(SPYDID),PYDID, PropertyID, SPINID, TaxYear, + other fields of information such as County, etc.
Outside Recordset1: Grabs each SPIN record sequentially until EOF = example of 60 records
Inside Recordset2: Adds 5 records one LOOKUP value, AO Prop AO Initial, AO Final, BOR Initial, BOR Final until EOF
I found this example of a loop-in-a-loop from John Spenser MVP 2010. My goal is to build a framework of loops that allows
the number of SPINS to vary over time, and the number of Lookup categories to vary over time so it never has to be hardcoded. I am not sure this sample does the trick but it is the closest example I could find.
Private Sub x()
Dim bReturnto As Variant
Dim r As DAO.Recordset
Set r = Me.RecordsetClone
bReturnto = Me.Bookmark 'Remember where we were, so we can comeback here
If Me.Dirty then Me.Dirty = False 'Force any changes to be saved
If r.RecordCount > 0 Then
r.MoveFirst
While Not r.EOF
Me.Bookmark = r.Bookmark
'Docmd.SendObject ...
r.MoveNext
Wend
End If
Me.Bookmark = bReturnto
End Sub
Help me build a framework for these loops. Please be verbose. Some examples are so cryptic I can't understand them.
Some code has stuff like dim ADODB, or dim connections, etc. I am not sure what is necessary.
Thanks,
Phred