Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Loop through 1 Recordset and ans 2 Arrays

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
mistux
Site Admin


Joined: 25 Jun 2004
Posts: 1042
Location: South Bend, Indiana USA

PostPosted: Wed Oct 08, 2008 9:52 am    Post subject: Loop through 1 Recordset and ans 2 Arrays Reply with quote

This bit of code loops through one table and two Arrays to generate an SQL query statement then runs it to append data from several tables into one big detail table.


MyArray lists the actual table names that it loops through to get some data.

MyArrayMonth corresponds to the actual table names knowing that they represent months so it was easy to assing month numbers for use in the query.

What this did was to take all the defect codes for every part for a given month (all seperate tables) and append it to one combining detail table. Using a defect code table to go through all the specific fileds in that table.

Example of the Jan 2008 table:
Code:

Part Number   MA   S   MR   MP   L   BLW   BK   SRK   BKS   COR   CR   ...etc
12387   H      1               4                  3            5         2         
16191   S         1                4             2              6   1   





Code:

Dim strMYSQL As String
Dim myTable As String
Dim myDefectCode As String
Dim MyArray As Variant
Dim MyArrayMonth As Variant
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

MyArray = Array("Jan 2008", "Feb 2008", "March 2008", "April 2008", "May 2008", "June 2008", "July 2008", "August 2008", "September 2008")
MyArrayMonth = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
 
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("T_DefectCodes")
 
For x = 0 To 8    'for every month in the array
        'Debug.Print MyArray(x)
        myTable = MyArray(x)
        myMonth = MyArrayMonth(x)
       
        If rs1.RecordCount = 0 Then Exit Sub
        rs1.MoveFirst
          ' loop through each record in the first recordset
          Do Until rs1.EOF
                  myDefectCode = rs1!DefectCode_ID  'Get the data from the specific field from that recordset
                   
                    strMYSQL = "INSERT INTO T_DefectCountDetail ( Part_Pattern_ID, Machine, DefectCode_ID, DefectQty, DefectMonth, DefectYear )" & _
                    " SELECT [" & myTable & "].[Part Number], [" & myTable & "].MA," & "'" & myDefectCode & "' AS Defect, [" & myTable & "]." & myDefectCode & ", " & myMonth & " AS MyDate, 2008 AS myYear" & _
                    " FROM [" & myTable & "]" & _
                    " WHERE [" & myTable & "]." & myDefectCode & " Is Not Null"
                   
                  'Debug.Print strMYSQL
                 
                  'Run queries here
                    DoCmd.SetWarnings False
                        DoCmd.RunSQL strMYSQL
                    DoCmd.SetWarnings True
               
                    rs1.MoveNext
          Loop
Next x

rs1.Close
Set rs1 = Nothing
Set db = Nothing

Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group