Re: How to run VBA code on all rows of a table


Unsure how to do that but I'm willing to try.

Right now I start each of the seven subroutines with

Private Sub SubName()
... lots of code
End Sub

What's involved in encapsulate the calculations into a function, and call
the function
in a query?

Do I just create a new section

Private Function()
... put in ALL the code from all seven subroutines
End Function

Is it that simple to encapsulate?

THEN how do I call the function in a query [assume it is an update query]

Thanks for your reply & assistance.
Jimbo213


"Douglas J. Steele" wrote:

Can you encapsulate the calculations into a function, and call the function
in a query?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Jimbo213" <Jimbo213@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D5BCCB25-0084-46BB-838F-8E3DF81D20AF@xxxxxxxxxxxxxxxx

Ok you asked for it =;)
Instead of simplified code here is the actual code from one of twenty-six
similar "chunks" of code

If IsNull(Me.CompanyBox) = False And IsNull(Me.Boundary) = False And
IsNull(Me.Interface_Status) = False And IsNull(Me.Owning_Project) = False
And
IsNull(Me.Project_s_SMC) = False Then
Me.FrameStatusEIA = 25
GoTo CK50
Else:
Needed = "For 25% you need Company & Boundary & Status & Project# &
SMC."
GoTo BAILOUT
End If

This code is part of a long subroutine. SubA in my example.
There are seven Subs [my example showed only three]

There is one button that calls SubA then SubB then ... Sub G
At the end, all seven sub-scores are computed
Then I compute an average ... and I'm done ... WITH THAT ROW

I'd like to know how to keep applying the seven subroutines to row1 then
row2 then row3 ... to the end of the file.

There's gotta be a way to do that.

Thanks for your reply & assistance.
Jimbo213


"Douglas J. Steele" wrote:

"Jimbo213" <Jimbo213@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1677F00A-7206-4CD9-8E25-78844CC1BC4D@xxxxxxxxxxxxxxxx

"Klatuu" wrote:
Tell me a bit about the scoring?

Here is a simplified example [where F# is the field number in a
record]

if F1 & F2 & F3 meet certain rules then ScoreA = computed value A
if F4 & F5 & F6 meet certain rules then ScoreB = computed value B
if F7 & F8 & F9 meet certain rules then ScoreC = computed value C
OverallScore = (ScoreA + ScoreB + ScoreC)/3

I have a button that computes this for every row I'm on.

I have 250 rows in my master table.
I'd like to have some sort of looping code or macro that would
essentially
"press the button" from the first row to the last row

This is actually one case where simplifying may not have helped. <g>

On the face of it, it looks as though you should be able to use an Update
query (or perhaps three Update queries), rather than looping through a
recordset using VBA. It's almost always better to use SQL than looping.
Of
course, it's possible that your actual calculations don't lend themselves
to
using SQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)







.