validate data in form based on another control

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PAULinLAOS - 16 Sep 2007 15:24 GMT

I want to validate data entry in one control based on another. I looked at
some of the other threads in the discussion group but still not understanding.

It's an inventory database. In one control, I choose from a Combo box called
[Combo18] whether the record is a 'Sale' or a 'Purchase'. 'Sale'/'Purchase'
is stored in the control [type] as a number, Sale=1 and Purchase=2. I also
have one field/control for "IN" and one for "OUT". If it's a 'Purchase', then
"IN" should be an integer of 1 or more, and OUT must be 0. If it's a sale,
the opposite: OUT should be an integer 1 or more; IN zero. I need to validate
this rule on my form so that data entry people don't accidentally record the
amount sold in the 'IN' control, or record the amount purchased as inventory
OUT. It's a pretty simple concept.

I cannot seem to make work a validation rule in the properties of the "IN"
or "OUT" controls. This is what I have written

Validation Rule for "IN" control: IIf([type]=2,>0,0)
Validation Rule for "OUT" control: IIf([type]=1,>0,0)

I also tried:

Validation Rule for IN: IIf(Forms![inventory]![Combo18]="Purchase",>0,0)
validation Rule for OUT: IIf(Forms![inventory]![Combo18]="Sale",>0,0)

Basically, with these validation rules, it doesn't allow me to put any data
but zero in the fields. It just doesn't work; these rules lock up the IN/OUT
fields so that no data entry can be made. Maybe the syntax is wrong? I am
sure that my logic is correct, but can't express that in the Validation rule
field of properties.

Please help! Can't sleep. (Using Access 2000 format of Access 2003)

Reply to this Message

Jeff Boyce - 16 Sep 2007 15:57 GMT

I'm curious why you would need to essentially duplicate the data entry. It
sounds like you are saying that the value for the "IN" field is exclusively
determined by the value chosen in the combo box... and likewise for the
"OUT" field. Why force the users to provide the same information twice?

If you want to have an indication of whether the direction was in or out,
use a query to see the value chosen in the combo box, and handle
accordingly.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I want to validate data entry in one control based on another. I looked at
> some of the other threads in the discussion group but still not understanding.
[quoted text clipped - 27 lines]
>
> Please help! Can't sleep. (Using Access 2000 format of Access 2003)

Reply to this Message

PAULinLAOS - 17 Sep 2007 02:34 GMT

I don't think it's really duplicating the data entry. Yes, instead of having
one field for inventory IN/OUT, I have two fields, one for IN and the other
for OUT. The system works fine with having two fields and I'd rather not
change that.

After the type of sale is entered, e.g. sale, purchase, gift,
damaged/stolen, one needs to type in how many in either the IN or the OUT
field. If the type is a 'sale' or a 'gift' or a 'damage' then naturally the
inventory IN must equal zero and the inventory OUT must be greater than zero.
Likewise, if the type is a 'purchase', then the amount entered in IN must be
greater than zero and the amount entered in OUT must be equal to zero.

The validation rule is simply there to ensure that the user doesn't enter
the inventory OUT in the IN field or vice versa. The way to validate that is
based on whether the sale type is a 'sale', 'purchase', 'gift' etc.

There are other ways to help the user, I'm sure. For instance, if 'sale' is
selected, then the cursor goes automatically to the 'OUT' field and locks the
IN field. That would make it even more efficient for the user, but it seems
like a more complicated rule to write. So, I just want to stick with having a
validation rule for each of the IN/OUT controls based on the sale type
selected in the combo box.

Does anyone know how to do that?

> I'm curious why you would need to essentially duplicate the data entry. It
> sounds like you are saying that the value for the "IN" field is exclusively
[quoted text clipped - 46 lines]
> >
> > Please help! Can't sleep. (Using Access 2000 format of Access 2003)

Reply to this Message

Jeff Boyce - 17 Sep 2007 13:11 GMT

I may not have understood your earlier description...

Are you saying that IN and OUT are holding a count of items, not just a "1"
or "0"? If so, you are certainly able to use two fields to hold "count"
information, but it will make your (and your user's) work harder.

Take a look at the form's BeforeUpdate event ... this is a place you can
perform your validation checks using code like:

If cboSelectTransaction = "Sale" Then
If txtOUT = 0 Then
Msgbox "For a sale, the OUT count must be greater than 0"
...

It sounds like you've already decided how you are going to present this to
the user, and just wanted to know "what button do I push?".

Good luck

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I don't think it's really duplicating the data entry. Yes, instead of having
> one field for inventory IN/OUT, I have two fields, one for IN and the other
[quoted text clipped - 20 lines]
>
> Does anyone know how to do that?

Reply to this Message

PAULinLAOS - 17 Sep 2007 13:38 GMT

I think that you understand me now, but I don't understand that code.

My IN field is a number; it refers to the number of items purchased. My OUT
field is a number; it refers to the number of items sold/gift/damaged. I have
another field on the form which is a combo box where you select if the record
is a purchase/sale/gift/damage.

I just want to force the user to put a number of 1 or more in the IN field
when the combo box says "Purchase", and otherwise stay 0. And, if the combo
box is "sale","damaged" or "gift", the OUT box must be 1 or more, and
otherwise 0.

I don't really know much about writing code, but the code you wrote doesn't
make sense to me because you have an If/Then statement without any answer
after the Then.

I think I want something like this for the Beforeupdate of the IN field:

If Me.[Combo18] = "Purchase" Then Me.[IN] > 0 Else = 0

I just don't know how to write the code. I don't know if it should go in the
Beforeupdate or the Afterupdate either.

> I may not have understood your earlier description...
>
[quoted text clipped - 49 lines]
> >
> > Does anyone know how to do that?

Reply to this Message

Jeff Boyce - 18 Sep 2007 13:57 GMT

I wasn't trying to write the code, merely to provide a rough outline.
You've correctly determined that you need to "finish" the "If ... Then"
statement to finish your validation.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I think that you understand me now, but I don't understand that code.
>
[quoted text clipped - 72 lines]
> > >
> > > Does anyone know how to do that?

Reply to this Message

PAULinLAOS - 23 Sep 2007 06:58 GMT

Thank you, Jeff for outlining the what the statement should look like. But,
I'm still not clear on exactly how to writ the statement. I tried writing it,
like explained above, but it doesn't work. I used an IFF statement, but it
didn't work. By any chance, can you let me know what I did wrong in that
statement?

> I wasn't trying to write the code, merely to provide a rough outline.
> You've correctly determined that you need to "finish" the "If ... Then"
[quoted text clipped - 93 lines]
> > > >
> > > > Does anyone know how to do that?

Reply to this Message

Jeff Boyce - 26 Sep 2007 15:01 GMT

If you'll provide the code you used, the newsgroup readers can take a look.

By the way, the If... Then expression is what you'd use in most situations
involving event procedures, while the IIF() function is used more often in
queries.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> Thank you, Jeff for outlining the what the statement should look like. But,
> I'm still not clear on exactly how to writ the statement. I tried writing it,
[quoted text clipped - 99 lines]
> > > > >
> > > > > Does anyone know how to do that?

Reply to this Message