Solved

How to repeat a routine for all records in the database.

Posted on 2013-11-03
19
292 Views
Last Modified: 2013-11-03
Greetings,
I have a button that runs a recalculation of some values for the current record.  I would like it to step through all the records and run the same recalculation, then return to the current record.  I remember how to do a Do while not EOF from the past, but how do I do it in VBA?

Here is the routine that needs to be repeated for each record;

Private Sub RecalculatePdaDominance_Click()
Call xSp1Pct_AfterUpdate
Call xSp6PCT_AfterUpdate
Call xSp16PCT_AfterUpdate
'Only need one of the species from each strata to trigger the recalculation
End Sub



Thanks,
Pat
0
Comment
Question by:MurphyPH
  • 12
  • 4
  • 3
19 Comments
 
LVL 21
Comment Utility
Pat, I would recommend using an Update query. Your query would select all the desire records to update.


If it were my project I owuld convert the three calculations to functions in a standard code module. That way they can be called from an update query.

Even better would be to NOT store the calculation at all. Just call the function to do the calculation as needed. This way it will always be correct.
0
 

Author Comment

by:MurphyPH
Comment Utility
What this is part of is an update query that changes all the records, but then I need something that will recalculate all the records.  I have converted a macro that updates many fields using the following code.  How do I get the other routines to go through all the records?

At the end of the following code is the RecalculatePDADominance routine that only runs for the current record and I need it to run for all records.  So if I make it a part of one of the other update queries maybe that will work.  I will try that.

Here is the code that glues together a string of update queries.  I still don't see how I can add an update query that will run some logic that actually has to go through a string of other routines - kind of complicated.

Private Sub IndicatorUpdateButton_Click()
DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSp1", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp2", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp3", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp4", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp5", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp6", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp7", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp8", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp9", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp10", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp11", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp12", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp13", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp14", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp15", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp16", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp17", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp18", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp19", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp20", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp21", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp22", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp23", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp24", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp25", acViewNormal, acEdit
    'Now update all the dominance and Prevalence calculations.
    Refresh
    Call RecalculatePdaDominance_Click
    DoCmd.SetWarnings True
    MsgBox "Successfully updated the indicator status for all records. ", vbOKOnly
0
 

Author Comment

by:MurphyPH
Comment Utility
I think I am looking for something like this, but I get errors and probably haven't set this up properly.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        Call xSp1Pct_AfterUpdate
        Call xSp6PCT_AfterUpdate
        Call xSp16PCT_AfterUpdate
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
you have to specify the table or query for the recordset in this line

Set rs = CurrentDb.OpenRecordset

i.e.,

Set rs = CurrentDb.OpenRecordset("tableName")
0
 

Author Comment

by:MurphyPH
Comment Utility
Thanks that helps, and seems to run without errors, but the changes are not being saved for each record so I need to track that down as well.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
Comment Utility
You wanting this to from in the  form's recordset Mode the form tot he first record, run the update and then mode tot eh next record in the form. Repeat until the last record has been update. Is that correct?

Note: This will be very slow.

If yes, then try:

Dim RecCount As Long
Dim Counter as Long

RecCount = Me.RecordCount

DoCmd.GoToRecord , , acFIRST

For Counter = 1 To RecCount


        Call xSp1Pct_AfterUpdate
        Call xSp6PCT_AfterUpdate
        Call xSp16PCT_AfterUpdate

        'Move to the next record. Don't ever forget to do this.
            If Counter < RecCount Then
                DoCmd.GoToRecord , , acNEXT
            End If
        Next Counter


MsgBox "Finished looping through records."

Open in new window


WARNING: Be sure to test on a backup copy of your back end!
0
 

Author Comment

by:MurphyPH
Comment Utility
THTC,
thanks, but I think the code in the previous posts does the same thing.  My problem now is that as it steps through the records, it isn't saving the calculations.  I have a Do.Save at the end of the routine and perhaps I am putting the calculations in the controls  but they are not saved to the tables.

Everything is saved in the first record, but not in subsequent records.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
with your codes

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True

        Call xSp1Pct_AfterUpdate  'How does this procedure affects the current record ?


        Call xSp6PCT_AfterUpdate  'How does this procedure affects the current record ?
        Call xSp16PCT_AfterUpdate  'How does this procedure affects the current record ?

        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
0
 

Author Comment

by:MurphyPH
Comment Utility
Each of those runs some logic that fills in some numeric controls and some checkboxes.   I have a button that just runs this following code and it correctly updates the current record.  Perhaps the code I have is not really going to the next record?

