Code for Message Box

[Alex Motilal]

I am using a database for the purchase of Raw Cotton. The purchase consists of two Operations (MyOperation ID 1 & 2). Under ID 1 Minimum Support price is fixed with Basic price & 14 different prices with allowances. Under ID 2 Commecial purchase is done with any rate concluded.
When a wrong rate is entered under ID 2 a message box appears indicating the Basic rate & the 14 different rates and correction should be made. This message box appears without any problem when a wrong rate is entered.
The problem appears when rates are entered under UD 2. Run time Error 94 appears.
I request the experts to look into the code & indicate what mistake is there.
I also want if a wrong rate under ID 1 is entered, Access should not allow to proceed till correction is made.

[Private Sub RatePerQtl_BeforeUpdate(Cancel As Integer)


Dim GetBasicRt As Double
Dim GetNinePCmoisRt As Double
Dim GetTenPCmoisRt As Double
Dim Get11PCmoisRt As Double
Dim Get12PCmoistRt As Double
Dim GetMIC1Rt As Double
Dim GetMIC2Rt As Double
Dim GetMIC3Rt As Double
Dim GetMIC4Rt As Double
Dim GetMIC5Rt As Double
Dim GetMIC6Rt As Double
Dim GetMIC7Rt As Double
Dim GetMIC8Rt As Double
Dim GetMIC9Rt As Double
Dim GetMIC10Rt As Double

GetBasicRt = DLookup("[BasicRate]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetNinePCmoisRt = DLookup("[ForNinePCMoisture]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetTenPCmoisRt = DLookup("[ForTenPCMoisture]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
Get11PCmoisRt = DLookup("[ForElPCMoisture]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
Get12PCmoistRt = DLookup("[ForTwPCMoisture]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC1Rt = DLookup("[MICone]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC2Rt = DLookup("[MICtwo]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC3Rt = DLookup("[MICthree]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC4Rt = DLookup("[MICfour]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC5Rt = DLookup("[MICfive]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC6Rt = DLookup("[MICsix]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC7Rt = DLookup("[MICseven]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC8Rt = DLookup("[MICeight]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC9Rt = DLookup("[MICnine]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)
GetMIC10Rt = DLookup("[MICten]", "[tblMSPRates]", "[Season_ID]=" & Me.Parent.ComboSeason & "And[Variety_ID]=" & Me.Parent.MyVty)

If Me.Parent.MyOperation = 2 Then
Me.RatePerQtl = Me.RatePerQtl.Value
ElseIf Me.Parent.MyOperation = 1 And Me.RatePerQtl <> GetBasicRt And Me.RatePerQtl <> GetNinePCmoisRt And Me.RatePerQtl <> GetTenPCmoisRt _
And Me.RatePerQtl <> Get11PCmoisRt And Me.RatePerQtl <> Get12PCmoistRt And Me.RatePerQtl <> GetMIC1Rt And Me.RatePerQtl <> GetMIC2Rt _
And Me.RatePerQtl <> GetMIC3Rt And Me.RatePerQtl <> GetMIC4Rt And Me.RatePerQtl <> GetMIC5Rt And Me.RatePerQtl <> GetMIC6Rt And Me.RatePerQtl <> GetMIC7Rt _
And Me.RatePerQtl <> GetMIC8Rt And Me.RatePerQtl <> GetMIC9Rt And Me.RatePerQtl <> GetMIC10Rt Then
MsgBox "The Rate, Rs." & Me.RatePerQtl & "/Qtl you have entered, is not in the MSP List Rate." & vbCrLf & vbCrLf & _
"For Current Season, " & Me.Parent.ComboSeason.Column(1) & ", Basic Rate is Rs." & GetBasicRt & "/Qtl" & vbCrLf & vbCrLf & _
"For various allowances, the Rates/Qtl are as follows:" & vbCrLf & vbCrLf & "Rs." & GetNinePCmoisRt & ", Rs." & GetTenPCmoisRt & ", Rs." _
& Get11PCmoisRt & ", Rs." & Get12PCmoistRt & ", Rs." & GetMIC1Rt & ", Rs." & GetMIC2Rt & ", Rs." & GetMIC3Rt & vbCrLf & "Rs." & GetMIC4Rt & ", Rs." _
& GetMIC5Rt & ", Rs." & GetMIC6Rt & ", Rs." & GetMIC7Rt & ", Rs." & GetMIC8Rt & ", Rs." & GetMIC9Rt & ", Rs." & GetMIC10Rt & vbCrLf & vbCrLf & _
"Re enter the Correct Rate.", vbCritical, "CAUTION! WRONG RATE ENTERED!!"
End If
End Sub]

Thanks

[ranman256]

i think youre doing this the wrong way. Theres no need for all this code.
Normally, you would monitor input at the form.
lookup tables would let users pick items,rates to add to the 'order'.

[isladogs]

I also agree that this is not the best approach.
However for info, error 94 is invalid use of null which means you are getting a null output in one or more DLookups
To fix, wrap the formula in Nz function to give an empty string or perhaps zero if null occurs.

Nz(DLookup(.....),"")

[Alex Motilal]

Thanks ridders52 your solution worked. I used Nz((-----),"0") and it worked.
I request ranman256 to clarify. If the rate has to be picked up I can use a Combo Box. However, here under Operation ID 1 rates are provided which can be selected. But under Operation ID 2 any rate can be entered. Is it possible to use a Combo Box?
Alex

[isladogs]

If it's a number field don't put the 0 in quotes. That's only needed for text strings.