I am making a database using MS Access for my NGO, where we receive requisitions for relief materials from different centers and accordingly, we place an order to the supplier or dispatch from our stock. So generally, one requisition will have any one outcome either an order or dispatch. But rarely we may also send some quantity as dispatch and the rest as order. For example, we received a requisition from CoochBehar for 500 blankets. We placed an order for 375 blankets and dispatched 125 blankets from our stock.
I have two buttons on the Requisition form 'Order' & 'Challan'. I want to enable or disable them according to the situation. For new records, both should be enabled. I made a subroutine at the beginning and call it on the load event and current event of the form. It works for the existing data but when I try to enter a new record the error pos up.
I am using the following code -
Sub Check_Order_Dispatch()
Dim OrdCount As Integer
Dim DispCount As Integer
Dim OrdQnty As Double
Dim DispQnty As Double
Dim Qnty As Double
OrdCount = DCount("[OrdID]", "T02_Order_Details", "[OReqID] = " & Me.Txt_ReqID & " And [Item] = " & Me.Req_Details_SubF.Form!Cmbo_Item)
DispCount = DCount("[DispID]", "T04_Dispatch_Details", "[DReqID] = " & Me.Txt_ReqID & " And [Item] = " & [Req_Details_SubF].[Form]![Cmbo_Item])
OrdQnty = Nz(DLookup("Quantity", "T02_Order_Details", "[OReqID] = " & [Txt_ReqID] & " And [Item] =" & [Req_Details_SubF].[Form]![Cmbo_Item]), 0)
DispQnty = Nz(DLookup("Quantity", "T04_Dispatch_Details", "[DReqID] = " & [Txt_ReqID] & " And [Item] =" & [Req_Details_SubF].[Form]![Cmbo_Item]), 0)
Qnty = Me.Req_Details_SubF.Form!Txt_Quantity
If IsNull(Me.Txt_ReqID) Then
Btn_Challan.Enabled = True
Btn_Order.Enabled = True
Else
If OrdCount > 0 And OrdQnty < Qnty Then
Btn_Challan.Enabled = True
Btn_Order.Enabled = True
ElseIf OrdCount > 0 And OrdQnty >= Qnty Then
Btn_Challan.Enabled = False
Btn_Order.Enabled = True
ElseIf DispCount > 0 And DispQnty < Qnty Then
Btn_Challan.Enabled = True
Btn_Order.Enabled = True
ElseIf DispCount > 0 And DispQnty >= Qnty Then
Btn_Challan.Enabled = True
Btn_Order.Enabled = False
Else
Btn_Challan.Enabled = False
Btn_Order.Enabled = False
End If
End If
End Sub
I tried all possibilities that came to mind but to no effect. I am not very good at VBA. Please guide me. Thanks in advance!




