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").Valu e * 2
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
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").Valu
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
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
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")
'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
ASKER
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.
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
If Name = B then Total Score = (tblTest!GreetingScore/2*4
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
While Not rs.EOF
CalcScore = rs.Fields("Greeting").Valu
'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