VBA to update recordset

[StuLux]

This is probably fairly basic but despite searching I can't find some simple advice on how to do the following:

I have some VBA code that runs when a form is opened at a specific record - the code updates a field on the form depending on date values in other fields. This code works fine.

What I want to do now is to run the same code but for all records in the table and I don't understand how I write the code to step through each record. I envisage running this code when the database is opened to make sure all records are always up to date, if there is a better way of doing this then please let me know.

 

[SydneyGeek]

If you're updating the same field based on the same rules, use an Update query instead. It's much more efficient than looping through a recordset.

If you haven't used Update queries before, check out this tutorial.

Denis

 

[StuLux]

Denis

Thanks for the reply. I had thought about using an update query but the code I have built is very lengthy - thsi is it in VBA:

Public Function UpdatetxtCurrentPhase()
        If IsDate(Me.txtControl_Finish) _
        And (Me.txtControl_Finish) >= Nz(Me.txtImprove_Finish) _
        And (Me.txtControl_Finish) >= Nz(Me.txtAnalyse_Finish) _
        And (Me.txtControl_Finish) >= Nz(Me.txtMeasure_Finish) _
        And (Me.txtControl_Finish) >= Nz(Me.txtDefine_Finish) _
        And Nz(Me.txtImprove_Finish) < Now() _
        And Nz(Me.txtAnalyse_Finish) < Now() _
        And Nz(Me.txtMeasure_Finish) < Now() _
        And Nz(Me.txtDefine_Finish) < Now() Then
        Me.txtCurrentPhase = "Control"
                Else
                If IsDate(Me.txtImprove_Finish) _
                And (Me.txtImprove_Finish) >= Nz(Me.txtAnalyse_Finish) _
                And (Me.txtImprove_Finish) >= Nz(Me.txtMeasure_Finish) _
                And (Me.txtImprove_Finish) >= Nz(Me.txtDefine_Finish) _
                And Nz(Me.txtAnalyse_Finish) < Now() _
                And Nz(Me.txtMeasure_Finish) < Now() _
                And Nz(Me.txtDefine_Finish) < Now() Then
                Me.txtCurrentPhase = "Improve"
                        Else
                        If IsDate(Me.txtAnalyse_Finish) _
                        And (Me.txtAnalyse_Finish) >= Nz(Me.txtMeasure_Finish) _
                        And (Me.txtAnalyse_Finish) >= Nz(Me.txtDefine_Finish) _
                        And Nz(Me.txtMeasure_Finish) < Now() _
                        And Nz(Me.txtDefine_Finish) < Now() Then
                        Me.txtCurrentPhase = "Analyse"
                                Else
                                If IsDate(Me.txtMeasure_Finish) _
                                And (Me.txtMeasure_Finish) >= Nz(Me.txtDefine_Finish) _
                                And Nz(Me.txtDefine_Finish) < Now() Then
                                Me.txtCurrentPhase = "Measure"
                                        Else
                                        If (Me.txtDefineStart) > Now Or IsNull(Me.txtDefineStart) Then
                                        Me.txtCurrentPhase = "Not Started"
                                        Else
                                        Me.txtCurrentPhase = "Define"
                                        End If
                                End If
                        End If
                End If
        End If

I thought this would be quite difficult to replicate in a query - do you still think this is the best way?

 

[SydneyGeek]

If you rewrite the code to leave out the Me. notation, and replace Me.txtCurrentPhase = with UpdatetxtCurrentPhase, you should be able to call the function directly in the Update To row of the query grid.
Try making a copy of the function and altering it. That way, if it misbehaves you haven't lost anything. I can then show you how to loop through the recordset if you have to go that route.

Denis

Denis

 

[StuLux]

I hadn't relaised you could call a function in an update query! - obvious really but I had assumed you could only use Access built in functions. Thanks a lot for your help (again)!