Solved 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.

 

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

 

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

 

[Emma35]

Hi everyone......first of all thank you for the great suggestions. I caught a bad bug before Xmas and have been in bed ever since so haven't got back to this until this morning. I'm going to try a couple of changes and will get back with the results

 

[bob fitz]

Can you post a copy of the db, or at least show us a picture of its' relationship window.

 

[Emma35]

Hi again all......i've made some changes to the table structure which solved the problem. I think i was guilty of trying to find a quick solution using VBA when the answer was right in front of me. Thanks everyone for pointing me in the right direction as always.

Em x