Solved

Append Records to Temporary Table Using the For Loop

Posted on 2012-03-26
4
455 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

773 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