We help IT Professionals succeed at work.

Loop thru code

Karen Schaefer
on
Looking for suggestions on how to loop thru the first recordset and determine if either of the following fields equal true, and I need to look at each value and update accordingly.

fieldnames: SOT, AC, OptLimit

I want to simplify the code to determine if the current record's SOT or AC or OptLimit fields = true and then update the those counterpart fields in the tblParameter_CriticalList.

In My current code I ran the first strsql for SOT then commented out because I thought I was only going to have to run the code once, no I need to automate the process.

Any suggestions toupdate the CriticalList that contains the unique value of ParamID and determine whether the AC, SOT or OptLimit fields need to equal true.

Thanks,

Karen
Function UpdateCritical()
    Dim strSQL As String
    Dim strSQL1 As String
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim nParam As String
    
    CurrentDb.Execute ("Delete * from tblParameter_CriticalList")
    
    CurrentDb.Execute ("INSERT INTO tblParameter_CriticalList ( [Parameter ID] )" & _
                    " SELECT TWD_ParamListOrignal.PARAMETER" & _
                    " FROM TWD_ParamListOrignal" & _
                    " GROUP BY TWD_ParamListOrignal.PARAMETER" & _
                    " ORDER BY TWD_ParamListOrignal.PARAMETER")

    strSQL = "SELECT *" & _
           " FROM tblParameter_CriticalList"
 '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>   
    Set rs = CurrentDb.OpenRecordset(strSQL)
        Do Until rs.EOF
            nParam = rs.Fields("[Parameter ID]")
          '  strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (SOT = -1)"
           ' strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (AC = -1)"
           'strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (OptLimit = -1)"
           
           Set rs1 = CurrentDb.OpenRecordset(strSQL)
            
            If rs1.RecordCount > 0 Then
                rs.Edit
                   'rs.Fields("SOT") = True
                   ' rs.Fields("AC") = True
                   ' rs.Fields("OptLimit") = True
                rs.Update
                rs.MoveNext
            End If
            rs.MoveNext
            
        Loop
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Open in new window

Comment
Watch Question

Eric FlammOwner

Commented:
First off, I would declare an Object variable of type database:
dim dbs as DAO.Database
set dbs=currentDB()

Open in new window

. That way, you don't need to reinstantiate the database (currentDB) every time.

Second, your tblParameter_CriticalList contains only 1 field ([Parameter ID]) - I'm not sure what the point of the Group By and Order By clauses is in the Insert statement. In fact, I don't think this is what you meant to do.

What is the structure of your ParamListOriginal table? Is this what you want to loop through? I think I'm going to need a bit more information before I can propose a solution.
NorieAnalyst Assistant

Commented:
What exactly do you have to update?

The code to check if the fields are true is straightforward, for example something like this.
If rst.Fields("AC") = True Then

       ' do something
End If

If rst.Fields("SOT") = True Then

      ' do somehing
End If

If rst.Fields("optLimits") = True Then
      ' do something 
End If

Open in new window


Is it the 'do something' bit you are having trouble with?
Karen SchaeferBI ANALYST

Author

Commented:
1.  The first step is to create a unique list of Parameter IDs
2.  Then based on this unique list of Parameters, update the tblParameter_CriticalList - to check in the
TWD_ParamListOrignal"  for all instances and determine if any of those records for the fields SOT, AC, OPTlimit = true and if so Update those fields in the tblParameter_CriticalList for that unique Parameter ID

Here is my latest attempt.

However, for somereason the update skips some records even thou when I validate my date the Paramater id in the critical table should have the SOT=True, AC=False, and OPTLIMIT = True.  Not sure why it is not looking at all the records from the TWD_ParamListOrignal and determine if any = True for all records per Parameter ID.

K
Function UpdateCritical()
    Dim strSQL As String
    Dim strSQL1 As String
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim nParam As String
    Dim nSOT As Variant
    Dim nAC As Variant
    Dim nOptLimit As Variant
    Dim curDB As DAO.Database

