Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

Append Records to Temporary Table Using the For Loop

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
skennelly
Asked:
skennelly
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
IrogSintaCommented:
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
 
Dale FyeCommented:
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
 
skennellyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now