Validation rule

[Vugar]

hi everyone,

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 fill itemID and StatusID that not available in the Stock.

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

 

[pbaldy]

I would probably use the before update event of the form, so you can test the quantities against each other. More here:

http://www.baldyweb.com/BeforeUpdate.htm

You can use DLookup() or open a recordset to get the value from the query.

 

[Vugar]

could you please write to the write update event on my example: for ItemID, StatusID?

 

[Vugar]

If Len(Me.SomeControl & vbNullString) = 0 Then
MsgBox "You need to fill out SomeControl"
Cancel = True
Me.SomeControl.SetFocus
End If

How I can use that event in my example?
1.stock Query: ItemID, StatusID
2.Outgoing Form:ItemID, StatusID

 

[myrt]

what was suggested was something of the sort:

sub txtItemID_BeforeUpdate(Cancel as Integer)
If DCount("fieldname1", "table/query", "fieldname2 =" & Me.txtItemID.value) = 0 then
msgbox("Insert valid id")
cancel = true
else
cancel = false
end if
end sub

Also you could use something of the sort:
me.txtboxname.validationrule = "IN " & string
me.txtboxname.validationruletext = "Insert valid id"

My are only suggestions, because it's not really clear to me what's in your form.
Someone else may be of more help, here. Some veterans, maybe

 

[pbaldy]

You can use a DLookup() to get the quantity on hand from your query and test against the amount requested on the form.

 

[pbaldy]

Didn't this get resolved here?

http://www.access-programmers.co.uk/forums/showthread.php?t=280976