If / Then code help. Please (1 Viewer)

K

Kroghr

Registered User.
Local time
Tomorrow, 10:56
Joined
Oct 20, 2008
Messages
17
I'm trying to come up with a way to populate 5 unbound text boxes from on calculated controls value. Placing this on the Form under OnCurrent is not working for me. Will someone ttel me where I'm going wrong?


IF Me!TOWeight < 11000 Then
Me.V1 = 100
Me.VR = 100
Me.V2 = 111
Me.Vyse = 124
Me.Vermr = 103

IF Me!TOWeight < 11501 Then
Me.V1 = 100
Me.VR = 100
Me.V2 = 110
Me.Vyse = 125
Me.Vermr = 105

IF Me!TOWeight < 12001 Then
Me.V1 = 100
Me.VR = 100
Me.V2 = 109
Me.Vyse = 127
Me.Vermr = 107

IF Me!TOWeight < 12501 Then
Me.V1 = 100
Me.VR = 101
Me.V2 = 109
Me.Vyse = 127
Me.Vermr = 107

IF Me!TOWeight < 13001 Then
Me.V1 = 101
Me.VR = 103
Me.V2 = 109
Me.Vyse = 128
Me.Vermr = 109

IF Me!TOWeight < 13501 Then
Me.V1 = 102
Me.VR = 105
Me.V2 = 111
Me.Vyse = 129
Me.Vermr = 110

IF Me!TOWeight < 14001 Then
Me.V1 = 104
Me.VR = 108
Me.V2 = 113
Me.Vyse = 131
Me.Vermr = 112

IF Me!Toweight = 14500 Then
Me.V1 = 105
Me.VR = 109
Me.V2 = 114
Me.Vyse = 132
Me.Vermr = 1112
 
K

Kempes

Registered User.
Local time
Today, 23:56
Joined
Oct 7, 2004
Messages
327
Hi,

For starters you have omitted the word "else" between each IF, and I don't see an "End if", which there should be one for each if.

However, rather than using multiple IF statements, try Case.

Example

Select Case me.test.value
Case Is > 10
me.result1 = 5
me.result2 = 25
Case 10
me.result1 = 8
me.result 2 = 12
Case Is < 10
me.result1 = 1
me.result2 = 3
End Select

This may be easier for you.
 
M

Mutdogus

Registered User.
Local time
Tomorrow, 07:56
Joined
Apr 12, 2005
Messages
48
You also need a way out of your code because 2200 is going to be < 11000, so that code is going to run, but so is all of your other code because 2200 is also less than everything else. So after you are done processing in each Case statement you need to put an exit sub/function statement.
 
K

Kempes

Registered User.
Local time
Today, 23:56
Joined
Oct 7, 2004
Messages
327
I'm pretty sure you won't need exit sub, as I believe once it satifies the case criteria, it won't carry on checking the remaining...

However, what if your value exceeds 14500?

You'll need to pop a case else and the end, which will take care of all other scenerios!

Select Case test_expression

Case condition_1
result_1
Case condition_2
result_2
...
Case condition_n
result_n

Case Else
result_else

End Select
 
Last edited:
MSAccessRookie

MSAccessRookie

AWF VIP
Local time
Today, 18:56
Joined
May 2, 2008
Messages
3,428
Kempes is absolutely correct about using the Select Option. If, However, you still prefer the If() Approach, then you will need to add an Elseif Statement between each condition, and an EndIf at the end of the block. As you can see below, this can get very long.

Code: 
If  Me!TOWeight < 11000 Then
    Me.V1 = 100
    Me.VR = 100
    Me.V2 = 111
    Me.Vyse = 124
    Me.Vermr = 103
 
ElseIf  Me!TOWeight < 11501 Then
    Me.V1 = 100
    Me.VR = 100
    Me.V2 = 110
    Me.Vyse = 125
    Me.Vermr = 105
 
ElseIf  Me!TOWeight < 12001 Then
    Me.V1 = 100
    Me.VR = 100
    Me.V2 = 109
    Me.Vyse = 127
    Me.Vermr = 107
 
ElseIf  Me!TOWeight < 12501 Then
    Me.V1 = 100
    Me.VR = 101
    Me.V2 = 109
    Me.Vyse = 127
    Me.Vermr = 107
 
ElseIf  Me!TOWeight < 13001 Then
    Me.V1 = 101
    Me.VR = 103
    Me.V2 = 109
    Me.Vyse = 128
    Me.Vermr = 109
 
ElseIf  Me!TOWeight < 13501 Then
    Me.V1 = 102
    Me.VR = 105
    Me.V2 = 111
    Me.Vyse = 129
    Me.Vermr = 110
 
ElseIf  Me!TOWeight < 14001 Then
    Me.V1 = 104
    Me.VR = 108
    Me.V2 = 113
    Me.Vyse = 131
    Me.Vermr = 112
 
ElseIf  Me!Toweight = 14500 Then
    Me.V1 = 105
    Me.VR = 109
    Me.V2 = 114
    Me.Vyse = 132
    Me.Vermr = 1112
 
