459,453 Members | 961 Online
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

# Validation rule leak

 100+ P: 115 Hi i'm working on storing shoes so each article number had a size , and in my database i'm working that every article had different sizes, and since each article had max 5 sizes for example , i added 5 fields to the item table (st_q1,...st_q5) here is the item table (article,price,totStock,st_q1,st_q2,st_q3,st_q5) i'm working on the form to enter items to stock by quantity sizes.that form contain txtArticle bound to Article in OrderDetail table. and 7 check boxs the 1st one represent the total quantity need to be entered ( txtQuantity) and it's bound to the totQty in the table OrderDetail. the second one represent the total stock of that article txtStock bound to the field StockQty in item table.and the 5 txt box represents the quantity of article for each size (txtq1, txtq2, txtq3, txtq4, txtq5). so in this form i have one txtTot bound to a field in a table, and 5 others txtq1, txtq2, txtq3, txtq4, txtq5. i want to be able to just enter qty = txtTot , not less or more that txtTot, i tried this validation rule on txtq1, txtq2, txtq3, txtq4 and txtq5: Expand|Select|Wrap|Line Numbers [q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10]<=[quantity] this just make sure that i dont enter a sum(qi)>txtTot but that dont work if the sum(qi) < txtTot, cause if sum(qi) < txtTot the record still save in the database. any help please ! Aug 7 '10 #1
9 Replies

 Expert 100+ P: 1,134 I have trouble following your post But, if your rule is not less or more than then that must mean the rule is really must be equal to so Expand|Select|Wrap|Line Numbers [q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10]=[quantity]    should work If you think about the rule you wrote it should be obvious why it was doing what you described because that is exactly what you told it to do the sum(qi) must be less than or equal to quantity before the record can be saved Aug 9 '10 #2

 100+ P: 115 yes i tried that but still not working i will tell exactly what i'm doing, maybe you could help me. well on each txtqi i wrote in the validation rule that: Expand|Select|Wrap|Line Numbers [q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10]<=[quantity] it works just fine but here the problem for example 1st i need to enter the quantity = 10 (for example) after that i enter in the txtq1 (4) , and in the txtq2 (3) and in the txtq3 (4) when i want to add something in the txtq4 the validation text will pop up cause 4+3+4 = 11 > 10 and that's false. but see this example (quantity = 10 , txtq1=4,txtq2=1 ) and it also save it without prompting a alert message ... but 5#10. I also tried that one on each txtqi Expand|Select|Wrap|Line Numbers q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10]=[quantity]  and this always gives the validation text. like for example if quantity=10 and i start to put anything in the txtq1, it gives the error , even if i put txtq1=10!!! Aug 9 '10 #3

 100+ P: 115 Hi i just figure it out how can i do it ... but i don't know if i'm doing it right . i put this code on the after update of the txtq10 Expand|Select|Wrap|Line Numbers Dim Qty, sum As Integer quty = Me.Qty sum = Nz(Me.q1, 0) + Nz(Me.q2, 0) + Nz(Me.q3, 0) + Nz(Me.q4, 0) + Nz(Me.q5, 0) + Nz(Me.q6, 0) + Nz(Me.q7, 0) + Nz(Me.q8, 0) + Nz(Me.q9, 0) + Nz(Me.q10, 0) If (quty < sum) Then MsgBox "check the Quantity. qty < sum" q1.SetFocus End If If (quty > sum) Then MsgBox "check the Quantity. qty > sum" q1.SetFocus End If this had worked ... but still i have a problem if i entered for example this: quantity=10 txtq1=2 txtq2,txtq3,...,txtq10=2 that's would be wrong and this MsgBox "check the Quantity. qty < sum" will appears the focus is now on the txtq1. (q1.SetFocus) so if i just modify the txtboxs without modifying the txtq10 , the wrong quantity will save without message alert because i just wrote the validation on the txtq10 after update. I did try to write the same code on each txtq(i) but everytime that i try to modify the qty i got the alert message and the focus will go to the txtq1. and what if i just modify one txtq(i), and i pressed enter all the way that will not enter in the after update txtq10 cause txtq10 will not be updated neither changed .. any one can help me please!! Aug 9 '10 #4

 Expert 100+ P: 1,134 Actually, thinking upon what you are doing the way you are trying to setup the rule appears to be a sort of circular refernce. the rule txtq1+txtq2+txtq3+txtq4+txtq5=txtQty if we start with 1+1+1+1+1=5 and you want to change txtq1=2 if you try to change txtqty to 6 first the rule on will kick in and prevent it if you try to change txtq1 first the rule will kick in an prevent it. I think you would be better off making the txtQty textbox uneditable and then on txtq1 etc ... putting some code in the after update event that adds up the values in txtq1 etc and updates txtQty automatically. Aug 9 '10 #5