Solved

VBA calculate function using access table values

Posted on 2004-10-25
420 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
Question by:jkharodia
    5 Comments
     
    LVL 8

    Expert Comment

    by:bramsquad
    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
    I am new to vba how do i write a while loop outside of the function?
    0
     
    LVL 8

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    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 …

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now