Assigning values with VBA

[Pharcyde]

Good morning all!

I am desperately in need of somebody's help, as my VB is weak and needs a lot of work!

I have a form which holds student marks. They have 3 attempts for each exam, a final mark, and a final result.

The table that holds these values has them down as:


Mark
Mark2
Mark3 - Marks between 0-100
MarkFinal - determined by Mark - Mark3
ResultFinal - Combination of Passes and Fails, depending on MarkFinal

Heres where I cant get the form working properly. I'm sure its basic but cant get it right ! ! !

MarkFinal should be the value of the last exam taken, i.e if Mark & Mark2 have been taken, MarkFinal is Mark2...

Result Final is 'P' (Pass) if the result is over 50, 'F' if under, 'PR' (Pass resit) if a resit is taken, etc etc...

I've tried using 'If' statements and the like but to no avail so I must be doing it wrong.

If anyone could help I'd be very very greatfull!!!

Cheers, LeeBoy

P.S - the marks are already in so is there a way of invoking it other than 'after update' - so that it will update all marks that are already there?

 

[Mile-O]

It's still - after months - not normalised.

 

[namliam]

Well, from your explanation your MarkFinal and ResultFinal are CALCULATED RESULTS from your marks fields.

Rule #1: Never store calculated results.

Also, what if in the future you need to accomidate 4 marks? It might be smart to "Normalize" this, putting the marks in a related table instead of next to eachother. This way, if someone only takes 1 test and passes... You dont have 2 Null/Empty fields. This is considered "good practice" and i think a MUST...

Let me know which way you are going, either way needs a different solution...

Greetz

 

[Pharcyde]

It's still - after months - not normalised.

Mile-O - Great memory buddy!!!

This is a different database tho! I inherited it when I came to work here and Its over 150Mb with mucho critical data on it so dont wanna be losing it just before the examboard!

The one you helped me with is all done, works fine, and the girls who use it absolutely love it!! Cheers very much for that!

 

[Pharcyde]

I see what you mean about Normalization namliam, its a stopgap measure at the moment, because the examboard is a week today..!

Calculated Results? I'll trust you on that one! I thought it was just a case of conditional statements in the code.as in, 'take the last taken mark, copy it here, etc, etc...

Sooo, I'd like to go the first way please!

 

[namliam]

Something like:

Iif(Isnull([Mark3]);iif(isnull([mark2]);[mark];[mark2]);[Mark3])

will return the latest mark.

I think you will have to use a function to get a nice string return... Iif would get complex....

Function Something(Mark1 As Variant, Mark2 As Variant, Mark3 As Variant) As String
    If Mark3 > 50 Then
        Something = "PR2"
    ElseIf Mark2 > 50 Then
        Something = "PR"
    ElseIf Mark1 > 50 Then
        Something = "Pass"
    Else
        Something = "Fail"
    End If
End Function

Greetz & Hope it is what your looking for.

The Mailman

 

[Pharcyde]

Greetz namliam!

Cheers muchly for all the help, I got email notification that you'd replied just as I managed to complete it!!!!

I'm bound to forget it in the future so this post will deffo be helpful to me.

Once again, thankyou,

LeeBoy