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