field based on an other field value

[Khalil Handal]

Hi to all,




tlkpCategories is a table with the following fields:
pkCategoryId, CategoryName, StartNumber, EndNumber
The StartNumber and EndNumber fields are of Number Data Type


I have a form with two different controls: a text box named txtCallNumberFinal and a combo box named cboCategory with row source tlkpCategories.
Each category name has a corresponding number between StartNumber and EndNumber fields.


My issue is that the user might enter the correct category number but selects the wrong category name in the combo box. So, I want to check that the number used for the text box matches the correct category name.


Example:
category 1 is between StartNumber 100 and EndNumber 199.999
The user input txtCallNumberFinal was 120.256 but he selects Category 6 instead of category 1.


Which is better in a case like this:
1- display an error message to alert the user of his wrong category name choice.
or
2- the program will display the Category name based on the data input number automatically in the combo box.

Note:
The control txtCallNumberFinal is an alpha numeric that starts with numbers and ends with letters. The characters are / alphabets are not important in my case.
So I think the val() function will be used in a case like this to ignore the apphabets.


Any help is appriciated

[orange]

? Why does the user get to choose both Category and Number?
Seems you could have logic, and if the user knows the Category , you could assign a number from your lkup table,
BUT readers have no idea of your application/requirements in detail.

[Micron]

pkCategoryId, CategoryName, StartNumber, EndNumber

There are only 4 fields in the table and a user is inputting 1 of them in a textbox first? You could have all of them in a 4 column combo, showing whichever column(s) you want but utilizing only 1 of them.

[Khalil Handal]

Hi,
Each category has a range of numbers not only one number. So any number between 100 and 199.999 can be used for category 1. I hope this makes it more clear.
It is a library database where for example "mathematics" is a category and a corresponding number is a number between 510 and 519.999. As you can see more than one number ,ight be in one category.

[June7]

Option 2 - cascading comboboxes - a common topic.

[orange]

I agree with June re Cascading combos, but I'd like to know more about the application.
Does Dewey Decimal system approach apply ---if so then cascading combos would seem to handle the taxonomy.

[Khalil Handal]

Yes, Dewey Decimal System approch apply.
How many combo boxes should I use?

[June7]

However many you need to manage user options.

[Khalil Handal]

Thank you all very much.