EndIf

Note: You could make this a lot shorter by creating a function to do what each block item does. This could also create the advantage of being easily able to add more conditions in the future when they are required, without the code getting out of control.

Code: 
Public Function SetValues(frm As Form, V1 As Integer, VR As Integer, V2 = 114, Vyse As Integer, Vermr As Integer) As Variant
 
    frm.V1 = 105
    frm.VR = 109
    frm.V2 = 114
    frm.Vyse = 132
    frm.Vermr = 1112
 
End Function
 
Private Sub MySub()
 
    Dim Response As Integer
 
    If  Me!TOWeight < 11000 Then
        Response = SetValues(me, 100, 100, 111, 124, 103)
    ElseIf  Me!TOWeight < 11501 Then
        Response = SetValues(me, 100, 100, 110, 125, 105)
    ElseIf  Me!TOWeight < 12001 Then
        Response = SetValues(me, 100, 100, 109, 127, 107)
    ElseIf  Me!TOWeight < 12501 Then
        Response = SetValues(me, 100, 101, 109, 127, 107)
    ElseIf  Me!TOWeight < 13001 Then
        Response = SetValues(me, 101, 103, 109, 128, 109)
    ElseIf  Me!TOWeight < 13501 Then
        Response = SetValues(me, 102, 105, 111, 129, 110)
    ElseIf  Me!TOWeight < 14001 Then
        Response = SetValues(me, 104, 108, 113, 131, 112)
    ElseIf  Me!Toweight = 14500 Then
        Response = SetValues(me, 105, 109, 114, 132, 112)
    EndIf
 
End Sub
 
Last edited:
K

Kroghr

Registered User.
Local time
Tomorrow, 10:56
Joined
Oct 20, 2008
Messages
17
Thank you all so much for your advice. I'm learning as i go here. I'll start reaserching how to create a function next. Again, thank you all for the advice.
 
J

JCShort

Registered User.
Local time
Today, 18:56
Joined
Aug 29, 2008
Messages
35
Using the following code posted by Kempes:

Select Case test_expression

Case condition_1
result_1
Case condition_2
result_2
...
Case condition_n
result_n

Case Else
result_else

End Select

How would you handle null values? My case else doesn't recognize null values, so it errors out. The error occurs when someone deletes the input into the case field. In my situation, the case field is a date and it is not required. Once data is input into the field, I want my case to run. I tried an if statement, but that isn't working either:

public function

if caseinfo is not null then

select case

case condition1
result1

etc

end select

end if

end function
 
MSAccessRookie

MSAccessRookie

AWF VIP
Local time
Today, 18:56
Joined
May 2, 2008
Messages
3,428
JCShort said:
Using the following code posted by Kempes:

Select Case test_expression

Case condition_1
result_1
Case condition_2
result_2
...
Case condition_n
result_n

Case Else
result_else

End Select

How would you handle null values? My case else doesn't recognize null values, so it errors out. The error occurs when someone deletes the input into the case field. In my situation, the case field is a date and it is not required. Once data is input into the field, I want my case to run. I tried an if statement, but that isn't working either:

public function

if caseinfo is not null then

select case

case condition1
result1

etc

end select

end if

end function
Click to expand...

I would think that Null is just another condition, and the following should work for you:
Code: 
Case IsNull(YourControl) 
[SIZE=5][COLOR=red][B]OR[/B][/COLOR][/SIZE] 
Case NOT IsNull(YourControl)
 
G

georgedwilkinson

AWF VIP
Local time
Today, 17:56
Joined
Mar 4, 2008
Messages
3,856
Couln't you have just used a lookup table for all this and totally eliminated all code?
 
J

JCShort

Registered User.
Local time
Today, 18:56
Joined
Aug 29, 2008
Messages
35
George,
You are probably right, hence you are a VIP & I'm not. :)

I'm not familiar with all of the built-in functionality within Access, so I tend to rely on code to make my DB's work. I've never created a look-up table, so I'm going to do some research and teach myself how.

This forum is a great help! Thanks for taking the time to review posts.
 
Rabbie

Rabbie

Super Moderator
Local time
Today, 23:56
Joined
Jul 10, 2007
Messages
5,906
JCShort said:
George,
You are probably right, hence you are a VIP & I'm not. :)

I'm not familiar with all of the built-in functionality within Access, so I tend to rely on code to make my DB's work. I've never created a look-up table, so I'm going to do some research and teach myself how.

This forum is a great help! Thanks for taking the time to review posts.
Click to expand...
They are actually much easier to learn than VBA
 
You must log in or register to reply here.

Similar threads

InFlight
Solved Write to a file
Replies
7
Views
902
gemma-the-husky
gemma-the-husky
L
Form Combo Box Drop Down Sorting
Replies
15
Views
587
Minty
Minty
M
VBA Code Cemetery Database
Replies
0
Views
681
m0aje
M
G
Some SQL help needed
Replies
2
Views
824
grades
G
T
Organizing If-else-statement
Replies
4
Views
4,102
Thinh
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom