If then statement

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Using MS Access 2002 sp2

I've looked for sample code through help and web sites but I can't seem to
get this statement to work. I'm still just learning the VBA end of Access
so this will prob. be simple for many.

Problem: I have a form which is filled out with livestock load information
from a truckers log.
The three of the fields needed in my calculation are: "NetWeightUnloaded",
"AvgerageHogWeightUnloaded" and "Cripples". The NetWeightUnloaded is
entered first which auto calculates the Average, then the Cripples. What
I'm trying to code is: If the Cripple field is not null then multiply the
AverageHogWeightUnloaded by the number of Cripples and put that number in
the NetUnloadedWeight field then move to the next entry on the form after
the Cripples which is labeled "One". Now if the Cripple field is null I need
it just to move to the next when hitting tab.

Here is what I've tried in the AfterUpdate of Cripples:

Private Sub Cripples_AfterUpdate()
Dim ctl As Control
Set ctl = Forms![Log Form Correction query]![One]

If IsNull([Cripples]) Then
Me![Cripples] = "0"
DoCmd.GoToControl ctl.[One]

Else
Me![NetWeightUnLoaded] = Sum(([AvgerageHogWeightUnLoaded]) * [Crips])
DoCmd.GoToControl ctl.[One]
End If
End Sub

All suggestions would be appreciated.
Thanks,
Chad
 
Chad,

A couple of things here. Cripples should be a numeric field
of a LongInteger type if you are going to do multiplication
on it. If you set this in the table, also say that the
default value is zero and now you have eliminated having to
do one If statement because it will never be null now, just
a zero if there aren't any.

Another thing is that what you really want to use is the
SetFocus command to put the user where they want to go
instead of the GoToControl.

Also, I assume that where you are referring to "Crips" in
the last calculation line, you really mean "Cripples".

With all of those things incorporated see if the following
code works, although I am concerned about the Sum part of
your formula...

Private Sub Cripples_AfterUpdate()

If Me!Cripples > 0 Then
Me![NetWeightUnLoaded] =
Sum((Me![AvgerageHogWeightUnLoaded]) * Me![Cripples])
Me!One.SetFocus
End If

End Sub

Gary Miller
Sisters, OR
 
Back
Top