?
Solved

Append Records to Temporary Table Using the For Loop

Posted on 2012-03-26
4
Medium Priority
?
470 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 2000 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 48

Expert Comment

by:Dale Fye
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

762 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