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

Again, without specifics I can only give generic answers.

Your original statement was that

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

My recommendation would be to have 3 separate functions for each of the
three scores. Remember that fields in tables can be Null, so you're best off
using Variant as the data type in your parameter list:

Function ScoreA(F1 As Variant, F2 As Variant, F3 As Variant) As Long

If IsNull(F1) Or IsNull(F2) Or IsNull(F3) Then
ScoreA = 0
Else
' Put your calculations here...
ScoreA = ....
End If

End Function

Repeat for ScoreB and ScoreC.

In your query, you'd create a computed field by typing the following in an
empty cell on the Field row:

OverallScore: ScoreA(F1, F2, F3) + ScoreB(F4, F5, F6) + ScoreC(F7, F8, F9)

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


"Jimbo213" <Jimbo213@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:841C17EB-3695-4361-9624-BFE3AA57A168@xxxxxxxxxxxxxxxx

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!)









.