Solved

Append Records to Temporary Table Using the For Loop

Posted on 2012-03-26
4
458 Views
Last Modified: 2012-08-14
I need to loop through a selected group of records and append them to a temporary table. However, each record within the set is to be broken down further into the temporary table based on a number field with a For i = fldNumber statement.  I have attached the code to help out. Presently, this code loops through the recordset (x) amount of times but only appends the first record's values (x) amount of times, instead of looping through all of the (x) values and appending those to the temporary table. Thanks in advance.
[
] 

Dim sSQL As String
Dim strSet As String
Dim db As Database
Dim Rst As Recordset

    strSet = "SELECT tblTemplateDetail.TemplateDetailID " & _
        "FROM tblTemplateDetail " & _
        "WHERE tblTemplateDetail.TemplateID = " & [Forms]![frmTemplateDetail]![frmTemplateDetailSub]![TemplateID] & _
        " AND tblTemplateDetail.Code = ""S"" ORDER BY tblTemplateDetail.TemplateDetailID ;"
        
    Set db = CurrentDb
    Set Rst = db.OpenRecordset(strSet)
    
    Do Until Rst.EOF
    With Rst
    
    For i = 1 To Me.frmTemplateDetailSub!Sets
    sSQL = "INSERT INTO [tblStrengthTemp] (SetNumber, SetsRep, SetsWeight, SetsTime, MaxPercent, SetsRepID) " _
            & "VALUES ('" & i & "', '" & (Me![frmTemplateDetailSub]![Reps]) & "', '" & (Me![frmTemplateDetailSub]![Weight]) _
            & "', '" & (Me![frmTemplateDetailSub]![Time]) & "', '" & (Me![frmTemplateDetailSub]![Max]) & "', '" & (Me![frmTemplateDetailSub]![TemplateDetailID]) & "')"
    CurrentDb.Execute sSQL
    Next i
    Rst.MoveNext
    End With
    Loop
    DoCmd.RunCommand acCmdSaveRecord


Rst.Close
Set Rst = Nothing
 

Open in new window

0
Comment
Question by:skennelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 21
ID: 37769694
You Insert table make mainly form reference (see be;ow)

   
         sSQL = "INSERT INTO [tblStrengthTemp] (SetNumber, SetsRep, SetsWeight, SetsTime, MaxPercent, SetsRepID) " _
            & "VALUES ('" & i & "', '" & (Me![frmTemplateDetailSub]![Reps]) & "', '" & (Me![frmTemplateDetailSub]![Weight]) _
            & "', '" & (Me![frmTemplateDetailSub]![Time]) & "', '" & (Me![frmTemplateDetailSub]![Max]) & "', '" & (Me![frmTemplateDetailSub]![TemplateDetailID]) & "')"

Open in new window


You are not using any fields from the recorddet in your insert (append) statement.

What is the point of the record set loop?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 37769942
If I'm understanding your code right I'm assuming you want to populate a temp table with a certain number of sets for each ID.  If that is the case, your Do-Until-Loop is not making use of the recordset field.  The TemplateDetailID should be coming from your recordset instead of your form.  Here a revised loop that should work:

    Do Until Rst.EOF
        For i = 1 To Me.frmTemplateDetailSub!Sets
            sSQL = "INSERT INTO [tblStrengthTemp] (SetNumber, SetsRep, SetsWeight, SetsTime, MaxPercent, SetsRepID) " _
                    & "VALUES ('" & i & "', '" & (Me![frmTemplateDetailSub]![Reps]) & "', '" & (Me![frmTemplateDetailSub]![Weight]) _
                    & "', '" & (Me![frmTemplateDetailSub]![Time]) & "', '" & (Me![frmTemplateDetailSub]![Max]) & "', '" & (Rst![TemplateDetailID]) & "')"
            CurrentDb.Execute sSQL
        Next i
        Rst.MoveNext
    Loop 

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37771264
How large can your "Sets" field get?

Rather than using a loop, I would create a separate table (tblNumbers) with a single field (intNumber), with values from 1 to N (where N is the max value of your Sets field).  Then I would modify the query that is currently inside the loop to a INSERT INTO ... SELECT syntax, where the SELECT statement uses tblNumbers and field intNumber to fill in the value of the "SetNumber", something like.

strSQL = "INSERT INTO [tblStrengthTemp] (SetNumber, SetsRep, SetsWeight, SetsTime, MaxPercent, SetsRepID) " _
            & "SELECT '" & intNumber & "', '" _
                                & Me![frmTemplateDetailSub]![Reps]) & "', '" _
                                & Me![frmTemplateDetailSub]![Weight] & "', '" _
                                & Me![frmTemplateDetailSub]![Time] & "', '" _
                                & Me![frmTemplateDetailSub]![Max] & "', '" _
                                & Rst![TemplateDetailID]) _
             & " FROM tblNumbers WHERE intNumber <= " & me![frmTemplateDetailSub]!Sets
0
 

Author Closing Comment

by:skennelly
ID: 37771349
Thank you. I was able to adapt a solution through your code. I wanted to populate the temp table with a certain number of sets and also use all the fields in the recordset. So the code adaptations were as follows:

'Used all the fields in the recordset for the update, not just the TemplateDetailID
    strSet = "SELECT * " FROM tblTemplateDetail " & _

and
'Used the recordset instead of the form to populate the fields in the Temp Table.
sSQL = "INSERT INTO [tblStrengthTemp] (SetNumber, SetsRep, SetsWeight, SetsTime, MaxPercent, SetsRepID) " _
                    & "VALUES ('" & i & "', '" & (Rst![Reps]) & "', '" & (Rst![Weight]) _
                    & "', '" & (Rst![Time]) & "', '" & (Rst![Max]) & "', '" & (Rst![TemplateDetailID]) & "')"

Thanks again.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question