If statement

  • Thread starter Thread starter Abby B via AccessMonster.com
  • Start date Start date
A

Abby B via AccessMonster.com

I put the following code in the after update event of a field. It is supposed
to update two fields but it doesn't do anything! Can anyone advise please. I
think it has to do with the if statment structure! I have three conditions
for each case and a different output in each. thanks.

Private Sub region_AfterUpdate()
If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] < 9 Then
[rma#] = "ON1010" & [74pi]
[CostCentre] = "30365"
If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] > 9 Then
[rma#] = "ON10" & [74pi]
[CostCentre] = "30365"
If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] > 99 Then
[rma#] = "ON1" & [74pi]
[CostCentre] = "30365"
If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] > 999 Then
[rma#] = "ON" & [74pi]
[CostCentre] = "30365"
Else
If [region] = "QR" And [RMA/74PI] = "74PI" And [74pi] < 9 Then
[rma#] = "QC1010" & [74pi]
[CostCentre] = "30366"
If [region] = "QR" And [RMA/74PI] = "74PI" And [74pi] > 9 Then
[rma#] = "QC10" & [74pi]
[CostCentre] = "30366"
If [region] = "QR" And [RMA/74PI] = "74PI" And [74pi] > 99 Then
[rma#] = "QC1" & [74pi]
[CostCentre] = "30366"
If [region] = "QR" And [RMA/74PI] = "74PI" And [74pi] > 999 Then
[rma#] = "QC" & [74pi]
[CostCentre] = "30366"
Else
If [region] = "WE" And [RMA/74PI] = "74PI" And [74pi] < 9 Then
[rma#] = "WE1010" & [74pi]
[CostCentre] = "30366"
If [region] = "WE" And [RMA/74PI] = "74PI" And [74pi] > 9 Then
[rma#] = "WE10" & [74pi]
[CostCentre] = "30366"
If [region] = "WE" And [RMA/74PI] = "74PI" And [74pi] > 99 Then
[rma#] = "WE1" & [74pi]
[CostCentre] = "30366"
If [region] = "WE" And [RMA/74PI] = "74PI" And [74pi] > 999 Then
[rma#] = "WE" & [74pi]
[CostCentre] = "30366"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
 
I appears that you nested these IFs, so all of them must be true to work.

Change it to group your statements....


Private Sub region_AfterUpdate()

If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] < 9 Then
[rma#] = "ON1010" & [74pi]
[CostCentre] = "30365"
end if

If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] > 9 Then
[rma#] = "ON10" & [74pi]
[CostCentre] = "30365"
end if

If [region] = "ON" And [RMA/74PI] = "74PI" And [74pi] > 99 Then
[rma#] = "ON1" & [74pi]
[CostCentre] = "30365"
end if
..
..
..
 
Using this many IFs make your code difficult to read and maintain, as well as
runs quite slowly. I would suggest converting your IFs to Select Case:

Select Case [region]
Case "ON"
If [RMA/74PI] = "74PI" then
Select Case [74pi]
Case Is < 9
[rma#] = "ON1010" & [74pi]
[CostCentre] = "30365"
Case 10 to 99
....
Case 100 To 999
...
End Select
end if
Case "QR"
...
Case "WE"
....
End Select

Sorry I don't have time to do more.

Of course, this might not be what you want. If so, sorry to butt in.

John H W
 
This seems to be what your after:



Private Sub region_AfterUpdate()

If [RMA/74PI] = "74PI" Then

Select Case [region]
Case "ON"

Select Case [74PI]
Case Is < 9
[rma#] = "ON1010" & [74PI]
Case Is > 999
[rma#] = "ON" & [74PI]
Case Is > 99
[rma#] = "ON1" & [74PI]
Case Else
[rma#] = "ON10" & [74PI]
End Select

[CostCentre] = "30365"

Case "QR"

Select Case [74PI]
Case Is < 9
[rma#] = "QC1010" & [74PI]
Case Is > 999
[rma#] = "QC" & [74PI]
Case Is > 99
[rma#] = "QC1" & [74PI]
Case Else
[rma#] = "QC10" & [74PI]
End Select

[CostCentre] = "30366"

Case "WE"

Select Case [74PI]
Case Is < 9
[rma#] = "WE1010" & [74PI]
Case Is > 999
[rma#] = "WE" & [74PI]
Case Is > 99
[rma#] = "WE1" & [74PI]
Case Else
[rma#] = "WE10" & [74PI]
End Select

[CostCentre] = "30366"
End Select

End If

End Sub


-LGC
 
Back
Top