SQL, ADO and Order (1 Viewer)

[dynamictiger]

SQl and ADO and Order and ???????

I have been making a wizard for setting parameters order of appearance in my application.

The table structure is as follows:

tblPool
PoolID
Type
Construction
Sanitiser
pH Control

trelPool
RelPoolID
PoolID
TestID

tblTreatOrder
RelPoolID
Parameter
TestTreat
Order

There are quite a few supporting tables but they dont add clarity so we can omit them.

In the wizard on form one we set the attributes of the pool relating to the pool table, that is PoolType (Spa or Pool), Construction, Sanitiser and pH Control. I have added an option of ALL, ANY, ANY and ANY in respective order. ALL means either pool types in this case spa or pool, and ANY means that the parameter we are testing for should be present for all constructions and all sanitisers and all pH Controls.

So far so good. This works well. I feed this data back to a class to process as we move through the wizard.

The problem I am thinking about and wondering aloud how to approach is this;

In the case the user selects a specific pool with a specific pH and specific sanitiser and a specific construction they may want to alter the order of parameters.

This form as it stands allows this.

However, if you then revist the all and any options as laid out above the parameters are reordered when we return to the specific example above. To try and make this clearer.

Attributes
Pool
Concrete
Liquid Chlorine
HCL Acid

Order Parameter
1 FAC
2 pH
3 TA

Attributes
ALL
ANY
ANY
ANY

Order Parameter
1 pH
2 FAC
3 TA

Attributes
Pool
Concrete
Liquid Chlorine
HCL Acid

Order Parameter
1 pH
2 FAC
3 TA


Which is not what we want to happen.

I think this error is caused by the way I save the order from the parameters. Following is the code I am currently using.

'Messages Sub
'Related Tables:
'tblTreatOrder
'trelPoolTest
'tlkupParameter
'tlkupTestTreat
'trelOutcome
'tblPoolTarget
'SubTables:
'trelOutcome -tblMessages, tblChemName, tlkupDirection
'trelPoolTest -tblPool, tlkupTestGroup
'tblTreatOrder -tlkupTestTreat, trelOutcome
'tblPoolTarget -tblTreatOrder, tlkupMath, trelOutcome
'Comments:
'The messages are added to tblMessages by the form in the wizard form itself, no action is required from this sub to add the messages.  Rather this sub grabs the msgID from the combo box and this is all the information required.
'Likewise this sub grabs the information from the math combo box, the target value and the parameter selected.
'Fields:
'cboP - Column 0 is the ID of the parameter
'cboM - Column 0 is the ID of the math to apply
'txtT - is the target value at which this message fires
'txtM-Column 0 is the ID of the message
'Order is dictated by the order of appearance on the form this is available from intI
'Comment:
'There are several interrelated tables involved in this sub.  The core tables are trelPoolTest
'First action in the sub is to check we have an entry in trelPoolTest, that is that the pool and selected test group are available, if they aren't we need to add them.
'CODE____________________________________________________________
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngID As Long

    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT * FROM trelOutcome WHERE Msg = " & Me.prpTMsg & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
       
    If Me.prpTParam = 0 Then
    
       If .EOF Then
    
        .AddNew
        
        End If
        
        .Fields("Chemical") = 0
        .Fields("msg") = Me.prpTMsg
        
        .Update
        
    Else
    
        If .EOF Then
        
        MsgBox "You cannot add a new message for this chemical here.  Please go back and add the " & _
                       "message at the message form. ", vbOKOnly + vbInformation, "No Message on file"
                       
         End If
         
    End If
                       
    'End If
    
    .Close
    
    End With
        
    
    strSQL = "SELECT * FROM trelPoolTest WHERE Pool = " & Me.prpPoolID & " AND Test = " & Me.prpPoolTestGroup & ";"
    
    'Call myCreateQuery(strSQL, "qzDebugAddMsg" & Me.prpPoolID & "-" & Me.prpPoolTestGroup)
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
            'We dont have a pool test set up.  Set one up now
            
            .AddNew
            
            .Fields("Pool") = Me.prpPoolID
            .Fields("Test") = Me.prpPoolTestGroup
            
            .Update
            
        End If
        
            lngID = .Fields("RelPoolID")
            
        .Close
        
       
        
    End With
    
'Once verified that the pool and test group are entered into
'trelPoolTest we can then add the parameter to tblTreatOrder.
'Required ID from trelPoolTest, ParameterID, TestType and  Order
'Once this information is added to tblTreatOrder,
'we then need the ID value from tblTreatOrder
'CODE________________________________________________________________

    strSQL = "SELECT * FROM tblTreatOrder WHERE PoolTest =" & lngID & " AND Parameter = " & Me.prpTParam & _
                    " AND Type = " & Me.prpTType & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
        .AddNew
        
        .Fields("PoolTest") = lngID
        
        End If
        
        .Fields("Parameter") = Me.prpTParam
        .Fields("Type") = Me.prpTType
        .Fields("Order") = Me.prpTOrder
        
        .Update
        
        lngID = .Fields("ID")
        
        .Close
        
    End With
    
'The ID value from tblTreatOrder is required for the tblPoolTarget
'as well as Math, TargetValue and Outcome
'With these values added we are Complete, and the messages are
'correctly added to the tables to enable searching by the automated pool report.
'CODE________________________________________________________________
 
    strSQL = "SELECT * FROM tblPoolTarget WHERE PoolParam = " & lngID & ";"
    
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    
    With rst
    
        If .EOF Then
        
            .AddNew
            
            .Fields("PoolParam") = lngID
            
        End If
        
            .Fields("Math") = Me.prpTmath
            .Fields("Tvalue") = Me.prpT
            .Fields("Outcome") = Me.prpTMsg
            
            .Update
            
            .Close
            
        End With
        
    Set rst = Nothing