iterate through database 4

[jagsdj]

Hello all...

I've been working on this for days and finally thought I'd post and see what other ideas I can get.

I have two tables:
table1:
station_ID DO BOD
1 5 <20
2 <10 6

table2:
station_ID DO DO_Commnet BOD BOD_Comment
1 5 (empty) 20 <
2 10 < 6 (empty)


I have the data in table1 and I need that data to be transferred to table2, but before it does I need to qc it and make sure if the fields contain &quot;<&quot; or &quot;>&quot;, then to parse out the &quot;>&quot; or &quot;<&quot; and put them in separate fields in table2.

Does anybody have any idea on how to go about doing this?

any and all help is greatly appreciated.

 

[jagsdj]

Ken S.

Yes, every record as all 40 parameters in it:

table1: date parameter1 parameter2 parameter3...parameter40

now, whether they have values in it or not is totally optional. if they don't have values in it, then it really dosen't matter that a check needs to be done for that particular parameter.

Yes, parameter1 is the naming convention for our discussions, real parameters are DO, BOD, PH, Zinc, Lead, etc.

The min/max value will never change since this is a number set by us. It is teh allowable units.

Did that clear up some issues? I'll be happy to be more thorough...if need be.

Thanks again!

 

[Eupher]

jags -

Here's a bit of code I hacked together. Basically it opens 2 recordsets: one from the main table based on the current record's primary key (station ID instead of date), and the other loads all the records from the parameters table. Then it iterates through all the fields in the first recordset and compares the values to the min and max values in the 2nd recordset, where the field name in the 1st recordset corresponds to the parameter name in the 2nd.

Private Sub btnValidate_Click()

Dim CurDB As DAO.Database
Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim SQLStmt1 As String
Dim SQLStmt2 As String
Dim fld As DAO.Field

SQLStmt1 = "SELECT * FROM tblTable1 WHERE Station_ID = " & Me!Station_ID
SQLStmt2 = "SELECT * FROM tblParameters"

Set CurDB = CurrentDb()
Set Rs1 = CurDB.OpenRecordset(SQLStmt1, dbOpenDynaset)
Set Rs2 = CurDB.OpenRecordset(SQLStmt2, dbOpenDynaset)

For Each fld In Rs1.Fields
    Rs2.FindFirst "[Parameter] = '" & fld.Name & "'"
    If Rs2.NoMatch = False Then     'if field name matches parameter name
        If Not IsNull(fld) Then     'if there is a value in the field
            If fld < Rs2![minVal] Or fld > Rs2![maxVal] Then    'if value is outside normal range
                MsgBox fld.Name & &quot; value is outside of normal range&quot;
                Else
                    MsgBox fld.Name & &quot; value is within normal range&quot;
            End If
        End If
    End If
Next fld

Rs1.Close
Rs2.Close
Set Rs1 = Nothing
Set Rs2 = Nothing
Set CurDB = Nothing

End Sub

Hope this gets you started in the right direction.

Ken S.

 

[jagsdj]

Eupher and J.McBeef

I want to thank you both for your patience and perservence in helping me to overcome my inadaquacy in VB. I have learned so much just from posts here and studying code, more than any class could've helped!

For this I thank you!

I converted the above code to ADO and thought I'd post here just in case anybody else could use a helping hand.

Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim rs5 As New ADODB.Recordset
Dim fld As ADODB.Field

If (fld.Name = rs3.Fields(&quot;ParameterID&quot) Then
If Not IsNull(fld.Value) Then
If (((fld.Value) >= rs3.Fields(&quot;QC_MIN&quot) And ((fld.Value) <= rs3.Fields(&quot;QC_Max&quot)) Then
rs4.Fields(fld.Name) = rs2.Fields(fld.Name)
rs4.Update
'MsgBox fld.Value
'MsgBox &quot;within range&quot;
Else
rs5.Fields(&quot;rowNum&quot = rowCount
rs5.Fields(&quot;FieldName&quot = fld.Name
rs5.Fields(&quot;FieldValue&quot = rs2.Fields(fld.Name)
rs5.Fields(&quot;ErrorDesc&quot = &quot;Value out of Range&quot;
rs5.Fields(&quot;FileName&quot = ExcelFileName
rs5.Update

'MsgBox fld.Value
'MsgBox &quot;outside range&quot;
End If
Else
rs4.Fields(fld.Name) = rs2.Fields(fld.Name)
rs4.Update
End If
rs3.MoveNext
End If