First, any help would be greatly appreciated...
I am trying to have a dynamic query populate a recordset so that I can perform several sorting/selecting operations on the data.
Note: I took out two loops (Line and Position) because I was simply trying to get the Recordset / QueryDef operations to succeed for one case, which is yet to happen.
The Problem:
How can I actually execute the queries to ensure the recordsets are getting populated? As of now, they are not.
Any Thoughts??
And Thanks.
John
[Begin Code]
Option Compare Database
Function fn_Compile_Results()
On Error GoTo fn_Compile_Results_Err
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1")
'DoCmd.OpenQuery ("qry_tblLaborFinal_1_1")
'Clears any previous data from tables
Dim db As Database
Set db = CurrentDb()
Dim labortemp_rs As Recordset
Dim laborfinal_rs As Recordset
Dim demand_rs As Recordset
Dim laborcurrent_rs As Recordset
Dim labor_qdf As QueryDef
Dim demand_qdf As QueryDef
Set labortemp_rs = db.OpenRecordset("tbl_Labor_Temp")
Set demand_rs = db.OpenRecordset("tbl_DemandTemp")
Set laborfinal_rs = db.OpenRecordset("tbl_Labor_Final")
Set laborcurrent_rs = db.OpenRecordset("tbl_Labor_Current")
Set labor_qdf = db.CreateQueryDef()
Set demand_qdf = db.CreateQueryDef()
Dim pos_int As Integer 'Defines position counter
Dim shi_int As Integer ' Shift
Dim lin_int As Integer ' Line
Dim typ_int As Integer ' Type
Dim sta_int As Integer ' Status
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Dim demand As Integer
Dim current_table As String
Dim sch_emp() As Double
Dim ID As Integer 'Temp variables used to bridge temp table and final table
Dim Name As String
Dim Line As Integer
Dim Position As Integer
Dim Hours As Double
Dim grinder_sql As String
Dim machoper_sql As String
Dim inspect_sql As String
Dim packoper_sql As String
Dim package_sql As String
Dim boxroom_sql As String
Dim demand_sql As String
pos_int = 1
shi_int = 1
lin_int = 1
typ_int = 1
sta_int = 1
counter = 0
'Sets pre-defined SQL code to add each position's demand
grinder_sql = "tbl_Production_ProductCodes_Lines.Labor_Front_GrindOperators "
machoper_sql = "tbl_Production_ProductCodes_Lines.Labor_Front_BrdCkOperators "
inspect_sql = "tbl_Production_ProductCodes_Lines!Labor_Front_1stLay+tbl_Production_ProductCodes_Lines!Labor_Front_2ndLay+tbl_Production_ProductCodes_Lines!Labor_Front_FrzInsp+tbl_Production_ProductCodes_Lines!Labor_Front_FryRm+tbl_Production_ProductCodes_Lines!Labor_Front_OvenRm+tbl_Production_ProductCodes_Lines!Labor_Front_SpiralRm "
packoper_sql = "tbl_Production_ProductCodes_Lines!Labor_Back_LeadPackOperators+tbl_Production_ProductCodes_Lines!Labor_Back_PackingOperators "
package_sql = "tbl_Production_ProductCodes_Lines!Labor_Back_Insp+tbl_Production_ProductCodes_Lines!Labor_Back_Fastback+tbl_Production_ProductCodes_Lines!Labor_Back_Packer+tbl_Production_ProductCodes_Lines!Labor_Back_Pallet "
boxroom_sql = "tbl_Production_ProductCodes_Lines.Labor_Back_Box "
demand_sql = ""
'Begin Line Loop - Removed Temporarily
'Begin Position Loop - Removed Temporarily
'Initialize variables
ID = 0
Name = ""
Line = 0
Position = 0
Hours = 0
With labor_qdf 'Query w/ variables to count: line, position, and shift
.SQL = "INSERT INTO tbl_Labor_Temp ( EmpID, EmpName, Line, [Position], Hours ) " _
& "SELECT tbl_Employees.ID, tbl_Employees.Name, tbl_Employees_Attributes.Line, tbl_Employees_Attributes.PositionID, tbl_Employees.HoursWorked " _
& "FROM tbl_Employees_Attributes INNER JOIN tbl_Employees ON tbl_Employees_Attributes.ID = tbl_Employees.ID " _
& "WHERE (((tbl_Employees_Attributes.Line) = " & lin_int & ") And ((tbl_Employees_Attributes.PositionID) = " & pos_int & ") And ((tbl_Employees_Attributes.ShiftID) = " & shi_int & ") And ((tbl_Employees_Attributes.TypeID) = 1) And ((tbl_Employees_Attributes.StatusID) = 1)) " _
& "ORDER BY tbl_Employees.HoursWorked;"
'.Execute '--Currently Throws an Error
End With
i = 0
ReDim Preserve sch_emp(i)
With laborcurrent_rs
Do Until .EOF 'Populates temp array for comparison
sch_emp(i) = !EmpID
i = i + 1
ReDim Preserve sch_emp(i)
.MoveNext
Loop
End With
demand_sql = "INSERT INTO tbl_DemandTemp ( Demand )"
If pos_int = 1 Then
demand_sql = demand_sql & " SELECT " & grinder_sql
ElseIf pos_int = 2 Then
demand_sql = demand_sql & " SELECT " & machoper_sql
ElseIf pos_int = 3 Then
demand_sql = demand_sql & " SELECT " & inspect_sql
ElseIf pos_int = 4 Then
demand_sql = demand_sql & " SELECT " & packoper_sql
ElseIf pos_int = 5 Then
demand_sql = demand_sql & " SELECT " & package_sql
ElseIf pos_int = 6 Then
demand_sql = demand_sql & " SELECT " & boxroom_sql
Else
MsgBox ("Error: Pos_int variable tests.")
End If
demand_sql = demand_sql & "FROM tbl_Production_ProductCodes_Lines INNER JOIN tbl_InputDemand ON (tbl_Production_ProductCodes_Lines.Line = tbl_InputDemand.Line) AND (tbl_Production_ProductCodes_Lines.ProductCode = tbl_InputDemand.ProductCode) "
demand_sql = demand_sql & "WHERE (((tbl_InputDemand.Date)=[Forms]![Tools]![Tools_Date]) AND ((tbl_InputDemand.Line)= " & lin_int & ") AND ((tbl_InputDemand.Shift)=[Forms]![Tools]![Combo_Shift]));"
'MsgBox (demand_sql)
With demand_qdf
.SQL = demand_sql
'.Execute
End With
With demand_rs
demand = !demand
End With
j = 0
Do Until counter = demand
With labortemp_rs
Do Until j = i - 1
If sch_emp(j) = !EmpID Then
.Edit
!Hours = !Hours + 8
j = j + 1
Else
j = j + 1
End If
Loop
ID = !EmpID
Name = !EmpName
Line = !Line
Position = !Position
Hours = !Hours
.MoveNext
End With
With laborfinal_rs
.AddNew
!EmpID = ID
!EmpName = Name
!Line = Line
!Position = Position
!Hours = Hours
.Update
End With
ID = 0
Name = ""
Line = 0
Position = 0
Hours = 0
counter = counter + 1
'Need to flush array: sch_emp()
Loop
MsgBox ("Loop Complete!")
demand_sql = "" 'Clears demand_sql string
pos_int = pos_int + 1 'Increment position loop
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1") 'Clears Temp Labor Table
DoCmd.OpenQuery ("qry_tblDemandTemp_0") 'Clears Temp Demand Table
'End Position Loop
lin_int = lin_int + 1 'Increment line loop
'End Line Loop
'Outside Loops
DoCmd.OpenQuery ("qry_tblLaborCurrent_1_1")
DoCmd.OpenQuery ("qry_tblLaborCurrent_1_2")
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1")
DoCmd.OpenQuery ("qry_tblDemandTemp_0")
labortemp_rs.Close
laborfinal_rs.Close
demand_rs.Close
db.Close
fn_Compile_Results_Exit:
Exit Function
fn_Compile_Results_Err:
MsgBox Error$
Resume fn_Compile_Results_Exit
End Function
[End Code]
I am trying to have a dynamic query populate a recordset so that I can perform several sorting/selecting operations on the data.
Note: I took out two loops (Line and Position) because I was simply trying to get the Recordset / QueryDef operations to succeed for one case, which is yet to happen.
The Problem:
How can I actually execute the queries to ensure the recordsets are getting populated? As of now, they are not.
Any Thoughts??
And Thanks.
John
[Begin Code]
Option Compare Database
Function fn_Compile_Results()
On Error GoTo fn_Compile_Results_Err
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1")
'DoCmd.OpenQuery ("qry_tblLaborFinal_1_1")
'Clears any previous data from tables
Dim db As Database
Set db = CurrentDb()
Dim labortemp_rs As Recordset
Dim laborfinal_rs As Recordset
Dim demand_rs As Recordset
Dim laborcurrent_rs As Recordset
Dim labor_qdf As QueryDef
Dim demand_qdf As QueryDef
Set labortemp_rs = db.OpenRecordset("tbl_Labor_Temp")
Set demand_rs = db.OpenRecordset("tbl_DemandTemp")
Set laborfinal_rs = db.OpenRecordset("tbl_Labor_Final")
Set laborcurrent_rs = db.OpenRecordset("tbl_Labor_Current")
Set labor_qdf = db.CreateQueryDef()
Set demand_qdf = db.CreateQueryDef()
Dim pos_int As Integer 'Defines position counter
Dim shi_int As Integer ' Shift
Dim lin_int As Integer ' Line
Dim typ_int As Integer ' Type
Dim sta_int As Integer ' Status
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Dim demand As Integer
Dim current_table As String
Dim sch_emp() As Double
Dim ID As Integer 'Temp variables used to bridge temp table and final table
Dim Name As String
Dim Line As Integer
Dim Position As Integer
Dim Hours As Double
Dim grinder_sql As String
Dim machoper_sql As String
Dim inspect_sql As String
Dim packoper_sql As String
Dim package_sql As String
Dim boxroom_sql As String
Dim demand_sql As String
pos_int = 1
shi_int = 1
lin_int = 1
typ_int = 1
sta_int = 1
counter = 0
'Sets pre-defined SQL code to add each position's demand
grinder_sql = "tbl_Production_ProductCodes_Lines.Labor_Front_GrindOperators "
machoper_sql = "tbl_Production_ProductCodes_Lines.Labor_Front_BrdCkOperators "
inspect_sql = "tbl_Production_ProductCodes_Lines!Labor_Front_1stLay+tbl_Production_ProductCodes_Lines!Labor_Front_2ndLay+tbl_Production_ProductCodes_Lines!Labor_Front_FrzInsp+tbl_Production_ProductCodes_Lines!Labor_Front_FryRm+tbl_Production_ProductCodes_Lines!Labor_Front_OvenRm+tbl_Production_ProductCodes_Lines!Labor_Front_SpiralRm "
packoper_sql = "tbl_Production_ProductCodes_Lines!Labor_Back_LeadPackOperators+tbl_Production_ProductCodes_Lines!Labor_Back_PackingOperators "
package_sql = "tbl_Production_ProductCodes_Lines!Labor_Back_Insp+tbl_Production_ProductCodes_Lines!Labor_Back_Fastback+tbl_Production_ProductCodes_Lines!Labor_Back_Packer+tbl_Production_ProductCodes_Lines!Labor_Back_Pallet "
boxroom_sql = "tbl_Production_ProductCodes_Lines.Labor_Back_Box "
demand_sql = ""
'Begin Line Loop - Removed Temporarily
'Begin Position Loop - Removed Temporarily
'Initialize variables
ID = 0
Name = ""
Line = 0
Position = 0
Hours = 0
With labor_qdf 'Query w/ variables to count: line, position, and shift
.SQL = "INSERT INTO tbl_Labor_Temp ( EmpID, EmpName, Line, [Position], Hours ) " _
& "SELECT tbl_Employees.ID, tbl_Employees.Name, tbl_Employees_Attributes.Line, tbl_Employees_Attributes.PositionID, tbl_Employees.HoursWorked " _
& "FROM tbl_Employees_Attributes INNER JOIN tbl_Employees ON tbl_Employees_Attributes.ID = tbl_Employees.ID " _
& "WHERE (((tbl_Employees_Attributes.Line) = " & lin_int & ") And ((tbl_Employees_Attributes.PositionID) = " & pos_int & ") And ((tbl_Employees_Attributes.ShiftID) = " & shi_int & ") And ((tbl_Employees_Attributes.TypeID) = 1) And ((tbl_Employees_Attributes.StatusID) = 1)) " _
& "ORDER BY tbl_Employees.HoursWorked;"
'.Execute '--Currently Throws an Error
End With
i = 0
ReDim Preserve sch_emp(i)
With laborcurrent_rs
Do Until .EOF 'Populates temp array for comparison
sch_emp(i) = !EmpID
i = i + 1
ReDim Preserve sch_emp(i)
.MoveNext
Loop
End With
demand_sql = "INSERT INTO tbl_DemandTemp ( Demand )"
If pos_int = 1 Then
demand_sql = demand_sql & " SELECT " & grinder_sql
ElseIf pos_int = 2 Then
demand_sql = demand_sql & " SELECT " & machoper_sql
ElseIf pos_int = 3 Then
demand_sql = demand_sql & " SELECT " & inspect_sql
ElseIf pos_int = 4 Then
demand_sql = demand_sql & " SELECT " & packoper_sql
ElseIf pos_int = 5 Then
demand_sql = demand_sql & " SELECT " & package_sql
ElseIf pos_int = 6 Then
demand_sql = demand_sql & " SELECT " & boxroom_sql
Else
MsgBox ("Error: Pos_int variable tests.")
End If
demand_sql = demand_sql & "FROM tbl_Production_ProductCodes_Lines INNER JOIN tbl_InputDemand ON (tbl_Production_ProductCodes_Lines.Line = tbl_InputDemand.Line) AND (tbl_Production_ProductCodes_Lines.ProductCode = tbl_InputDemand.ProductCode) "
demand_sql = demand_sql & "WHERE (((tbl_InputDemand.Date)=[Forms]![Tools]![Tools_Date]) AND ((tbl_InputDemand.Line)= " & lin_int & ") AND ((tbl_InputDemand.Shift)=[Forms]![Tools]![Combo_Shift]));"
'MsgBox (demand_sql)
With demand_qdf
.SQL = demand_sql
'.Execute
End With
With demand_rs
demand = !demand
End With
j = 0
Do Until counter = demand
With labortemp_rs
Do Until j = i - 1
If sch_emp(j) = !EmpID Then
.Edit
!Hours = !Hours + 8
j = j + 1
Else
j = j + 1
End If
Loop
ID = !EmpID
Name = !EmpName
Line = !Line
Position = !Position
Hours = !Hours
.MoveNext
End With
With laborfinal_rs
.AddNew
!EmpID = ID
!EmpName = Name
!Line = Line
!Position = Position
!Hours = Hours
.Update
End With
ID = 0
Name = ""
Line = 0
Position = 0
Hours = 0
counter = counter + 1
'Need to flush array: sch_emp()
Loop
MsgBox ("Loop Complete!")
demand_sql = "" 'Clears demand_sql string
pos_int = pos_int + 1 'Increment position loop
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1") 'Clears Temp Labor Table
DoCmd.OpenQuery ("qry_tblDemandTemp_0") 'Clears Temp Demand Table
'End Position Loop
lin_int = lin_int + 1 'Increment line loop
'End Line Loop
'Outside Loops
DoCmd.OpenQuery ("qry_tblLaborCurrent_1_1")
DoCmd.OpenQuery ("qry_tblLaborCurrent_1_2")
DoCmd.OpenQuery ("qry_tblLaborTemp_1_1")
DoCmd.OpenQuery ("qry_tblDemandTemp_0")
labortemp_rs.Close
laborfinal_rs.Close
demand_rs.Close
db.Close
fn_Compile_Results_Exit:
Exit Function
fn_Compile_Results_Err:
MsgBox Error$
Resume fn_Compile_Results_Exit
End Function
[End Code]
