How to run VBA code on all rows of a table

[Jimbo213]

I have VBA code that "scores" each row in a table when I push a button.
It executes three sub-routines.
call subA
call subB
call subC

How can I make those three sub-routines run on all rows of the table?

I'd like the for-next type of code please.

Thanks,

 

[rm]

I can offer 1 (of many) suggestion. However the descirption here is a
bit vague


Dim strQuery As String

Dim objCnn As ADODB.Connection
Dim objRS As ADODB.Recordset

Set objCnn = New ADODB.Connection
Set objRS = New ADODB.Recordset

Set objCnn = Application.CurrentProject.Connection

strQuery = "SELECT stuff FROM mytable WHERE some condition
is ...;"

With objRS
.ActiveConnection = objCnn
.cursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Open strQuery

If Not (.BOF And .EOF) Then
.MoveFirst
' more code
Do Until .EOF 'not a for... next but it works
'do stuff
.MoveNext
Loop
Else
msgbox "we have no records"
End If
End With

objRS.Close
objCnn.Close

Set objCnn = Nothing
Set objRS = Nothing

 

[Klatuu]

Tell me a bit about the scoring?
It may be possible to do this by calling a function in an SQL query rather
than using recordset processing which is usually much slower.

 

[rm]

Mr. Hargis is correct. If you are after and aggregate then there is a
host of SQL aggregate functions available in Jet SQL

Avg Function
Count Function
First, Last Functions
Min, Max Functions
StDev, StDevP Functions
Sum Function
Var, VarP Functions

 

[Jimbo213]

:
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

Thanks for offering to help.

 

[Douglas J. Steele]

:
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.

 

[Jimbo213]

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

:
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.

 

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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

:
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.

 

[Jimbo213]

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

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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

:
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.

 

[Douglas J. Steele]

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

(no e-mails, please!)

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

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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


:


:
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.