VBA calculate function using access table values

Hi

I have an access table which has a name filed and a score field.  I want to perform calaculations using the score field based on the value in the name field.  I have attempted to write the below in a Access module and return the value to a query.  However the below code only calculates for the first row and returns the same value for the rest.  Do i need to write a loop, i have tried writing several loops but no joy.  Please help!

Public Function CalcScore() As Integer

Dim db As Database
Dim rs As Recordset

Dim intCount As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest")

CalcScore = rs.Fields("Greeting").Value * 2

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
   
End Function


jkharodiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bramsquadCommented:
if you want to loop through the values, your going to need to do something like this

While Not rs.EOF
    CalcScore = rs.Fields("Greeting").Value * 2
    'run the query or do whatever you do with the data here
    rs.MoveNext
Wend

the only part to watch out for is if you are planning on getting the value from the return value of the function, then you would have to do a while loop outside of the function.  if you choose to keep the while loop inside the function, you are going to have to run your query within the function too, otherwise it wont cycle through all the values (itll just pass the last one).

~b
0
jkharodiaAuthor Commented:
I am new to vba how do i write a while loop outside of the function?
0
bramsquadCommented:
well, you would want to call the function within your loop

in your situation, it wouldnt make much sense however, becuase the only thing you would be doing is multiplying the value times two....so heres what i think you could do.

Depending on how you want to execute this query, you have options as to how you can go about creating the query to execute.  The easiest way I can think of would be to create a query and place your data in the 'criteria' section

Step 1:  Create your query (you might already know this)

Make your query, but in the criteria of choice you can add something like this

[Forms]![Form1]![TextBox1]

this will reference the Text Box named 'TextBox1' on the Form named 'Form1'

Step 2:  Create Your Module (with event handler)

If you didnt have this already, you need code to handle an event which will start this process.  For example, you could create a command button named "Command1" and add this code

'this code will execute when you click 'Command1'
Private Sub Command1_Click()
  'from here, you can call your function, and do whatever else you want to do
  CalcScore()
End Sub

Public Function CalcScore() As Integer
'create DAO connection
Dim db As Database
Dim rs As Recordset

'set connecion
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest")

'make sure its at the first value
rs.MoveFirst

'cycle through connection values
While Not rs.EOF
    'remember the TextBox?  set that here
    TextBox1.Value = Cstr(rs.Fields("Greeting").Value * 2)
    'run the query
    DoCmd.OpenQuery "qryYourQuery"
    rs.MoveNext
Wend

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
   
End Function

Step 3: Executing your query

If you look at the above code, you are executing your query in that.  What this does, is gets the data, places it in a textbox, then executes the query.  The query has to get the data from the textbox in order to execute, so it will execute a query with different criteria everytime.  

I hope its an append query, or something like that, becuase this loop wont do much good if it doesnt manipulate the data somehow.  

hope this helps

~b
0
jkharodiaAuthor Commented:
Let me start again....
I have a Access table which records scores for various criterias for a person. eg see below this is only part of it there are another 10 criterias.

eg tblTest
Name:          GreetingScore   CorrectAnswerScore   etc
A                        5                           4
B                        2                           3
C                        3                           2  
A                        0                           1
B                       1                            0  

What i want to do is calculate the total score for each person.  It is not easy as adding the GreetingScore and the CorrectAnswerScore as the calculation varies depending on the persons name.  Eg if Name is A then the Total Score for person A  will be  (tblTest!GreetingScore/2*3   +  tblTest!CorrectAnswerScore/2*3).
If Name = B then Total Score =   (tblTest!GreetingScore/2*4   +  tblTest!CorrectAnswerScore/2*2).

I originally created queries for each person which calculates the score, however I would like one query to calculate all the scores and this is not possible with a IIF statement hence i would like to write a vba module which my query can call and calculate the Total Score.

I hope this makes sense.
0
bramsquadCommented:
i made a program and posted it.

this is a really toned down version of what you have (with ten fields and all)

but i think youll get the picture of what im saying once you see the working project.

(also my bad, i added another loop i didnt think about until i started coding)

let me know

~b

http://www.geocities.com/drakefresh/scores.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.