VBA Problem in Access

  • Thread starter Thread starter Rock
  • Start date Start date
R

Rock

I have a form that I'm trying to get some 'math' completed via a
command button. I've posted the code below. I have 5 IF statements
(four of which are ElseIF). I've added a msgbox to each to see if the
code is picking up where I would expect. And it is....almost. The
Form lists various items and has a few textboxes. The greater than,
less than numbers in the code reflects an age. So, if age is >=0.1
and <=1.39999 then do this math, elseif, age >1.4 and <=1.9999 then do
this math and so on...all is well, until I get to a record where the
age is between 6 and 13, the last ElseIF....nothing happens when the
button is clicked. If I move to the next record and the age is, say
4, I get the desired result in textbox173. If I move the next record
and the age is 10, I click the button and I get nothing! What am I
over looking? I've tried moving the lines of code up in the list of
if's and elseif's but that did not help...any ideas? (I've added a
commented line: here is my problem in the code to show where the code
seems to stop working!!

Private Sub Command175_Click()
' this code is to populate text173
' empsalary
' range <20k, >20001<=28k,>28001

Dim Sal

Sal = EmpSalary.Value

If (Text57.Value >= "0.1" And Text57.Value <= "1.3") And Sal > 28001#
Then
Text173.Value = Text127.Value * Text89.Value
MsgBox ("Should be x 3")

ElseIf (Text57.Value >= "1.4" And Text57.Value <= "1.9999") And Sal
28001# Then
Click to expand...
Text173.Value = Text133.Value * Text89.Value
MsgBox ("Should be x 2.5")

ElseIf (Text57.Value >= "2" And Text57.Value <= "2.9999") And
Sal > 28001# Then
Text173.Value = Text139.Value * Text89.Value
MsgBox ("Should be x 2")

ElseIf (Text57.Value >= "3" And Text57.Value <= "5.9999") And
Sal > 28001# Then
Text173.Value = txb5015.Value * Text89.Value
MsgBox ("Should be x 1.5")
'Here is my problem.....
ElseIf (Text57.Value >= "6" And Text57.Value <= "13.9999") And Sal
28001# Then
Click to expand...
Text173.Value = Txb501.Value * Text89.Value
MsgBox ("Should be x 1")


End If
End Sub
 
Rock said:
I have a form that I'm trying to get some 'math' completed via a
command button. I've posted the code below. I have 5 IF statements
(four of which are ElseIF). I've added a msgbox to each to see if the
code is picking up where I would expect. And it is....almost. The
Form lists various items and has a few textboxes. The greater than,
less than numbers in the code reflects an age. So, if age is >=0.1
and <=1.39999 then do this math, elseif, age >1.4 and <=1.9999 then do
this math and so on...all is well, until I get to a record where the
age is between 6 and 13, the last ElseIF....nothing happens when the
button is clicked. If I move to the next record and the age is, say
4, I get the desired result in textbox173. If I move the next record
and the age is 10, I click the button and I get nothing! What am I
over looking? I've tried moving the lines of code up in the list of
if's and elseif's but that did not help...any ideas? (I've added a
commented line: here is my problem in the code to show where the code
seems to stop working!!

Private Sub Command175_Click()
' this code is to populate text173
' empsalary
' range <20k, >20001<=28k,>28001

Dim Sal

Sal = EmpSalary.Value

If (Text57.Value >= "0.1" And Text57.Value <= "1.3") And Sal > 28001#
Then
Text173.Value = Text127.Value * Text89.Value
MsgBox ("Should be x 3")

ElseIf (Text57.Value >= "1.4" And Text57.Value <= "1.9999") And Sal
Text173.Value = Text133.Value * Text89.Value
MsgBox ("Should be x 2.5")

ElseIf (Text57.Value >= "2" And Text57.Value <= "2.9999") And
Sal > 28001# Then
Text173.Value = Text139.Value * Text89.Value
MsgBox ("Should be x 2")

ElseIf (Text57.Value >= "3" And Text57.Value <= "5.9999") And
Sal > 28001# Then
Text173.Value = txb5015.Value * Text89.Value
MsgBox ("Should be x 1.5")
'Here is my problem.....
ElseIf (Text57.Value >= "6" And Text57.Value <= "13.9999") And Sal
Text173.Value = Txb501.Value * Text89.Value
MsgBox ("Should be x 1")


End If
End Sub
Click to expand...

One problem I see: what if the salary is exactly 28001? Noting your ranges
at the top, wouldn't you want <=20k, 20001 to 28000, and >=28001? If so, you
should change your If statements to be "Sal >= 28001#".

Try putting a breakpoint on the If statement. When the code breaks, step
through the code and check the values of each item at each step. Sometimes
you can find places where things change unexpectedly, and that might give
you an idea where the problem lies.

Carl Rapson
 
One problem I see: what if the salary is exactly 28001? Noting your ranges
at the top, wouldn't you want <=20k, 20001 to 28000, and >=28001? If so, you
should change your If statements to be "Sal >= 28001#".

Try putting a breakpoint on the If statement. When the code breaks, step
through the code and check the values of each item at each step. Sometimes
you can find places where things change unexpectedly, and that might give
you an idea where the problem lies.

Carl Rapson- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Carl

Thanks for the comment, I'm not too familiar with using the Breakpoint
feature. So, I tried setting this up, not sure if I did it
correctly...but....I place my curser on the line that seems to be
skipped and from Debug I selected breakpoint (a burgandy line was
place there on the code. I switched to the form and tested the
button, the VBA Editor came up with a yellow arrow to the
left...anyway, I place my mouse over the >= TEXT57.value item and it
reads: 10.416666667....I place the curser over the <=TEXT57.value and
it reads the same. So that seems OK as the criteria I'm looking for
is >6 and <13....Is the anything else I can do with some of the debug
tools?
 
Rock said:
Hi Carl

Thanks for the comment, I'm not too familiar with using the Breakpoint
feature. So, I tried setting this up, not sure if I did it
correctly...but....I place my curser on the line that seems to be
skipped and from Debug I selected breakpoint (a burgandy line was
place there on the code. I switched to the form and tested the
button, the VBA Editor came up with a yellow arrow to the
left...anyway, I place my mouse over the >= TEXT57.value item and it
reads: 10.416666667....I place the curser over the <=TEXT57.value and
it reads the same. So that seems OK as the criteria I'm looking for
is >6 and <13....Is the anything else I can do with some of the debug
tools?
Click to expand...

Did the code continue as expected? In other words, did the code step into
the section of the If statement that you expect? If so, what were the values
in Text173, Text501, and Text89? To "step" in Debug mode, press F8.


Carl Rapson
 
Back
Top