How to display error message when available stock exceed limit

[harsh]

Hello All,

I am new in access.

I have an application in which user can place new order for product. We parse Excel Sheet and showing total stock & provide a user to input number of quantity which user want purchase.

I am trying to achieve like user can not input a quantity more then total stock item.

If user try to add quantity more then total available stock item then it should be display an error message or user will not be able to submit order.

For that i have tried like :-

[quantity]<=[totalstock]

Above "rule is added in quantity (text box)" but its not working.

Do i miss anything? or any syntax error or we can do these thing with any kind of event,query,etc...

Please find attached screenshot of my form.

Please let me know. Any help would be appreciated.

Thanks


Harsh Jain

[azhar2006]

Look at this example

http://office.microsoft.com/en-us/te...ai:TC001018548|

[ItsMe]

The following suggests that you are storing calculated values in your table(s)
[quantity]<=[totalstock]

A typical approach would be to use a built in function like Sum() to retrieve a calculated value like a total. This would be done on the fly, as needed.

As for comparing the user's request to the available stock, you would probably apply an If Then Else statement after using a function such as Sum().

[harsh]

Hello,

Thanks for your quick reply.

In my application the order form like user can ordered multiple product. which have their own available stocks so can't use idea of sum.
My concept is like :-
Product Name Quantity Total stock
Product1 5 10
Product2 2 20
Product3 2 30

Here i want to restrict quantity field that user can't be able to ordered quantity more that total stock. which total stock is already written static in excel sheet. that i display using query.
So how can i do that quantity must be less then or equal to total stock.

Any help would be appreciated.

Thanks
Harsh Jain

[ItsMe]

You could use an If Then statement. Maybe place it in a Before Update event handler.

If Me.InputField.Value > Me.QuantityAvailable.Value Then
msgbox "Not enough product in stock."
cancel = true
End if

[AnandKhinvasara]

Hi
What you have done is correct.
I made a sample database and checked with similar syntax and it works well for me.

Try following methods.
1. Check the fields. If possible use Cint([quantity])<=cint([totalstock])
2. Compact, repair database and then try.

[harsh]

You could use an If Then statement. Maybe place it in a Before Update event handler.

If Me.InputField.Value > Me.QuantityAvailable.Value Then
msgbox "Not enough product in stock."
cancel = true
End if

Hello i have written your given code in VBA form (in before update event handler):-

Private Sub quantity_BeforeUpdate(Cancel As Integer)


If [Form_NewOrder Details Subform]![quantity].Value > [Form_NewOrder Details Subform]![totalstock].Value Then
MsgBox "Not enough product in stock."
Cancel = True
End If
End Sub

But it not work for me so is there any mistake ?
Please tell me suggestion for that

Thanks

[harsh]

Hi
What you have done is correct.
I made a sample database and checked with similar syntax and it works well for me.

Try following methods.
1. Check the fields. If possible use Cint([quantity])<=cint([totalstock])
2. Compact, repair database and then try.

Hello Thanks for your quick reply
I tried your suggestion in my quantity text box rule property but it not work for me.
can i missing any thing ?
and you have said second step for 2. Compact, repair database and then try. so how can i do that is it possible to explain in some more detail ?

Any help would be appreciated.

Thanks

[ItsMe]

Try this in the Before Update of your form or the quantity control if there is one.

If 10 > 5 Then
msgbox "Not enough product in stock."
cancel = true
End if

If you are successful with the above code in the Before Update of a Control named quantity, try

If Me.quantity.Text > Me.[Form_NewOrder Details Subform].Form.[totalstock].Value Then
MsgBox "Not enough product in stock."
Cancel = True
End If
End Sub

If you are using the Form's Before Update, then maybe

If Me.quantity.Value > Me.[Form_NewOrder Details Subform].Form.[totalstock].Value Then
MsgBox "Not enough product in stock."
Cancel = True
End If
End Sub