Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2017, 06:34 AM   #1
noorudden
Newly Registered User
 
Join Date: Aug 2017
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
noorudden is on a distinguished road
Checking Stock in Another Table for Validation

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.

noorudden is offline   Reply With Quote
Old 08-13-2017, 07:48 AM   #2
ashleedawg
"Here for a good time"
 
ashleedawg's Avatar
 
Join Date: Jun 2017
Location: Canada
Posts: 152
Thanks: 18
Thanked 37 Times in 34 Posts
ashleedawg is on a distinguished road
Re: Checking Stock in Another Table for Validation

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.
__________________
_______________________________________
Back in the Fire
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ashleedawg is offline   Reply With Quote
Old 08-13-2017, 08:14 AM   #3
noorudden
Newly Registered User
 
Join Date: Aug 2017
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
noorudden is on a distinguished road
Re: Checking Stock in Another Table for Validation

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.
Attached Images
File Type: jpg TablesStock.jpg (98.5 KB, 32 views)

noorudden is offline   Reply With Quote
Old 08-13-2017, 09:10 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,884
Thanks: 41
Thanked 1,738 Times in 1,689 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Checking Stock in Another Table for Validation

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-13-2017, 09:50 AM   #5
noorudden
Newly Registered User
 
Join Date: Aug 2017
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
noorudden is on a distinguished road
Re: Checking Stock in Another Table for Validation

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.
noorudden is offline   Reply With Quote
Old 08-13-2017, 10:13 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,884
Thanks: 41
Thanked 1,738 Times in 1,689 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Checking Stock in Another Table for Validation

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 08-13-2017 at 10:24 AM.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
noorudden (08-13-2017)
Old 08-13-2017, 01:01 PM   #7
noorudden
Newly Registered User
 
Join Date: Aug 2017
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
noorudden is on a distinguished road
Re: Checking Stock in Another Table for Validation

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?

noorudden is offline   Reply With Quote
Old 08-13-2017, 07:05 PM   #8
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,736
Thanks: 50
Thanked 1,579 Times in 1,505 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Checking Stock in Another Table for Validation

There is no Cancel Param in After updating, use BefforeUpdate.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
noorudden (08-13-2017)
Old 08-13-2017, 11:03 PM   #9
noorudden
Newly Registered User
 
Join Date: Aug 2017
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
noorudden is on a distinguished road
Re: Checking Stock in Another Table for Validation

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

noorudden is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Stock Balances - Stock Checking ? Minty General 4 12-01-2016 04:52 AM
using one table for all forms of stock Cowboy_BeBa Tables 2 06-29-2016 02:20 AM
Stock management - calculating available stock items garywood84 Queries 5 09-21-2009 12:14 AM
Would like help with error checking/validation (I think) jenp Modules & VBA 2 02-15-2007 12:16 AM
VBA code for checking if quantity is greater than quantity in stock lisa09 Modules & VBA 3 05-23-2005 06:39 PM




All times are GMT -8. The time now is 01:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World