Set curDB = CurrentDb()

    curDB.Execute ("Delete * from tblParameter_CriticalList")
    
    
    curDB.Execute ("INSERT INTO tblParameter_CriticalList ( [Parameter ID] )" & _
                    " SELECT TWD_ParamListOrignal.PARAMETER" & _
                    " FROM TWD_ParamListOrignal" & _
                    " GROUP BY TWD_ParamListOrignal.PARAMETER" & _
                    " ORDER BY TWD_ParamListOrignal.PARAMETER")

    strSQL = "SELECT *" & _
           " FROM tblParameter_CriticalList"
    
    Set rs = curDB.OpenRecordset(strSQL)
        Do Until rs.EOF
            nParam = rs.Fields("[Parameter ID]")
            strSQL1 = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & ""
           Set rs1 = curDB.OpenRecordset(strSQL1)
                If rs1.Fields("SOT") = True Then
                    nSOT = True
                Else
                    nSOT = False
                End If
                If rs1.Fields("AC") = True Then
                    nAC = True
                Else
                    nAC = False
                End If
                If rs1.Fields("OptLimit") = True Then
                    nOptLimit = True
                Else
                    nOptLimit = False
                End If
            
            If rs1.RecordCount > 0 Then
                rs.Edit
                   rs.Fields("SOT") = nSOT
                   rs.Fields("AC") = nAC
                   rs.Fields("OptLimit") = nOptLimit
                rs.Update
                rs.MoveNext
            End If
            rs.MoveNext
            
        Loop

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
here is picture of sample data. sample pic
In this case the Parameter in the Critical table should have SOT= TRue, AC=True, OptLimit = true,

However, for some reason it skipped the Parameter ID when I ran my code.

K
NorieAnalyst Assistant

Commented:
I'll ask the obvious - have you stepped through the code and checked what's happening.

I think I'd pay close attention to what the values are in the fields and whether or not the match what you expect.
Eric FlammOwner

Commented:
I don't think you need to go through all this - you should be able to just insert into the CriticalList table from the Original table. Something like:
1) Delete * from Critical Table (to reset it)
2) Insert into Critical List (Parameter_Rec, Parameter ID, SOT, AC,OptLimit) Select ID, Parameter, SOT, AC, OptLimit from ListOriginal where ((SOT) or (AC) or  (OptLmit))

I'm not sure what the EU field in the CriticalList is, or how it gets populated.

My logic is just to select all the rows in the original list where any of the fields are true and insert the data into the Critical list. I assume the "ID" fields in both cases are autonumber surrogate keys, so you don't need to populate them (or check for uniqueness - they are primary keys, so they are unique by definition).
Karen SchaeferBI ANALYST

Author

Commented:
problem with this approach is that only the first instance of the Parameter ID from the Twd. updates the Critical list table and so not all instances of the Paramater ID from the TWD... table are looked at to determine all instances of the various fields in question.

I already attempted to do this by using query and I ran into the issue above issue.

K
Karen SchaeferBI ANALYST

Author

Commented:
so if you look at the picture sample the results should equal

sample 2
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Not quite clear on what your trying to accomplish, but a couple comments on the code:

<<    curDB.Execute ("INSERT INTO tblParameter_CriticalList ( [Parameter ID] )" & _
                    " SELECT TWD_ParamListOrignal.PARAMETER" & _
                    " FROM TWD_ParamListOrignal" & _
                    " GROUP BY TWD_ParamListOrignal.PARAMETER" & _
                    " ORDER BY TWD_ParamListOrignal.PARAMETER")
>>

  Add the optional argument dbFailOnError and some error handling any time you do .Execute

<<    strSQL = "SELECT *" & _
           " FROM tblParameter_CriticalList"
>>

  Anytime you use * for all fields, ask yourself if you truly need all the fields.  If not, list them seperately.


<<            strSQL1 = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & ""
>>

 PARAMETER is a reserved word in JET.  At the very least this should be:

            strSQL1 = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE [PARAMETER] = " & Chr(39) & nParam & Chr(39) & ""

 but I would change it and avoid it's use entirely.   And the last set of quotes is not required unless your really trying to add a zero-length string to the end because nParam might be NULL.  Should be:

            strSQL1 = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE [PARAMETER] = " & Chr(39) & nParam & Chr(39)

