Results 1 to 13 of 13
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Loop inside a Loop, basic help needed with framework for code.

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I dont think you need ANY loops. You should use queries.
    Build a query for your base data, then append the values to the table.
    The queries do the loops for you.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Let me see if I understand what you want;.

    You have a form where you will select 1 "property ID" and "Tax Year"
    You want to open recordset, "rsOuterLoop", on table "dbo_SPINs" filtered by "property ID" and "Tax Year"
    You will open recordset, "rsInnerLoop", on table "dbo_PropYRFilingNameLKUP" filtered by "spin ID"
    Append field data to table "dbo_SPYD"
    Loop "rsInnerLoop" until EOF
    Loop "rsOuterLoop" until EOF


    How is table "dbo_PropYRFilingNameLKUP" related to "dbo_SPINs"?


    If this is correct, not hard, just lots of code.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As ranman suggests, can probably be done with an SQL INSERT SELECT action statement that is executed with CurrentDb.Execute method.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    More Info

    Ranman: it is possible I am making this too difficult. A part of my question is am I over complicating this. Gotta think about that. My problem is time; not much.

    Steve: I think you are correct on almost all counts.

    You have an open form where you will select 1 "PropYrDetailID" and "Property ID" and "Tax Year" I can get these values from the form easy.

    You want to open recordset, "rsOuterLoop", on table "dbo_SPINs" filtered by "property ID" and "Tax Year" Yes, grab all the sPIN records by those parameters.

    You will open recordset, "rsInnerLoop", on table "dbo_PropYRFilingNameLKUP" filtered by "spin ID" No, not connected to db0_spin and is not filtered. It is only a lookup table. The final table (Append to Table) dbo_SPYD has a combo box whose source is from odbo_PropYRFilingNameLKUP it where the end user manually selects each value. It is not related to anything else it is a support table only supplying the five values AO Prop, AO Initial, AO Final, BOR Initial, BOR Final to the dbo_SPYD table. Customer wants to automate this so that the end user does not have to make all of the manual selections.

    The end user manually selects the Value to assign it to the SPYD record. They want to automate this process which is my challenge. rsInnerLoop would contain each of the five values. So the lookup table is a support table.

    Just for info

    dbo_Property table has PropertyID PK
    dbo_PropertyYearDetail has PYDID as PK and Property ID as FK
    dbo_SPIN has SPINID PK and PropertyID as FK
    dbo_SPYD has SPYDID PK, PYDID FK, PropertyID FK, SPINID FK

    Append field data to table "dbo_SPYD" Correct

    Loop "rsInnerLoop" until EOF Correct

    Loop "rsOuterLoop" until EOF Correct


    How is table "dbo_PropYRFilingNameLKUP" related to "dbo_SPINs"?

    See Table Relationships below.


    Click image for larger version. 

