Here's what I've come up with. And this code goes under "Event - Lost Focus" for the control "Quantity" on Form "Shipping".
Private Sub Quantity_LostFo cus()
If (StrComp("[Quantity]", "[Q - Limit Quantities]![Sum Of Quantity]", vbBinaryCompare )) = 1 Then
MsgBox "Insufficie nt Quantity", vbOKOnly
End If
End Sub
...
glitke
Last Activity: Sep 18 '08, 07:27 PM
Joined: Sep 15 '08
Location:
-
So how do you compare two strings in code then?...Leave a comment:
-
Ok. Thanks for the info....Leave a comment:
-
This line checks to see if the value that a user inputed is > the available quantity to be shipped.
So the "Quantity" is what the user inputted.
The "strSQL" is actually a query that finds the max quantity available.
The way the "available quantity to be shipped" is calculated is by
1. - selecting all records on the table, T - Main Frame,
- where the...Leave a comment:
-
Changing SQL to VB code for use in MSA
I have no clue how to write in code but my Access program needs demand that I use code to validate a field. Somewhere I read you can copy an Access query's SQL code into VB code and only need to modify the controls that have user defined parameters.
I already have a query built that generates this SQL:
SELECT [T - Main Frame].[MSO #], [T - Main Frame].[Part #], Sum([T - Main Frame].Quantity) AS SumOfQuantity
FROM... -
Funny, I opened my user profile and deleted it from there. ? I double checked I deleted it from subscriptions instead of started. Thanks for your understanding....Leave a comment:
-
Appreciate the advise. I read where you can create a query in design mode and then copy the SQL statement to code. I'll post if I get it to work. Much appreciation....Leave a comment:
-
Please note that I deleted my second question shortly after posting because I noticed it failed to meet the requirements of the specific rule you mention. My apologies for any inconvenice this caused. Thanks for your reminder though....Leave a comment:
-
I reconstructed my expression as a sum query and then used the results of the query for the validation which still doesn't work. The new expression simply states:
<=[Q - Limit Quantity]![SumOfQuantity]
When I test I get an error "MSAccess can't parse the Validation Rule expression you entered".
What am I doing wrong?...Leave a comment:
-
Here's the expression I used in the "Validation Rule" but it doesn't like.
<=(Sum(([T - Main Frame]![Quantity]) WHERE ((([T - Main Frame]![Status] = "Received") OR ([T - Main Frame]![Status] = "Shipped") OR ([T - Main Frame]![Status] = "Scrapped") ) AND ([T - Main Frame]![Part #] = [Part #]) AND ([T - Main Frame]![MSO #] = [MSO #])))))
After I created the Query I tried this:...Leave a comment:
-
Inventory Problem - Field Validation
Here's the deal. I receive parts from one company, refurb them, and then send them back. So, they send me 10 parts on one MSO#, which is like a PO#, and I can only ship back a max of 10 parts that refer to that MSO#. What I need to do is limit a quantity field on a form so that I don't send back more parts than I receive on my MSO.
I've created a query that returns the maximum amount I can send back based on the MSO#.
... -
Validation Error - Invalid Syntax - Operand w/o operator
I need the quantity entered in a field to be <= a certain quantity. I've opened the field properties and entered the following under the "Validation Rule".
<= ( sum ( ( [T - Main Frame]![Quantity] ) Where ( ( [T - Main Frame]![Part #] = [Part #] ) AND ( [T - Main Frame]![MSO #] = [MSO #] ) AND ( [T - Main Frame]![Status] = "Received" Or "Shipped" Or "Scrapped" ) ) ) )
I...
No activity results to display
Leave a comment: