C
Cyberwolf0000 via AccessMonster.com
I have an issue where I am using 3 different SQL statements strSQL, strSQL1
and strSQLUp in a Do Loop:
strSQL = "Select OpCode " & _
"FROM R2ROpCodes " & _
"WHERE R2RPerfID=" & Me.R2RPerfID & ";"
Set rec1 = CurrentDb().OpenRecordset(strSQL)
Debug.Print rec1.RecordCount
rec1.MoveFirst
Do Until rec1.EOF
'Gets several fields for later calculations at the Job Number Op
Code level
strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS
CDIFF, Sum(TIMEDIFF) AS TDiff, " & _
"Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, " & _
"Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps, " &
_
"Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps,
Sum(MR3_TIME) AS MR3Time " & _
"FROM WEB_DEV_EVENT_SUMMARY_TRIM " & _
"WHERE PRODUCTION_ORDER_NUMBER='" & ZeroPad(strJobNum,
12) & "' AND OPERATION_CODE='" & _
rec1!OpCode & "';"
Set rec = CurrentDb().OpenRecordset(strSQL1)
If Err.Number = 3146 Then
Set rec = ODBCFail(strSQL1, Me.Name, Now())
ElseIf Err.Number = 3075 Then
Resume Next
ElseIf Err.Number > 0 Then
GoTo UpdateActuals_Error
End If
'If there is at least 1 record then update the 7 fields at the
OpCode level
If rec.RecordCount > 0 Then
'Loop through rec1 recordset and upate the 7 fields for each
OpCode
strSQLUp = "UPDATE R2ROpCodes " & _
"SET OpCodeImps=" & rec!NGI & _
" OMR=" & rec!MR1Time + rec!MR2Time
If rec!MR1Imps <> 0 Or rec!MR2Imps <> 0 Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR1Imps + rec!MR2Imps
ElseIf Not IsNull(rec!MR3Imps) Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR3Imps
End If
strSQLUp = strSQLUp & " NIMV=" & rec!NGI / (rec!TDiff - rec!
MR1Time - rec!MR2Time - rec!MR3Time) & _
" RDT=" & rec!NGT + (rec!TDiff - (rec!NGT + rec!
MR1Time + rec!MR2Time + rec!MR3Time)) & _
" VC=" & rec!MR3Time & _
" RWaste=" & (rec!CDIFF - (rec!MR1Imps + rec!
MR2Imps + rec!MR3Imps + rec!NGI)) / rec!CDIFF & ";"
End If
CurrentDb().Execute strSQLUp, dbFailOnError
Set rec = Nothing
strSQL1 = ""
strSQLUp = ""
rec1.MoveNext
Loop
End If
The StrSQL is used to get the record information to updating several other
records. This works fine
Within this I loop through each record updating its recordset using strSQL1
to pull the information from another table. I then use strSQLUp to update
the recordset. The first time it loops though everything works fine. any
subsequent loops when it gets to the OpenRecordset statement that use strSQL1
if fails giving an error 3075. The odd thing is that when I look at the
description of the error it is showing part of the SQL statement from the
first strSQLUp. An example would be like this
1st Loop:
strSQL1 ="SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2100';"
strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"
2nd loop
Debug.Print shows the strSQL1 statement as:
strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2200';"
When it tries to open the recordset I get the Error 3075 state Missing
operator in SQL statement and shows:
"195856 OMR=1.37 MRImps=5465 NIMV=36863.542254846602672689629211 RDT=5.313
VC=1.577 RWaste=3.09566765856745E-02;" as the SQL code.
I am not sure why this is happening. As you can see just before I loop I am
clearing the 2 SQL statements, and setting rec = Nothing.
I know that you shouldn't store calculated values, but I couldn't figure out
how to show the proper values for each on a continuous subform with more that
1 record. If that can be done maybe I need to go that way.
TIA,
--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2
and strSQLUp in a Do Loop:
strSQL = "Select OpCode " & _
"FROM R2ROpCodes " & _
"WHERE R2RPerfID=" & Me.R2RPerfID & ";"
Set rec1 = CurrentDb().OpenRecordset(strSQL)
Debug.Print rec1.RecordCount
rec1.MoveFirst
Do Until rec1.EOF
'Gets several fields for later calculations at the Job Number Op
Code level
strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS
CDIFF, Sum(TIMEDIFF) AS TDiff, " & _
"Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, " & _
"Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps, " &
_
"Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps,
Sum(MR3_TIME) AS MR3Time " & _
"FROM WEB_DEV_EVENT_SUMMARY_TRIM " & _
"WHERE PRODUCTION_ORDER_NUMBER='" & ZeroPad(strJobNum,
12) & "' AND OPERATION_CODE='" & _
rec1!OpCode & "';"
Set rec = CurrentDb().OpenRecordset(strSQL1)
If Err.Number = 3146 Then
Set rec = ODBCFail(strSQL1, Me.Name, Now())
ElseIf Err.Number = 3075 Then
Resume Next
ElseIf Err.Number > 0 Then
GoTo UpdateActuals_Error
End If
'If there is at least 1 record then update the 7 fields at the
OpCode level
If rec.RecordCount > 0 Then
'Loop through rec1 recordset and upate the 7 fields for each
OpCode
strSQLUp = "UPDATE R2ROpCodes " & _
"SET OpCodeImps=" & rec!NGI & _
" OMR=" & rec!MR1Time + rec!MR2Time
If rec!MR1Imps <> 0 Or rec!MR2Imps <> 0 Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR1Imps + rec!MR2Imps
ElseIf Not IsNull(rec!MR3Imps) Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR3Imps
End If
strSQLUp = strSQLUp & " NIMV=" & rec!NGI / (rec!TDiff - rec!
MR1Time - rec!MR2Time - rec!MR3Time) & _
" RDT=" & rec!NGT + (rec!TDiff - (rec!NGT + rec!
MR1Time + rec!MR2Time + rec!MR3Time)) & _
" VC=" & rec!MR3Time & _
" RWaste=" & (rec!CDIFF - (rec!MR1Imps + rec!
MR2Imps + rec!MR3Imps + rec!NGI)) / rec!CDIFF & ";"
End If
CurrentDb().Execute strSQLUp, dbFailOnError
Set rec = Nothing
strSQL1 = ""
strSQLUp = ""
rec1.MoveNext
Loop
End If
The StrSQL is used to get the record information to updating several other
records. This works fine
Within this I loop through each record updating its recordset using strSQL1
to pull the information from another table. I then use strSQLUp to update
the recordset. The first time it loops though everything works fine. any
subsequent loops when it gets to the OpenRecordset statement that use strSQL1
if fails giving an error 3075. The odd thing is that when I look at the
description of the error it is showing part of the SQL statement from the
first strSQLUp. An example would be like this
1st Loop:
strSQL1 ="SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2100';"
strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"
2nd loop
Debug.Print shows the strSQL1 statement as:
strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2200';"
When it tries to open the recordset I get the Error 3075 state Missing
operator in SQL statement and shows:
"195856 OMR=1.37 MRImps=5465 NIMV=36863.542254846602672689629211 RDT=5.313
VC=1.577 RWaste=3.09566765856745E-02;" as the SQL code.
I am not sure why this is happening. As you can see just before I loop I am
clearing the 2 SQL statements, and setting rec = Nothing.
I know that you shouldn't store calculated values, but I couldn't figure out
how to show the proper values for each on a continuous subform with more that
1 record. If that can be done maybe I need to go that way.
TIA,
--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2