Name:	ER Diagram.JPG 
Views:	17 
Size:	77.5 KB 
ID:	20926


    Thanks Fred

  6. #6
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Hi June7. I think that is where I am trying to go. I would be using the record sets (I think) to pull the data together and then writing it to the final table with a SQL Insert Into query, in the code where all the record sets are built, using the output from the Recordsets. And this action would Append the Table. I don't know what a SQL Insert Select is. I am worried that I am over complicating something that might be done easier. I just don't know.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Simple example:

    CurrentDb.Execute "INSERT INTO table(field1, field2, field3) SELECT " & Me.ID & " AS ID, field2, field3 FROM tablename WHERE {some criteria}"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    I get what you're saying now. I would be using the recordsets to pull the data together and create the records. Your query above would need to pull different data from 4 tables, with different criteria, and combine the whole thing together and write it. I am assuming I can string all of this into one statement. Right at the moment that is well beyond my SQL skill set. As Ssanfu says "Not hard, just lots of code." So it also may be beyond my skill set. I hope not. I am really open to any ideas.

    Thanks Fred

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can use the query builder to build the correct SQL for pulling in the 4 tables. Once you get a query object that has the required dataset, can use it as the source for the INSERT SELECT or replicate the SQL statement in VBA code.

    VBA is not mandatory. An INSERT SELECT statement can be written in SQL View of query object and then run the object. As you note, regardless of approach, need to expand skills. This exercise will certainly accomplish that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    This was supposed to go to Ssnafu Steve. I sent it to Ranman

    Steve: it is possible I am making this too difficult. A part of my question is am I over complicating this. Gotta think about that. My problem is time; not much.

    Steve: I think you are correct on almost all counts.

    You have an open form where you will select 1 "PropYrDetailID" and "Property ID" and "Tax Year" I can get these values from the form easy.

    You want to open recordset, "rsOuterLoop", on table "dbo_SPINs" filtered by "property ID" and "Tax Year" Yes, grab all the sPIN records by those parameters.

    You will open recordset, "rsInnerLoop", on table "dbo_PropYRFilingNameLKUP" filtered by "spin ID" No, not connected to db0_spin and is not filtered. It is only a lookup table. The final table (Append to Table) dbo_SPYD has a combo box whose source is from odbo_PropYRFilingNameLKUP it where the end user manually selects each value. It is not related to anything else it is a support table only supplying the five values AO Prop, AO Initial, AO Final, BOR Initial, BOR Final to the dbo_SPYD table. Customer wants to automate this so that the end user does not have to make all of the manual selections.

    The end user manually selects the Value to assign it to the SPYD record. They want to automate this process which is my challenge. rsInnerLoop would contain each of the five values. So the lookup table is a support table.

    Just for info

    dbo_Property table has PropertyID PK
    dbo_PropertyYearDetail has PYDID as PK and Property ID as FK
    dbo_SPIN has SPINID PK and PropertyID as FK
    dbo_SPYD has SPYDID PK, PYDID FK, PropertyID FK, SPINID FK

    Append field data to table "dbo_SPYD" Correct

    Loop "rsInnerLoop" until EOF Correct

    Loop "rsOuterLoop" until EOF Correct


    How is table "dbo_PropYRFilingNameLKUP" related to "dbo_SPINs"?

    See Table Relationships below.

    Click image for larger version. 

Name:	ER Diagram.JPG 
Views:	17 
Size:	77.5 KB 
ID:	20927

  11. #11
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    June7 you sparked a question

    Quote Originally Posted by June7 View Post
    Can use the query builder to build the correct SQL for pulling in the 4 tables. Once you get a query object that has the required dataset, can use it as the source for the INSERT SELECT or replicate the SQL statement in VBA code.

    VBA is not mandatory. An INSERT SELECT statement can be written in SQL View of query object and then run the object. As you note, regardless of approach, need to expand skills. This exercise will certainly accomplish that.
    My biggest problem is it is one long statement. I am thinking I could break down the whole thing into several components. Use the Query Builder to create a query for each component. Run the Queries in sequence and pass the data back in VBA Variables in the vb. Then I could combine results into the final append query and write it to the table. I'm not fixated on recordsets but it just seems to make sense.

    It is kind of like the poor man's approach to writing a series of queries and dumping the results into temp tables, until you get your final result. But instead of temp tables, dump it back into vba to hold it. Then reassemble it to what I need. Am I out of my mind or would this be possible? If I can break it into parts I think I can handle it. I have Xanax! Is this reasonable?

    Thanks Fred

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure what you mean by 'dump it back into vba to hold it' - dump what, a single record of data, and dump where, into variables?

    At this point I don't understand your data well enough to advise specifics. We have attempted to convey concepts. Yes, VBA looping code can do it but if a single SQL action can, why go with VBA looping and its complexities? Which do YOU think will best suit your situation?

    Can a single SELECT query with appropriate joins and filter criteria return ALL (with the exception of identifier and date info that would be contributed by user input on form) data you want to save into other table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Thanks all for your help. I am going to mark this solved. Phred

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  2. Replies: 3
    Last Post: 04-17-2014, 08:37 PM
  3. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  4. Replies: 2
    Last Post: 11-15-2013, 09:58 AM
  5. Error Handling Loop...Stuck Inside!
    By Soule in forum Programming
    Replies: 4
    Last Post: 02-23-2012, 07:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums