Checking Stock in Another Table for Validation (1 Viewer)

noorudden · Aug 13, 2017

Hello,
I am making a Stock in and out access program and i have come to a roadblock.

Table/Form
StockIn > Total Stock > 70 (Number)

Table/Form
StockOut > Qty > 100 (Number) << this field must verify above field if it is either 0 or - then it should give the error and tab should not move.

please help.

ashleedawg · Aug 13, 2017

You're a little vague. (It can be tough to visualize what someone means over the net, in writing.) If I understand correctly:

You have a form called Stockout with a textbox called Qty?

A separate table called StockIn has a field called TotalStock?

The form Stockout creates new records in a different table based on user input in Qty (and presumably other fields). You want to prevent users from entering a number in Qty that is larger that TotalStock from the StockIn field. Is this correct?

Sometimes a screenshot is helpful, perhaps showing the 2 tables/forms you're referring to.

noorudden · Aug 13, 2017

thanks for quick reply.
please attached image.
yes you are right
when i am entering a stock out which is going in the Kitchen table in Qty and when i am putting a value greater than TotalStock which is in Stores it must not allowed it unless i put above 0.

jdraw · Aug 13, 2017

You have not provided enough info for readers to give a focused response.
Here is link to a free video on Stock Control that may be useful.

Make a description of the "business involved" in simple English and gradually add sufficient details to tell readers what you are trying to do/support with your database.

Get your tables identified and tested before jumping to physical database and forms.

Good luck.

noorudden · Aug 13, 2017

i have already checked the provided linked database in detail.
and that business concept doesn't match with mine.

i am only stuck on one small thing and all other things have already been sorted.

In your provided video link there is a Part No and that does not apply in groceries.

my question is that how a field in form will check the available stock. and if stock is below minimum then it should not proceed and prompt an error.

jdraw · Aug 13, 2017

I'm not sure of your table structure, but in many inventory/stock control systems there are

Items (Products/Parts whatever)
there is usually a ReOrderLevel (when to reorder this item/product)
and ReorderQty (how much to reorder)

In most systems as per this Allen Browne article you do a StockTaking to get the physical Count of Items on Hand, then you add any new Incoming Stock from Supplier and subtract any Sales from Inventory. These transactions should give you stock on hand at any time ( there still may be loss(spoilage) or loss because of theft...)

ReorderPoint wikipedia

Good luck.

noorudden · Aug 13, 2017

thanks for your help But..

After spending countless hours as i am new to ms access i have found the solution to my problem.
Created a textbox linking it through Dlookup to the Table where TotalStock is.
Then on the stockout form and on Qty afterupdate() event procedure i have written a following IF statement.

Private Sub KtQty_AfterUpdate()
kqt = Me.KtQty.Value
tsk = Me.Tstock.Value

If kqt > tsk Or kqt <= 0 Then
Cancel = True
MsgBox "Stock is Either 0 or Minus Please Enter Correct Stock"
Me.KtQty.Undo

Else
Cancel = False
End If
End Sub

working absolutely fine.
only one thing is left.
if the statement is true then the focus should not move and entry should not be saved unless user enters correct stock number.
Any help?

arnelgp · Aug 14, 2017

There is no Cancel Param in After updating, use BefforeUpdate.

noorudden · Aug 14, 2017

Thanks arnelgp,
i did that yesterday.
all seems to be fine now.

Checking Stock in Another Table for Validation (1 Viewer)

noorudden

New member

ashleedawg

"Here for a good time"

noorudden

New member

Attachments

jdraw

Super Moderator

noorudden

New member

jdraw

Super Moderator

noorudden

New member

arnelgp

..forever waiting... waiting for jellybean!

noorudden

New member

Similar threads

Users who are viewing this thread