validation of two fields against a specific range

[Michael1839]

Hello and thank you for reading my question. I am using Access 2007. This is the problem; I have a warehouse divided into four departments North, East, South, and West. Each department is assigned its own storage location range North is N1-N1000, East is E1-E1000, South is S1-S1000, and West is W1-W1000. On the access input form of my database are two fields, one combo box field for Department and one combo box field for location stored. When the user is updating the data base for parts received or shipped, the user must enter the department and location stored to correctly debit or credit that department. The location stored entered must be assigned to the department. (Example: dept. is north and storage location is N65, the user enters D65 instead, error message is displayed, "you have entered the wrong location"). What I need is instructions how to write the code to check the fields to verify location stored matches department, and display a warning message. How do I do this? I have a table with department names and storage location ranges.


Thanks for your help.

[June7]

Maybe you need cascading comboboxes http://www.datapigtechnologies.com/f...combobox2.html

[Michael1839]

Hello, Thanks for the quick reply. That is not exactly what I am looking. when the data entry person fills the department field, the person must enter the warehouse location where the item will be stored or removed from. example North department has the space in the warehouse area of N1-N1000 each space is a location. If the location is not between N1-N1000 an error will display. I am unable to get the location field to look at the department field and the assigned locations to check what is entered into the location field.

[June7]

But if the location combobox list is restricted to only locations associated with department, user cannot select an invalid location.

You have a table that shows association of departments with locations? Provide example of this table.

In order for search and filter of the location identifiers to work properly since the values are text, they will all have to be the same length using filler zeros as necessary, like:

N0001 - N1000

Otherwise, N1000 will sort before N2.