[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBA calculate function using access table values

Posted on 2004-10-25
5
Medium Priority
?
429 Views
Last Modified: 2012-08-13
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


0
Comment
Question by:jkharodia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 12401193
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
 

Author Comment

by:jkharodia
ID: 12401346
I am new to vba how do i write a while loop outside of the function?
0
 
LVL 8

Expert Comment

by:bramsquad
ID: 12402712
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
 

Author Comment

by:jkharodia
ID: 12408696
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
 
LVL 8

Accepted Solution

by:
bramsquad earned 750 total points
ID: 12426932
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Simple Linear Regression

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question