VBA Help

[ssanfu]

Here is an example of how I would approach it. (Note: this before I saw the posts between you and June)

If the Pushups was "EXP" or "DNF", I returned 0. You can change that to "EXP" if you want.


(BTW, the dB is in A2000 format)

[ssanfu]

I looked at you dB... I have the query "PFT Query" working for PPoints and SPoints.
Also, I have the form "Quick Score" returning values for "Pushups" and "Situps".

It's late and I didn't get the others finished. Still have to combine the tables and modify the code.

I think you should be able to finish the changes. You seem to have a good handle on the VBA side.

If you have questions about what I have done or get stuck, post back.

[killermonkey]

Steve,

You're freaking awesome. I think I can handle the rest but thank you for going helping me out. If you have any good resources for actually learning VBA more in depth please pass it along. I'd like to get a handle on it and actually understand it.

Again thank you and if I run into anymore problems I know where to come.

Tyler

[June7]

If individuals will have multiple stat records, normalization would call for a table with individuals general info (ID, name, birthdate, gender, address, phone, email). Then the stat table would have only the ID as a foreign key. Customary handling of age would be to calculate when needed. Should have a date in the stat record and that can be used to calculate age at time of data collection.

[killermonkey]

Steve,

I've actually go another question for you if you don't mind helping me or at least pointing me in the right direction.

I'm trying to integrate the run points and walk test points into one table but the walk test isn't as simple.

I'll try and explain.

People performing the test will either do a 1.5 mile run or a 1 mile walk. With the run it's simple and similar to the pushups and situps. Age, gender and time it took them to run it determines their points.

The walk however is based off a Oxygen Volume (VO2) max which when paired with age and gender gives them their points.

The VO2 Max formula is as follows:

132.853-0.0769(Weight)-0.3877(Age)+6.315(Gender(males=1, females=0))-3.2649(Time)-0.1565(HR)

That formula gives a VO2 max which correlates in a table to a point value.

What I would like to do is incorporate this into the vba code like what you wrote for pushups and stiups. I've already copied that code and modified it to work with run times but I'm struggling to add in the walk portion.

This is what I would like to do and hopefully this makes sense:

Public Function RunPnts(Run As String, pAge As Integer, pGender As String, Atype As String, HR as Integer, Weight as Single) As String   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim sSQL As String
   Dim AgeRange As Integer
   Dim VMax as Integer
   Dim WalkTime as Single


   'check for exempt (EXP) or Did Not Finish (DNF)
   If Run = "EXP" Or Run = "DNF" Then
      RunPnts = 0
   Else


      Set dbs = CurrentDb


      'convert age to a range
      Select Case pAge
         Case 16 To 29
            AgeRange = 29
         Case 30 To 39
            AgeRange = 30
         Case 40 To 49
            AgeRange = 40
         Case 50 To 59
            AgeRange = 50
         Case Is > 60
            AgeRange = 60
      End Select

      If AType = "Run" Then

      
      sSQL = "SELECT Points FROM RunPoints"
      sSQL = sSQL & " WHERE Time = '" & Run & "' AND AGE = " & AgeRange & " AND Gender = '" & pGender & "' AND AerobicType = '" & AType "'"
      '      Debug.Print sSQL
           
      Elseif AType = "WALK" Then
         
      WalkTime = Time(Minutes) + Time(Seconds/60)
      VMax = 132.853-0.0769(Weight)-0.3877(Age)+6.315(Gender(males=1, females=0))-3.2649(WalkTime)-0.1565(HR)
      sSQL = "SELECT Points FROM RunPoints"      
      sSQL = sSQL & " WHERE Time = '" & Run & "' AND AGE = " & AgeRange & " AND Gender = '" & pGender & "' AND AerobicType = '" & AType "'"
      '      Debug.Print sSQL

      Set rst = dbs.OpenRecordset(sSQL)
      'check if records found
      If rst.BOF And rst.EOF Then
         'no records
         RunPnts = 0


         
         'records
         RunPnts = rst("Points")
      End If
  
      'clean up
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
   End If


End Function

I've attached the database so you can see the RunPoints Table that I'm trying to work with. I've only put in walk data for the 29 age group for the moment.

Thank you again in advance for your help

Database.zip

[ssanfu]

I don't have A2010 available right now. I'll have to look at it tonight.

When creating variables, watch out for reserved words. "Run" is a reserved word.

Public Function RunPnts(Run As String, pAge As Integer, pGender As String, Atype As String, HR as Integer, Weight as Single) As String

Notice how I named the variables. I use "p" as a prefix to tell me the variable is a "parameter" variable - something that gets passed to the function/sub.

Public Function RunPnts(pRun As String, pAge As Integer, pGender As String, pAtype As String, pHR as Integer, pWeight as Single) As String

I try to name my object to differentiate what they are. I have see where a field, a control and a variable in code had the same name...... very confusing!!

The VO2 Max formula is as follows:

132.853-0.0769(Weight)-0.3877(Age)+6.315(Gender(males=1, females=0))-3.2649(Time)-0.1565(HR)

Let me see if I understand the formula

VO2 =
132.853
minus
0.0769 times (Weight) (a portion of the weight)
minus
0.3877 times (Age) (a portion of the age)
plus
6.315 times 1 or 0 depending on the gender (Gender(males=1, females=0))
minus
3.2649 times (Time) (a multiple of the time)
minus
0.1565 times (HR) (a portion of the HR) (HR = heart rate??)

Then take the calculated VO2 (VMAX) and look up in the walk table?



PS - do you mind if I remove the "Solved" flag for now?

[ssanfu]

Whew, finally got the run/walk UDF to run without errors. The problem was that, for the walk records, some of the HR and/or the AerobicTime values were NULL - mostly when the AerobicType was "EXP", which caused a "!Type" error.

[Dal Jeanis]

"pAge"?

Ummm, Steve - isn't "Page" a reserved word also?

[ssanfu]

Uh...... yeah, I finally realized that, so I changed it to "piAge".
I wish I could come up with a reason why I did that........ but I got nothing.


"pAge" just didn't look like "page". I usually type in lower case so intellisense will change the variable/command/function to the correct case.
It just took me a while to see it.

[Dal Jeanis]

Just pullin your chain. Aircode is aircode - and the compiler always wins.

[killermonkey]

Steve,

thank you again for you help. Based off what you did with the coding it looks like I was kind not really on the right path but I would have no doubt pulled my hair out trying to figure it out. The select case and sSQL are new to me so I'm going to have to do some research to bring myself up to speed. Everything you've done is spot on so far. Now I just need to figure out how to calculate the scores with and without exemptions but I think I have a handle on that. I don't know how I can repay you but your expertise is greatly appreciated.

I'll mark it as solved for now hopefully I won't need to bother you again.

[killermonkey]

Ok I've got yet another question. I spend all day yesterday trying to come up with a viable solution but it's escaping me. I want to create a form where a user can enter new information while at the same time see a quick overview of how the month is looking. Basically I want a textbox that counts stuff like the number of people that score between a 90 and 100 (Excellent), the number that score between a 75 and 89.9 (Satisfactory) and the number of people that score below a 75 (Failing). Additionally I'd like to see total number of passing scores and failing scores, failures in certain categories ect ect...more or less something like this:



The problem I'm having is that dcount blows accesses mind. It cant seem to handle even one dcount without freezing everything.

What I'm wondering is there an alternative to dcount where I can achieve the same thing without the lagging headache?


Thanks in advance

Tyler

[Dal Jeanis]

Make a new thread - this will take some debug. DCount shouldn't cause that much of a problem.