Help with simplifying my Code

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

John Ortt

Are there any techniques I can use to simplify (and or shorten) the
following code please?

It all works but it is rather ungainly.

Thanks in Advance,

John

Code Follows:
//////////////////////////////////

Sub PRICE()

Dim MeMOQ As Integer, MePB2 As Integer, MePB3 As Integer, MePB4 As Integer,
MePB5 As Integer, MePB6 As Integer, MePB7 As Integer, MePB8 As Integer,
MePB9 As Integer, MePB10 As Integer
Dim MePRICE1 As Double, MePRICE2 As Double, MePRICE3 As Double, MePRICE4 As
Double, MePRICE5 As Double, MePRICE6 As Double, MePRICE7 As Double, MePRICE8
As Double, MePRICE9 As Double, MePRICE10 As Double

MeMOQ = Nz(Me.MOQ)
MePB2 = Nz(Me.PB2)
MePB3 = Nz(Me.PB3)
MePB4 = Nz(Me.PB4)
MePB5 = Nz(Me.PB5)
MePB6 = Nz(Me.PB6)
MePB7 = Nz(Me.PB7)
MePB8 = Nz(Me.PB8)
MePB9 = Nz(Me.PB9)
MePB10 = Nz(Me.PB10)
MePRICE1 = Nz(Me.PRICE1)
MePRICE2 = Nz(Me.PRICE2)
MePRICE3 = Nz(Me.PRICE3)
MePRICE4 = Nz(Me.PRICE4)
MePRICE5 = Nz(Me.PRICE5)
MePRICE6 = Nz(Me.PRICE6)
MePRICE7 = Nz(Me.PRICE7)
MePRICE8 = Nz(Me.PRICE8)
MePRICE9 = Nz(Me.PRICE9)
MePRICE10 = Nz(Me.PRICE10)
Dim ErrorMess As String

Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = True: Me.PRICE4.Enabled = True

If MeMOQ = Null Or MeMOQ < 1 Or MePRICE1 = Null Or MePRICE1 < 0.01 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = False:
Me.PRICE2.Enabled = False: Me.PB3.Enabled = False: Me.PRICE3.Enabled =
False: Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB2 = Null Or MePB2 <= MeMOQ Or MePRICE2 = Null Or MePRICE2 >=
MePRICE1 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = False: Me.PRICE3.Enabled = False:
Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB3 = Null Or MePB3 <= MePB2 Or MePRICE3 = Null Or MePRICE3 >=
MePRICE2 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB4 = Null Or MePB4 <= MePB3 Or MePRICE4 = Null Or MePRICE4 >=
MePRICE3 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = True: Me.PRICE4.Enabled = True
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False

etc

End If
 
Hi, does this code work? The reason I ask is that in the If statement
If MeMOQ = Null Or MeMOQ < 1 Or MePRICE1 = Null Or MePRICE1 < 0.01 Then

the clause
MePRICE1 = Null
should cause a problem (I think). From memory you can't use equivalence with
Null. Instead use MePRICE1 Is Null or IsNull(MePRICE1)

To your question. Why are you reading the control values into variables?
Your code might be slightly faster [don't quote me on that] but overly
complicates things.

Sub PRICE()

Dim intCount As Integer
Dim ErrorMess As String ' What's this for?

MOQ.Enabled = True
PRICE1.Enabled = True
For intCount = 2 To 4 ' Why stop at 4? If the pattern in the
' If statement below carries on
' then go to 10.
Me.Controls("PB" & intCount).Enabled = True
Me.Controls("PRICE" & intCount).Enabled = True
Next intCount

If nz(MOQ, 0) < 1 Or nz(PRICE1, 0) < 0.01 Then
Call setControlEnabled(2)
ElseIf Nz(PB2),0) <= MOQ Or Nz(PRICE2, PRICE1 + 1) >= PRICE1 Then
Call setControlEnabled(3)
ElseIf Nz(PB3, 0) <= PB2 Or Nz(PRICE3, PRICE2 +1) >= PRICE2 Then

....

End If
End Sub

Private Sub setControlEnabled(Byval vintIndex As Integer)
For intCount = vintIndex To 10
Me.Controls("PB" & intCount).Enabled = False
Me.Controls("PRICE" & intCount).Enabled = False
Next intCount
End Sub

Graeme.
 
would select case syntax help?

John Ortt said:
Are there any techniques I can use to simplify (and or shorten) the
following code please?

It all works but it is rather ungainly.

Thanks in Advance,

John

Code Follows:
//////////////////////////////////

Sub PRICE()

Dim MeMOQ As Integer, MePB2 As Integer, MePB3 As Integer, MePB4 As Integer,
MePB5 As Integer, MePB6 As Integer, MePB7 As Integer, MePB8 As Integer,
MePB9 As Integer, MePB10 As Integer
Dim MePRICE1 As Double, MePRICE2 As Double, MePRICE3 As Double, MePRICE4 As
Double, MePRICE5 As Double, MePRICE6 As Double, MePRICE7 As Double, MePRICE8
As Double, MePRICE9 As Double, MePRICE10 As Double