I modified the call a little since I found the other just immediately jumped to the following:

Private Sub RecalculatePdaDominance_Click()
Call TreeUpdate
Call ShrubUpdate
Call HerbUpdate
'Only need one of the species from each strata to trigger the recalculation

End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:MurphyPH
Comment Utility
Here is the code as I currently have it.  I tried inserting a refresh, but thought that it would reset the recordset to 1, so removed it, and I tried a DoCmd.Save and it made no difference.
Private Sub IndicatorUpdateButton_Click()
DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSp1", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp2", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp3", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp4", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp5", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp6", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp7", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp8", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp9", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp10", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp11", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp12", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp13", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp14", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp15", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp16", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp17", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp18", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp19", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp20", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp21", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp22", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp23", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp24", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp25", acViewNormal, acEdit
    'Now update all the dominance and Prevalence calculations.
    Refresh
    Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("wetform")
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        Call TreeUpdate
        Call ShrubUpdate
        Call HerbUpdate
        'DoCmd.Save
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
Me.Refresh
    DoCmd.SetWarnings True
    MsgBox "Successfully updated the indicator status for all records. ", vbOKOnly


End Sub
0
 

Author Comment

by:MurphyPH
Comment Utility
This may be very important - I have 4 tables that are linked.  I am referencing the primary table WetForm in this code, but the actual changes happen to the WetVeg Table.  I assume the relationships still hold when the code is going through the recordset.  I tried using WetVeg, as well as a query that included the important fields from WetForm and WetVeg, but no change with any of those.
0
 

Author Comment

by:MurphyPH
Comment Utility
Ah ha, it isn't really looping.  I set a message box at the end of each loop that shows the record ID and it is the same all the time.  I have three records, and it loops three times, but it is on the same record.  What is going on with that?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
maybe the recordset you are opening is the problem.. check this part first
0
 

Author Comment

by:MurphyPH
Comment Utility
How do I do that? What do I check?
0
 

Author Comment

by:MurphyPH
Comment Utility
I used the code from THTC with some preface code to make it see the record set and that now works.  I wonder why the code  with the rs.MoveNext didn't work.  I will split the points.  Thanks all.  Here is the code that works.
Private Sub IndicatorUpdateButton_Click()
DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSp1", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp2", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp3", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp4", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp5", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp6", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp7", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp8", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp9", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp10", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp11", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp12", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp13", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp14", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp15", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp16", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp17", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp18", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp19", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp20", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp21", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp22", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp23", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp24", acViewNormal, acEdit
    DoCmd.OpenQuery "UpdateSp25", acViewNormal, acEdit
    'Now update all the dominance and Prevalence calculations.
    Me.Refresh
    
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("wetveg")
Dim RecCount As Long
Dim Counter As Long
RecCount = rs.RecordCount
DoCmd.GoToRecord , , acFirst

For Counter = 1 To RecCount
        Call TreeUpdate
        Call ShrubUpdate
        Call HerbUpdate
        'MsgBox ID1 & PlotID, vbOKOnly
        'Move to the next record. Don't ever forget to do this.
            If Counter < RecCount Then
                DoCmd.GoToRecord , , acNext
                'MsgBox ID1 & PlotID, vbOKOnly
            End If
        Next Counter
 
    DoCmd.SetWarnings True
    MsgBox "Successfully updated the indicator status for all records. ", vbOKOnly

End Sub

Open in new window

0
 

Author Closing Comment

by:MurphyPH
Comment Utility
Thanks.  I wonder why rs.MoveNext didn't work.
0
 
LVL 21
Comment Utility
I wonder why rs.MoveNext didn't work.
Because the recordset you were creating  had (absolutly) nothing to do with the form's recordset. Looping through (rs.Movenect)  the recordset did not change the recerd in the form. That is why it kept updating just the same record. d. The extra recordset was never changing the form's current record.

The line
Set rs = CurrentDb.OpenRecordset("wetveg")

Open in new window


could/should be removed.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you are updating the form's recordset, you only need this codes

with me.recordset
     .movefirst
     do until .eof
        Call xSp1Pct_AfterUpdate
        Call xSp6PCT_AfterUpdate
        Call xSp16PCT_AfterUpdate


     .movenext
     loop
end with
0
 

Author Comment

by:MurphyPH
Comment Utility
Thanks capricorn1,  way more efficient.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This collection of functions covers all the normal rounding methods of just about any numeric value.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now