How to Warn Users that Entered Numbers Are Not Divisible

[doyle60]

I have the following fields in a subform (with this sample data):

Style: 1912E
Color: White
Design: R4345
Units: 600
Size1: 100
Size2: 300
Size3: 200
....
Size9: 0

However, I allow users to enter a size range instead of the actual
values for each size. So they could enter this instead:

Units: 600
Size1: 1
Size2: 3
Size3: 2

My database will calculate the actual values for any report that needs
it---which is rare but done.

My problem is I want to validate that the entered values are divisible
and leave no fractions.

So if they input:
Units: 601
Size1: 1
Size2: 3
Size3: 2

I want the computer to allow the values but warn them.

What do I put in a Macro Condition to validate this?

Thanks,

Matt

 

[pietlinden]

I have the following fields in a subform (with this sample data):

Style: 1912E
Color: White
Design: R4345
Units: 600
Size1: 100
Size2: 300
Size3: 200
...
Size9: 0

However, I allow users to enter a size range instead of the actual
values for each size. So they could enter this instead:

Units: 600
Size1: 1
Size2: 3
Size3: 2

My database will calculate the actual values for any report that needs
it---which is rare but done.

My problem is I want to validate that the entered values are divisible
and leave no fractions.

So if they input:
Units: 601
Size1: 1
Size2: 3
Size3: 2

I want the computer to allow the values but warn them.

What do I put in a Macro Condition to validate this?

Thanks,

Matt

put code in the BeforeUpdate event of the control. use Mod to see if
there's a remainder and warn the user if there is one. In a macro...
hmm.. never ever use them. easy enough in code, though.

say the control is called txtAmount

the event would be
Sub txtAmount_BeforeUpdate()
if mod(me.txtAmount,4)<>0 then
msgbox "txtAmount is not divisible by 4!"
end if
end Sub

 

[John W. Vinson]

What do I put in a Macro Condition to validate this?

Units \ Size1 <> Units / Size1

should do it... / is the usual division operator, \ is an integer divide; so
601 / 3 = 200.3333333333333 and 601 \ 3 is 200.000000000000.

 

[doyle60]

I never knew about that backslash divider rule. I was able to use it
in a macro.

But just for the record, I had to make a few changes. First, it is
the sum of the sizes that needs to divide in to the units, not the
individual size. For example, one could order 28 units and put in a
size scale of 1-5-1. Five does not divide into 28 and so would
produce a wrong message using your equation. But 7 (1+5+1) does
divide into 28 and so no warning is needed.
So I put the mac in the After Update property.

I also had to deal with the fact that a null value could be in some of
these fields. Since correcting for that would make an awful long
condition in the mac, I made nulls zero in the query and actually used
that field for validation and not the true field.

But thank you. It works perfectly and does not seem to be too
annoying when editing the datasheet detail sub.

Matt

 

[John W. Vinson]

I never knew about that backslash divider rule. I was able to use it
in a macro.

But just for the record, I had to make a few changes. First, it is
the sum of the sizes that needs to divide in to the units, not the
individual size. For example, one could order 28 units and put in a
size scale of 1-5-1. Five does not divide into 28 and so would
produce a wrong message using your equation. But 7 (1+5+1) does
divide into 28 and so no warning is needed.

Well... you did not say that, and it was not obvious from your message that

So I put the mac in the After Update property.

BeforeUpdate will let you cancel the addition (if that's what you want to do).

I also had to deal with the fact that a null value could be in some of
these fields. Since correcting for that would make an awful long
condition in the mac, I made nulls zero in the query and actually used
that field for validation and not the true field.

The NZ() function in queries is made to order for this. Good move!

But thank you. It works perfectly and does not seem to be too
annoying when editing the datasheet detail sub.

Glad to be of help.