Solved

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

Posted on 2013-11-03
19
306 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
ID: 39620351
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
ID: 39620357
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
ID: 39620373
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
ID: 39620385
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
ID: 39620418
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
ID: 39620427
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
ID: 39620433
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
ID: 39620456
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
ID: 39620464
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:MurphyPH
ID: 39620471
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
ID: 39620474
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
ID: 39620487
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
ID: 39620508
maybe the recordset you are opening is the problem.. check this part first
0
 

Author Comment

by:MurphyPH
ID: 39620510
How do I do that? What do I check?
0
 

Author Comment

by:MurphyPH
ID: 39620534
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
ID: 39620548
Thanks.  I wonder why rs.MoveNext didn't work.
0
 
LVL 21
ID: 39620656
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
ID: 39620690
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
ID: 39620712
Thanks capricorn1,  way more efficient.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

867 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

14 Experts available now in Live!

Get 1:1 Help Now