accessonly11
Member
- Local time
- Today, 19:38
- Joined
- Aug 20, 2022
- Messages
- 91
dear access members,
please check where is trouble in the coding. need assistent to make
here is view of my sales order form,
there is some issues with coding behind it.
i am trying to make it like northwind form, (there is some difference)
i need, when ProductID combobox get focus, dorpdown list show,
and then user type product in productid combobox, afterupdate event, it check
if the product have quantity in stock, otherwise show msgbox asking for to add this product in purchase order
and make it is sure it is not a double (product) entry
then go to next field of quantity.
in Quantity text after update event, it check
quantity is not 0, and not greather then available in stock.
my coding is as under, but is not working as requirment
please check where is trouble in the coding. need assistent to make
here is view of my sales order form,
there is some issues with coding behind it.
i am trying to make it like northwind form, (there is some difference)
i need, when ProductID combobox get focus, dorpdown list show,
and then user type product in productid combobox, afterupdate event, it check
if the product have quantity in stock, otherwise show msgbox asking for to add this product in purchase order
and make it is sure it is not a double (product) entry
then go to next field of quantity.
in Quantity text after update event, it check
quantity is not 0, and not greather then available in stock.
my coding is as under, but is not working as requirment
Private Sub ProductID_BeforeUpdate(Cancel As Integer)
lngProduct = Nz(Me!ProductID, 0)
If lngProduct = 0 Then
Cancel = True
MsgBox "Please select a product from the list"
Me.ProductID.SetFocus
Exit Sub
End If
With Me.RecordsetClone
.FindFirst "ProductID = " & lngProduct
If Not .NoMatch Then
Cancel = True
MsgBox "This product is already entered!"
Me.Undo
Me.TimerInterval = 100
End If
End With
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "OrderDetails", "ProductID = " & Me.ProductID & " AND OrderID = " & Me.OrderID) > 0 Then
MsgBox "This Product already entered"
Cancel = True
Me.ProductID.SetFocus
Exit Sub
End If
'Me.ProductID.DefaultValue = 1
End Sub
Private Sub ProductID_AfterUpdate()
With Me.ProductID
If Not IsNull(.Value) Then
Me.RetailPrice = .Column(3)
Me.SalePrice = .[Column](4)
'Me.Qty = .[Column](2)
End If
End With
ProductID.Requery
With Me.RecordsetClone
If ProductID.Column(2) <= 0 Then
Cancel = True
MsgBox " This Product is Not Available, Do you want to add in Purchase Order", vbInformation, "Not In Stock"
Me.Undo
End If
End With
End Sub
Private Sub Qty_AfterUpdate()
If Qty.Value > ProductID.Column(2) Then
MsgBox "There is only " & ProductID.Column(2) & " available", vbInformation, "Stock"
Qty.Value = ProductID.Column(2)
End If
End Sub
Private Sub Qty_LostFocus()
If Qty.Value = 0 Then
MsgBox "There is only " & ProductID.Column(2) & " available", vbInformation, "Stock"
End If
Qty.SetFocus
End Sub
