Restrict Data Entry

[Emma35]

Hi Everyone,
I'm using a data entry form and i'd like to restrict what data users can enter based on a previous combo box selection. For example, if my combo box has the options A, B, C or D, then if the user selects option A i'd like to prevent them from entering a certain product number into a text box. However, If the same user changes their selection from A to B, then then can now enter that product number. The logic behind this is so that users cannot enter incorrect products into the wrong tables.

Thanks a lot

 

[Gasman]

Why not make the second entry a combo, and then not allow any new entries?
Otherwise put code in the afterupdate of each control.

 

[DHookom]

Logic like this requires a table of correct options so the second entry can be limited to valid selections. This is typically managed via cascading combo boxes.

 

[Emma35]

Thanks for the suggestions guys. What code would i need to put in the AfterUpdate event ?
A B C and D are different areas in a factory setting and i don't want the wrong items scanned to the wrong area

 

[MarkK]

i don't want the wrong items scanned to the wrong area

In your data, how is this "wrongness" identifiable?

The logic behind this is so that users cannot enter incorrect products into the wrong tables.

It seems like a mistake if there is more than one product table.

 

[Pat Hartman]

f the same user changes their selection from A to B, then then can now enter that product number. The logic behind this is so that users cannot enter incorrect products into the wrong tables.

Your table design is almost certainly wrong

Thanks for the suggestions guys. What code would i need to put in the AfterUpdate event ?

Regardless, the AfterUpdate event would be the wrong event to do data validation in.

Show us your schema and tell us why you think you need multiple tables for different types of parts. If your reasoning has to do with the attributes of parts, there are other options that allow you to always have a SINGLE part table but handle storing of attributes in one of two common ways.

 

[Emma35]

I realise now i probably haven't explained this very well. The same database is used in Area A and Area B. The information is scanned in from a barcode. So, there are items in Area A that don't belong in Area B and vice versa. If somebody scans a pallet in Area B which only belongs in Area A i'd like a message to appear saying something like 'This item is not compatible with this Area'.

 

[arnelgp]

you should add the Area to your table, so that it is easier to validate the scanned bar code.

 

[Pat Hartman]

You need a way of identifying who is scanning an item and where he is working in order to identify that something destined for area B is being scanned by someone working in area A.

A potential solution would be to have each employee record include the Area in which he works in his employee record. Then when he logs in for the day, a scanner is attached to the worker. Then when he scans something, you can identify the scanner and from that the person and from that the Area and compare that Area with the Area that the item is supposed to be stored in.

 

[GaP42]

... or the scanner used is associated to a work area. But in either case (as in Pat's suggestion or this one) what would happen if a worker is deployed to work in the other area or the scanner is moved to the other area because the existing one broke or some other need arose.
Are all item types only processed in one Area or the other? What if management decides a change in process is to occur and items of a certain type now are process in Area A and not B? Can this be easily adjusted? What of the existing items still in Area A?

A fundamental question: If an item is scanned, then the item is in that "Area" - why would you want to prevent that? If it is never meant to be processed in that area the problem is not that the record should not exist but perhaps the item record needs to be flagged for incorrect placement and transfer?

 

[Pat Hartman]

Or when an employee logs in, he specifies his work area as well as registering his scanner. If either changes during the day, he must log out and then back in.

The point is that you need a way to collect certain information before you can act on it.