Link to home
Start Free TrialLog in
Avatar of jkharodia
jkharodia

asked on

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


Avatar of bramsquad
bramsquad
Flag of United States of America image

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
Avatar of jkharodia
jkharodia

ASKER

I am new to vba how do i write a while loop outside of the function?
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
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.
ASKER CERTIFIED SOLUTION
Avatar of bramsquad
bramsquad
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial