Solved Sales Order Form Coding (1 Viewer)

[accessonly11]

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

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


 

[accessonly11]

currently facing these problems,
firstly, in product combo, i enter product baydal which have 100 in quantity, but after entering more then quantity, it is not showing msgbox "MsgBox "There is only " & ProductID.Column(2) & " available", vbInformation, "Stock".
despite this, the msgbox which is related to check double product entry showing, while i have not double entry.
thirdly, default value of productid is disabled, but still it is showing value " panadol"

 

[Eugene-LS]

default value of productid is disabled, but still it is showing value " panadol"

Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.

 

[Gasman]

Walk through your code with F8. It dos not help when messages are exactly the same unless you do that.
Dim your variables. lngProduct is not even dimmed?

You must have Option Explicit at the top of every module. VBA window/Toops/Options and Reuire variable declarattion'
That will only now work for new modules. You have to fix existing ones yourself.

 

[arnelgp]

If you are Saving the balance to a field on a table (inventory table), then you can check that field.
otherwise, you can compute the balance of an item in a Query and summing all Receipt and deducting
the sum of all your Invoice.

 

[arnelgp]

you check and test.

 

[Pat Hartman]

Checking the value in the AfterUpdate or the lostFocus event event is like closing the barn door after the horses have escaped.

You need to do your validation in the BeforeUpdate event (preferably, the form's BeforeUpdate event) so you can cancel the event and prevent Access from saving a record that shouldn't be saved.

 

[accessonly11]

Checking the value in the AfterUpdate or the lostFocus event event is like closing the barn door after the horses have escaped.

You need to do your validation in the BeforeUpdate event (preferably, the form's BeforeUpdate event) so you can cancel the event and prevent Access from saving a record that shouldn't be saved.

understand

 

[accessonly11]

you check and test.

working very well, as i require.
if the quantity of product left 0, i think it should be checked while user want to save or print order

 

[arnelgp]

i think it should be checked while user want to save or print order

you already have code for that?

 

[accessonly11]

i did not have, do i put code under command button click event

 

[arnelgp]

i think you already have that "feature"?

 

[accessonly11]

after insertion of available product, if user put value 0 or just leave it unchanged unconciously, there is no check on this step,
like as under,
gramex
septran

 

[arnelgp]

add code to the Form's BeforeUpdate event.
set Cancel = True if qty <= 0

 

[Pat Hartman]

i did not have, do i put code under command button click event

I guess you didn't understand my "after the horses have left the barn" analogy. If you don't want to save bad data, you need to do the validation in the form's BeforeUpdate event (or rarely, in the control's BeforeUpdate event)

Here's a video you might want to watch. You don't have to join. You can just play it.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

 

[accessonly11]

add code to the Form's BeforeUpdate event.
set Cancel = True if qty <= 0

its working now, me.qty.value was not responding, but when i change it to me.qty.text=0 now working

in form beforeupdate event

 

[accessonly11]

I guess you didn't understand my "after the horses have left the barn" analogy. If you don't want to save bad data, you need to do the validation in the form's BeforeUpdate event (or rarely, in the control's BeforeUpdate event)

Here's a video you might want to watch. You don't have to join. You can just play it.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

thanks for your guidance, i understand that validation of data is not good in afterupdate events of form or control.

 

[Pat Hartman]

Use Me.Fieldname to reference controls since .Value is the default property. There is no need to include the .Value peoperty and the .Text property has a special use. It is used when the control has the focus. Each control has THREE buffers ---

Me.FieldName.OldValue = The value loaded into the control in the current event. It is either Null for a new record or it is the value that is currently stored in the table which might be null if null is valid for that field.

Me.FieldName.Text = the buffer into which data is typed. In earlier versions of Access, the .Text property was only available when the control had the focus so I would be very careful about referencing the .Text buffer. This is the buffer that is generally referenced when you are using the change event. As each character is typed into the .Text buffer, the change event runs and you can ensure that the data length is not exceeded or that certain characters are not present. For example, if you want only uppercase letters, you can check each one as it is typed.

Me.FieldName.Value = the buffer where the completed entry is stored and from which it will be saved. When the control looses focus, the .Text value is moved to .Value and after that, they will be the same. Between the BeforeUpdate and AfterUpdate events, the .OldValue becomes the contents of the .Value buffer. So, at that point, all three buffers have the same contents, assuming you can still reference the .Text buffer

To verify what I am saying, put code into the Change event to display the value of each of the buffers as you type each character.

 

[accessonly11]

Use Me.Fieldname to reference controls since .Value is the default property. There is no need to include the .Value peoperty and the .Text property has a special use. It is used when the control has the focus. Each control has THREE buffers ---

Me.FieldName.OldValue = The value loaded into the control in the current event. It is either Null for a new record or it is the value that is currently stored in the table which might be null if null is valid for that field.

Me.FieldName.Text = the buffer into which data is typed. In earlier versions of Access, the .Text property was only available when the control had the focus so I would be very careful about referencing the .Text buffer. This is the buffer that is generally referenced when you are using the change event. As each character is typed into the .Text buffer, the change event runs and you can ensure that the data length is not exceeded or that certain characters are not present. For example, if you want only uppercase letters, you can check each one as it is typed.

Me.FieldName.Value = the buffer where the completed entry is stored and from which it will be saved. When the control looses focus, the .Text value is moved to .Value and after that, they will be the same. Between the BeforeUpdate and AfterUpdate events, the .OldValue becomes the contents of the .Value buffer. So, at that point, all three buffers have the same contents, assuming you can still reference the .Text buffer

To verify what I am saying, put code into the Change event to display the value of each of the buffers as you type each character.

very important informations. thanks