Validation rule

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #1

hi everyone,

I'm new in access and trying to create Stock DB.

I have two main tables of incoming and outgoing.

I have crate stock Query with following fields

ItemID (Group By) , StatusID (Group By), Quantity (Sum)

And In Outgoing Form
ItemID, StatusID, ReceivedQuantity

Now the problem is , I dont want the user to give a qty more than the qty available in the Stock.

Can you give your idea please, what should i use here, thanks in advanced.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 18:58

Joined

May 7, 2009

Messages

20,276

• Sep 15, 2015

• #2

go to design view of your form. select ReceivedQuantity control. on Property Sheet->Data->Validation Rule: <=Nz(Dlookup("Quantity","yourStockTableNameHere","ItemID = " & [ItemID]),0)

on Validation Text:
Quantity must be less than or equal to Stock Quantity!

 

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #3

Can I give two criteria?

ItemID and StatusID? It's different table.

Thanks

 

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #4

<=Nz(DLookUp("Quantity","TotalInStock"," ItemID = " & [ItemID]),0)

it doesn't work.

TotalInStock - it's Query.

 

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #5

<=Nz(DLookUp("[SumOfQuantity]","[TotalInStock]"," ItemID = " & [ItemID]),0)

it's working thank you very very much.

Can I give 2 criteria: Item ID and Status ID?

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 18:58

Joined

May 7, 2009

Messages

20,276

• Sep 15, 2015

• #6

yes!

<=Nz(DLookUp("[SumOfQuantity]","[TotalInStock]"," ItemID = " & [ItemID] & "And [Status ID] = " & [Status ID]),0)

 

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #7

Thank you very very much

 

V

Vugar

Registered User.

Local time

Today, 14:58

Joined

Sep 15, 2015

Messages

55

• Sep 15, 2015

• #8

one more question:when I fill "ItemID" i don't fill "Status" , however I can write quantity, but it's wrong writing quantity without Status.
could you please help with this issue

<=Nz(DLookUp("[SumOfQuantity]","[TotalInStock]"," ItemID = " & [ItemID] & "And [Status ID] = " & [Status ID]),0)