mistux Site Admin
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Wed Oct 08, 2008 9:52 am Post subject: Loop through 1 Recordset and ans 2 Arrays |
|
|
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
|
|
|