Validation rule leak

[hannoudw]

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:

[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 !

[Delerna]

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

[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

[hannoudw]

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:

[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

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!!!

[hannoudw]

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

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!!

[Delerna]

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+txt q3+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.

[Delerna]

My suggestion above is based on what you currently have as your table set up and I tend to just answer peoples questions.
However I feel I should point out a basic mistake you are making
You really should be designing tables that grow vertically instead of horrizontally.

HORIZONTAL GROWTH

(article,price, totStock,st_q1, st_q2,st_q3,st_ q5)

For example, what if in two years time they come out with a 6th size. A developer will have to go back into the database and add a 6th field to the table to accomodate the new size.
They will also have to update any queries, forms,reports that use that table. Extra rules will need to be added or extra after update events added. That is the draw back of horizontal growth. The new sizes must be added as new fields and there is then a lot of work updating everything else to accomodate the new fields


VERTICAL GROWTH
With vertical growth the new size will be added as a new record by a user and all the queries,forms and reports have been developed with vertical growth in mind and the new size will be automatically catered for. There should be no need for a developer to come back and make the addition

HOW?
I will keep the design simple rather than correct.

THE TABLE FIELDS
(article,price, Size,Stock)

THE RECORDS
DesertBoot,$10, 1,5
DesertBoot,$10, 2,1
DesertBoot,$10, 3,6
DesertBoot,$10, 4,2
DesertBoot,$10, 5,0

Notice how the sizes are growing vertically
To add a new size it is a simple matter of adding a new record and your form to update the stock will be a list of all the sizes of desertboot. TotalStock is also missing, this should be a calculated field that only exists in queries and forms and not in the table. Its value is calculated.




THE NEXT STEP....NORMALI ZE
Identical values should be moved to their own table. Notice in the above data DesertBoot and $10 is repeated for each size. This is a waste of space so.

THE TABLES
table1 (articleID,arti cle,price)
table2 (articleID,Size ,Stock)

THE DATA
table1
------
1,DesertBoot,$1 0
2,SandShoe,$5

table2
------
1,1,5
1,2,1
1,3,6
1,4,2
1,5,0
2,1,0
2,2,0
...


Anyway, thats the basics of "Effective Design"

There are plenty of articles about all of this on the internet and there is one on our site also

[hannoudw]

My data base was like that . but the thing is that i'm making this project depending on some one's need "It's my father :D " .
so he want when the form open and he choose the article to not insert the every size of the article, and the sizes of shoes are the same , and i don't think that there will be a new size more in the 50 or 100 years , anyway he just want when he open the form to add quantities to stock to see all the sizes and just fill the quantities of each size .
and i tried so many difficulties when i had the structure of table like you mentioned , cause every time that he choose a article i couldn't manage to see all the sizes of this article cause he need to fill them first .
I indeed appreciate your help, but if you see another way of doing this without making a form that enter all the sizes for every article by the user i'm listening !

[Delerna]

I agree that in this case it is unlikely that extra sizes of shoe will need to be added and I myself will break the "propper" database development rules if I see a benefit in doing so.

My post on propper database design was something I felt obliged to mention in case you were not aware. It wasn't meant to say you must develop that way, the choice is yours.

Just to answer the problems with this type of table structure. This is usually achieved through form/subform solutions.

The form will have the fields from table1 and you navigate through each record of table1 one at a time.
Inside the main form there is
a second form (called a subform) that displays only the records from table2 that relate to the current record on the main form.

New articles are added and updated through the fields on the main form and article sizes are added removed and updated through the fields on the sub form.

With this technique article1 might have say.. 1 size record attached to it. Article2 might have 3 size records attached to it etc etc.
You can add as few or as many size records to any particular article record as required

[Delerna]

incidentally, did you try the idea from post 5 in your current design? Will it work for you?

[hannoudw]

Question:
@Delerna
I'm thankful of your effort to help me.
And about #5 :

I'm still working on it and still not working, I just wanna keep the txtqty unlocked cause i wanna be able to modify the total quantity, and if i use the #5 i will not know if i did a mistake and i added more then the real quantity to stock.

For example if i have 10 items from the article1 and they are decomposed to: 1 item of the size1
2 items of the size2
2 items of the size3
1 item of the size4
1 item of the size5
1 item of the size6
2 item of the size7

And by any mistake i did entered this (1,1,2,1,1,1,2, 1)
as the quantity of every item, and i didn't notice that i entered less quantity.

But if i make sure to enter the total quantity number at the first place and after the quantity of every size. I'll be able to manage that possible mistake by checking if the total is equal to the sum of txtq(i).

And I solve it . I transfer the continuous form to a single form and i add a button with a click event to add the items to stock when quantity=Sum(qi ) and it works!!

Thanks again for the team help :)