>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
>
> Message posted via
http://www.accessmonster.com>