MeMOQ = Nz(Me.MOQ)
MePB2 = Nz(Me.PB2)
MePB3 = Nz(Me.PB3)
MePB4 = Nz(Me.PB4)
MePB5 = Nz(Me.PB5)
MePB6 = Nz(Me.PB6)
MePB7 = Nz(Me.PB7)
MePB8 = Nz(Me.PB8)
MePB9 = Nz(Me.PB9)
MePB10 = Nz(Me.PB10)
MePRICE1 = Nz(Me.PRICE1)
MePRICE2 = Nz(Me.PRICE2)
MePRICE3 = Nz(Me.PRICE3)
MePRICE4 = Nz(Me.PRICE4)
MePRICE5 = Nz(Me.PRICE5)
MePRICE6 = Nz(Me.PRICE6)
MePRICE7 = Nz(Me.PRICE7)
MePRICE8 = Nz(Me.PRICE8)
MePRICE9 = Nz(Me.PRICE9)
MePRICE10 = Nz(Me.PRICE10)
Dim ErrorMess As String

Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = True: Me.PRICE4.Enabled = True

If MeMOQ = Null Or MeMOQ < 1 Or MePRICE1 = Null Or MePRICE1 < 0.01 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = False:
Me.PRICE2.Enabled = False: Me.PB3.Enabled = False: Me.PRICE3.Enabled =
False: Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB2 = Null Or MePB2 <= MeMOQ Or MePRICE2 = Null Or MePRICE2 >=
MePRICE1 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = False: Me.PRICE3.Enabled = False:
Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB3 = Null Or MePB3 <= MePB2 Or MePRICE3 = Null Or MePRICE3 >=
MePRICE2 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = False: Me.PRICE4.Enabled = False
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False
ElseIf MePB4 = Null Or MePB4 <= MePB3 Or MePRICE4 = Null Or MePRICE4 >=
MePRICE3 Then
Me.MOQ.Enabled = True: Me.PRICE1.Enabled = True: Me.PB2.Enabled = True:
Me.PRICE2.Enabled = True: Me.PB3.Enabled = True: Me.PRICE3.Enabled = True:
Me.PB4.Enabled = True: Me.PRICE4.Enabled = True
Me.PB5.Enabled = False: Me.PRICE5.Enabled = False: Me.PB6.Enabled = False:
Me.PRICE6.Enabled = False: Me.PB7.Enabled = False: Me.PRICE7.Enabled =
False: Me.PB8.Enabled = False: Me.PRICE8.Enabled = False
Me.PB9.Enabled = False: Me.PRICE9.Enabled = False: Me.PB10.Enabled = False:
Me.PRICE10.Enabled = False

etc

End If
Click to expand...
 
Newbie said:
would select case syntax help?
Click to expand...

<snipped>

Yes, I think it would.

I just looked at the help file in Access and got the code below which looks
applicable.

In addition it says it goes straight to the relevant case rater than running
all the elseif statements.

Thanks.

//////////////////////////////////////////////////

Function Bonus(performance, salary)
Select Case performance
Case 1
Bonus = salary * 0.1
Case 2, 3
Bonus = salary * 0.09
Case 4 To 6
Bonus = salary * 0.07
Case Is > 8
Bonus = 100
Case Else
Bonus = 0
End Select
End Function
 
Graeme Richardson said:
Hi, does this code work? The reason I ask is that in the If statement
If MeMOQ = Null Or MeMOQ < 1 Or MePRICE1 = Null Or MePRICE1 < 0.01 Then

the clause
MePRICE1 = Null
should cause a problem (I think). From memory you can't use equivalence with
Null. Instead use MePRICE1 Is Null or IsNull(MePRICE1)
Click to expand...

It seems to work although it may just be skipping that bit of code.....I'll
try a little error checking in a minute.
To your question. Why are you reading the control values into variables?
Click to expand...

That was the only way I could find on the web and I adapted the code :)
Your code might be slightly faster [don't quote me on that] but overly
complicates things.

Sub PRICE()

Dim intCount As Integer
Dim ErrorMess As String ' What's this for?

MOQ.Enabled = True
PRICE1.Enabled = True
For intCount = 2 To 4 ' Why stop at 4? If the pattern in the
' If statement below carries on
' then go to 10.
Me.Controls("PB" & intCount).Enabled = True
Me.Controls("PRICE" & intCount).Enabled = True
Next intCount

If nz(MOQ, 0) < 1 Or nz(PRICE1, 0) < 0.01 Then
Call setControlEnabled(2)
ElseIf Nz(PB2),0) <= MOQ Or Nz(PRICE2, PRICE1 + 1) >= PRICE1 Then
Call setControlEnabled(3)
ElseIf Nz(PB3, 0) <= PB2 Or Nz(PRICE3, PRICE2 +1) >= PRICE2 Then

...

End If
End Sub

Private Sub setControlEnabled(Byval vintIndex As Integer)
For intCount = vintIndex To 10
Me.Controls("PB" & intCount).Enabled = False
Me.Controls("PRICE" & intCount).Enabled = False
Next intCount
End Sub

Graeme.
Click to expand...
Wow, far more readable. I'll give it a try.

Thanks Graeme
 
To answer your question, I didn't mean to stop at four, it was a mistake
which I just noticed thanks to your code :)
 
Back
Top