Solved

Append Records to Temporary Table Using the For Loop

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now