473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Validation rule leak

115 100+
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
  1. [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 1558
Delerna
1,134 Expert 1GB
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
  1. [q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10]=[quantity] 
  2.  
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
hannoudw
115 100+
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
  1. [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
  1. 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
hannoudw
115 100+
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
  1. Dim Qty, sum As Integer
  2. quty = Me.Qty
  3. 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)
  4. If (quty < sum) Then
  5. MsgBox "check the Quantity. qty < sum"
  6. q1.SetFocus
  7. End If
  8. If (quty > sum) Then
  9. MsgBox "check the Quantity. qty > sum"
  10. q1.SetFocus
  11. 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
Delerna
1,134 Expert 1GB
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
Delerna
1,134 Expert 1GB
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....NORMALIZE
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,article,price)
table2 (articleID,Size,Stock)

THE DATA
table1
------
1,DesertBoot,$10
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
Aug 10 '10 #6
hannoudw
115 100+
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 !
Aug 10 '10 #7
Delerna
1,134 Expert 1GB
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
Aug 10 '10 #8
Delerna
1,134 Expert 1GB
incidentally, did you try the idea from post 5 in your current design? Will it work for you?
Aug 10 '10 #9
hannoudw
115 100+
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 :)
Aug 11 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Dalan | last post by:
This ought to be simple enough, but not certain which to use. I have a few fields set to Require data to be entered; however, the message displayed by Access 97 is too generic to be of any real...
2
by: Joey P | last post by:
Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one...
2
by: Danny | last post by:
I created a function to test to see if entered text has a '.' in it, The code seems to work but the validation always fails even when 'true' is returned. i put = myfunction in the validation...
3
by: GGerard | last post by:
Hello In a text box, I would like the user to be able to enter only a number from 0 to 100 (including decimals Ex: 24.56) so I wrote this in the Validation Rule Propertie of the text box : >=...
6
by: Chuck | last post by:
A97. A database has a table: tblA which has a single text field, B. It is a primary field, indexed and no duplicates. It is used as a lookup for table tblC. A form based on tblA is used to add...
4
by: ron | last post by:
I have a access based guest book. I want to create a validation rule to block certain words or parts of a srting. How do i do this? ie: this is a nice site. come visit my porn site at www.abc.zy...
10
by: gweasel | last post by:
What is the best way to apply a Validation Rule - or rather, where is the best place to put it? Is there an advantage to putting it on the field in the table vs setting the validation rule on the...
3
by: KPR1977 | last post by:
I want to set a validation rule that will not allow me to input data in field1 in TableA that is not found in TableB under field2. Basically, Table B under field 2 has the following row entries: ...
12
alakazamm
by: alakazamm | last post by:
I am building a database on Access 2000 which include a Customer Details table. In this Customer Details table there is a 'Post Code' field which I am required to make an Input Mask to limit the...
1
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.