<<            If rs1.RecordCount > 0 Then>>

  This either is in the wrong place (why are you checking fields in rs1 before you've checked if records were returned?), or should be a check on rs (is there a record to update?).

  Last, I don't see why you can't accomplish this with SQL alone, but then I don't understand exactly what it is your doing.

Jim.
Eric FlammOwner

Commented:
Ahh - got it. So you have a nested loop:

Loop 1 - select distinct Parameter from Original List
Loop 2 - select * from Original List where Parameter=Parent Loop value
Action: update field(s) to true if same field in current row is true (never set back to false)

strSQL="Select distinct Parameter from TWD_ParamListOriginal"
rs1=dbs.openrecordset(strSQL)
strSQL="Delete from tblParameter_CriticalList"
dbs.execute(strSQL)
while not rs1.eof
strSQL="Insert into tblParameter_CriticalList([Parameter ID]) VALUES ('" & rs1("Parameter") & "')"
dbs.execute(strSQL)
  strSQL="Select * from TWD_ParamListOriginal where Parameter='" & rs1("Parameter") & "'" 'Using single quote delimiters for string 
rs2=dbs.openrecordset(strSQL)
while not rs2.eof
 strSQL="Update tblParameter_CriticalList SET SOT=" & rs2("SO"') & ", AC=" & rs2("AC") & ", OptLimit=" & rs2("OptLimit") & " WHERE [Parameter ID]='" & rs1("Parameter") & "'"
dbs.execute(strSQL)
rs2.movenext
wend
rs1.movenext
wend

Open in new window


If none of the parameter rows in the original list have a check mark, you will get a record in the Critical list with no check marks.

Karen SchaeferBI ANALYST

Author

Commented:
Let me restate what I am attempting to accomplish -

Yes I am aware of the Naming issues - but this is what I have to work with.

1.  Need to create a Unique list of Parameters and its detail from data that contains mulitples of the same Parameter ID's where the detail data may vary and I need to update the Critical table if any of the detail's SOT or AC or OptLimit = True.

Here is the table structure of the table I need to update.

SELECT tblParameter_CriticalList.[Parameter ID], tblParameter_CriticalList.[Parameter ID_Original], tblParameter_CriticalList.ParameterDescription, tblParameter_CriticalList.EU, tblParameter_CriticalList.SOT, tblParameter_CriticalList.AC, tblParameter_CriticalList.OptLimit, tblParameter_CriticalList.SensorPartNumber, tblParameter_CriticalList.[RemoteCIM ModulePartNumber], tblParameter_CriticalList.ParamReferenceDesignator, tblParameter_CriticalList.IDSMechanicalDrawingNumber
FROM tblParameter_CriticalList;

and here is the table structure from the first table "TWD_ParamListOrignal."  - Note this contains multiple of the same Parameter. - See picture above.

SELECT  TWD_ParamListOrignal.ID, TWD_ParamListOrignal.TWD, TWD_ParamListOrignal.PARAMETER, TWD_ParamListOrignal.SOT, TWD_ParamListOrignal.AC, TWD_ParamListOrignal.OptLimit
FROM TWD_ParamListOrignal;

So I need to update the single record in Critical table where all instances from the TWD_ParamListOrignal contain True value in any instance of the SOT, AC or Optlimit for the select Paramater.

I need to loop thru the TWD_ParamListOrignal. todetermine this fact and if so update the SOT, AC or Optlimit fields for the ONE record in the Critical table.

K
Eric FlammOwner

Commented:
See my previous comment/code - does what you ask, I think.
Karen SchaeferBI ANALYST

Author

Commented:
problem with the syntax of the update:

                    strSQL="Update tblParameter_CriticalList SET" & _
                            " SOT=" & rs2("SO"') & ", AC=" & rs2("AC") & ", OptLimit=" & rs2("OptLimit") & "" & _
                            " WHERE [Parameter ID]='" & rs1("Parameter") & "'""

I don't see how this will look at all instances per Parameter ID  then determine if any of the fields in question = true for all records prior to updating the single record in the  CriticalList table

K
Karen SchaeferBI ANALYST

Author

Commented:
sample
If I use the suggestions, then won't the Update query look at the first record return SOT, AC, OPTLIMIT = TRUE, But the next interation SOT = FALSE, AC = TRUE, OPTLIMIT = TRUE, Then the next SOT = TRUE AC = FALSE, OPTLIMIT = FALSE.

so the final results will always equal the last record in the list of distinct parameter. and not a consolidation of all possible answers.

K
BI ANALYST
Commented:
OK I figure it out by creating a separate check of record count for each of  the SOT, AC, & Optlimit.

Thanks to everyone for their assistance.

Karen
Function UpdateCritical()
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim nParam As String
    Dim curDB As DAO.Database

Set curDB = CurrentDb()

    curDB.Execute ("Delete * from tblParameter_CriticalList")
    
    'Creates the unique list of Paramater ID's in the Critical List table.
    curDB.Execute ("INSERT INTO tblParameter_CriticalList ( [Parameter ID] )" & _
                    " SELECT TWD_ParamListOrignal.PARAMETER" & _
                    " FROM TWD_ParamListOrignal" & _
                    " GROUP BY TWD_ParamListOrignal.PARAMETER" & _
                    " ORDER BY TWD_ParamListOrignal.PARAMETER")
    
    'Returns list of Critical Paramater IDs
    strSQL = "SELECT *" & _
           " FROM tblParameter_CriticalList"

    Set rs = CurrentDb.OpenRecordset(strSQL)
        Do Until rs.EOF
            nParam = rs.Fields("[Parameter ID]")
            strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (SOT = -1)"
             Set rs1 = CurrentDb.OpenRecordset(strSQL)
                   
                    If rs1.RecordCount > 0 Then
                        rs.Edit
                           rs.Fields("SOT") = True
                        rs.Update
                    End If
            
            strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (AC = -1)"
            Set rs1 = CurrentDb.OpenRecordset(strSQL)
            
                    If rs1.RecordCount > 0 Then
                        rs.Edit
                           rs.Fields("AC") = True
                        rs.Update
                    End If
             
            strSQL = "SELECT *" & _
                    " FROM TWD_ParamListOrignal" & _
                    " WHERE PARAMETER = " & Chr(39) & nParam & Chr(39) & " and (OptLimit = -1)"
            Set rs1 = CurrentDb.OpenRecordset(strSQL)
            
                    If rs1.RecordCount > 0 Then
                        rs.Edit
                            rs.Fields("OptLimit") = True
                        rs.Update
                    End If
            rs.MoveNext
            
        Loop

Open in new window

Eric FlammOwner

Commented:
My update works by processing each line in rs2, which is the collection of all records in the original set that have a given parameter value.However, you're right - the update needs to be more complex. SInce you figured it, I won't bother writing the code, but you could simply "OR" the values from the active record in the Critical List with the values from the current record in rs2 and use the result to update the critical list.
Top Expert 2014

Commented:
I recommend doing this entirely with SQL.

1. Join the two tables by the [Parameter ID] column.
2. Or the columns

The query should look something like this:
Update tblParameter_CriticalList Inner Join TWD_ParameterListOriginal 
On tblParameter_CriticalList.[Parameter ID] = TWD_ParameterListOriginal.[Parameter ID]
Set tblParameter_CriticalList.SOT = (tblParameter_CriticalList.SOT OR TWD_ParameterListOriginal.SOT), 
tblParameter_CriticalList.AC = (tblParameter_CriticalList.AC OR TWD_ParameterListOriginal.AC), 
tblParameter_CriticalList.OptLimit = (tblParameter_CriticalList.OptLimit OR TWD_ParameterListOriginal.OptLimit)

Open in new window

Top Expert 2014

Commented:
I made a couple of test tables in a test database and ran such a query.  It performed as expected.

In this test, I used different table names, but they would correspond as follows:
ORtest_Src = TWD_ParameterListOriginal
Ortest_Tgt = tblParameter_CriticalList


UPDATE ORtest_Src INNER JOIN Ortest_Tgt ON ORtest_Src.[Parameter ID] = Ortest_Tgt.[Parameter ID] 
SET Ortest_Tgt.SOT = [Ortest_Tgt].[SOT] Or [Ortest_Src].[SOT], 
Ortest_Tgt.AC = [Ortest_Tgt].[AC] Or [Ortest_Src].[AC], 
Ortest_Tgt.OptLimit = [Ortest_Tgt].[OptLimit] Or [Ortest_Src].[OptLimit];

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for kfschaefer1's comment http:/Q_27476777.html#37232086

for the following reason:

found the solution on my own. &nbsp;thanks to everyone for their assistances
Top Expert 2014

Commented:
@Karen

If you have a lot of rows in the TWD_ParameterListOriginal table, you may want to explore my SQL solution.  SQL would be several times faster than code.
Eric FlammOwner

Commented:
I object to closing this question without awarding points. At the very least, OP has taken my suggestion to create one reference to the database rather than creating new references for each query; furthermore, myself and other commenters provided several approaches which were superior to OP's original code, and which seem to have influenced OP's eventual solution.
Karen SchaeferBI ANALYST

Author

Commented:
thanks for your input, however, my solution resolved my issue with the updating of all possibilities of the SOT, AC, & OptLimit.
k
Karen SchaeferBI ANALYST

Author

Commented:
found the solution on my own