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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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







.

Relevant Pages

  • Re: tables inside a Query (Help!!!)
    ... if I have a Query named "MyQuery" and inside the query I'm ... Private mQueries As Variant ... Private mQueryName As String ... Private Sub GetQueries() ...
    (comp.databases.ms-access)
  • VB.NET 2005 - exception question
    ... Private Sub MyApplication_UnhandledException(ByVal sender As Object, ... The app will take what I have in the textbox aka SQL Query, ... Private pResults As New DataTable ... Private pQuery As String ...
    (microsoft.public.dotnet.general)
  • Re: difference between a private sub and a public function?
    ... Subroutines don't return anything. ... Functions or subs can be private or public. ... If a function or sub is public, then it can be seen by routines in other ... detailed questions are more likely to be ...
    (microsoft.public.excel.misc)
  • Re: How can a private sub send back a variable to the calling sub?
    ... Pass the parameters you want changed by your Sub to your Sub using ByRef rather than ... >subroutines, which are performed multiple times as the macro loops through ... the private subs attempt to perform various ... >replacements of text, and I know how to count the number of replacements ...
    (microsoft.public.word.vba.general)
  • [PATCH] scripts/kernel-doc: reorg code for readability
    ... followed by all subroutines, then ends with the main code execution. ... sub get_kernel_version{ ... -# separate source and object directories and for shadow trees. ...
    (Linux-Kernel)