Reset values to default values

[DJ44]

I have a database that, among other things will calculate the score of a site based on attributes of that site and the weighting factors assigned to each attribute.

The db allows the user to change the weighting factors at will and re-calculate the score. I am having trouble with the syntax of ADO recordsets and I was hoping someone could set me straight.

Here is the code I have for a module:

Public Sub HabValDefault(sTableName As String)

 On Error GoTo ErrorHandler
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim FactVar As Long
    Dim FactDefault As Long
        
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    
    
    Set cnn = CurrentProject.Connection
    
    rst.Open sTableName, cnn
       
    
    With rst
        Do While Not .EOF
            
            FactDefault = rst![dfltFactBlue]
            Set rst![FactBlue] = FactDefault
            
            
            
            'Goto next record
            .MoveNext
        Loop
    End With
    
    'Close the Recordset and Connection Objects
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

Exit Sub
ErrorHandler:

 Set rst = Nothing
 Set cnn = Nothing
 Err.Clear
 
End Sub

So ... In the same table I have a field "FactBlue" which is the weighting factor used in the calculation (changable by the user), I have another field called dfltFactBlue, which holds the default value. What I want the Sub to accomplish is replace the value in the FactBlue field with the value in the dfltFactBlue field ---- for each record and save the table at the end of it (if rst.Close,etc does not do this??).

Thanks for your help.

DJ

 

[Fizzio]

you need to update the recordset before moving to the next record.

.....
rst![FactBlue] = rst![dfltFactBlue]
.update
......

 

[Jon K]

Change these two lines:-

rst.Open sTableName, cnn

Set rst![FactBlue] = FactDefault


to:-

rst.Open sTableName, cnn, adOpenDynamic, adLockOptimistic

![FactBlue] = FactDefault



But I think you can use an Update Query rather than using recordset. It's much easier.

 

[Fizzio]

Good point JonK - update query is easier.

 

[DJ44]

Thanks to Fizzio and Jon.

I implemented the changes in the code and it was successful.

I thought of an update query but this seems slightly easier for my application.

Cheers! DJ

 

[DJ44]

I have ran into a new but related problem.

I call the code through a command button on a form that allows the user to change the weighting factors.

The code does not run , I suspect because the table behind the form that has to be updated by the code is "open", or the focus is in one of the fields.

If I run the code from another form with the editing form closed, everything works fine.

What must I put in my calling code to allow the code to work from the editing form?

Thanks again.

DJ

 

[Fizzio]

As you rightly say, the form is 'hogging' the recordset. The 'easy' way is to set the locks on the form to No Locks but this can be a little risky. If you do this, you must make sure that you refresh the form following the recordset code.

A little less risky would be to call an external module with all the recordset code into it. You could then close the form, run the code and open the form again without changing your locks.

Another possible way (but I do not know if this will work as I don't have access to Access at the mo) is to dynamically set the locks eg

me.recordlocks = 0 'no locks I think

rest of recordset code

me.recordlocks = 2 'Edited record only I think

I would check the values in help though