Populate 2nd text box based on inputted value of 1st

[markarmer]

I have a form which is essentially being used as a calculator...

On the form there are 2 text boxes named txtInput and txtOutput.
The basis of the boxes are to display an answer in txtOutput based on a number the user puts in txtInput...


There is no real calculation performed as I need to give a fixed answer based on what the user types in, for example:


If the user types a number between 1 and 5 in txtInput I need txtOutput to show 23
If the user types a number between 5 and 10 in txtInput I need txtOutput to show 34
If the user types a number between 12 and 20 in txtInput I need txtOutput to show 79
so on and so forth...


I need to be able to set the returned values as as I said, they can't be calculated. It will be tedious but I only need to put the values in for 35 different options so it won't be too bad, I just can't figure out how to have the txtInput use what is entered in it to pull up the right answer.


Essentially it is like an IF query, but with multiple options for the IF.


Any help with this would be greatfully appreciated, and thank you in advance.

 

[pbaldy]

As answered elsewhere:

It's certainly possible; you'd use the after update event of the input textbox, along with Select/Case. Personally though, I'd put the values in a table with 3 relevant fields: the upper and lower limits plus the value to return. Then you can use DLookup() or a recordset to retrieve the appropriate value. That makes it more maintainable than having it all in code.

 

[boblarson]

Use a Select Case statement in the After Update event of the input box

[COLOR=silver]Dim lngRet As Long[/COLOR]
 
[COLOR=silver]Select Case Me.YourTextBoxForInput[/COLOR]
[COLOR=silver]  Case 1 to 5[/COLOR]
[COLOR=silver]     lngRet = 23[/COLOR]
[COLOR=silver]  Case 6 to 10[/COLOR]
[COLOR=silver]     lngRet = 34[/COLOR]
[COLOR=silver]  Case 12 to 20[/COLOR]
[COLOR=silver]     lngRet = 79[/COLOR]
[COLOR=silver]  Case Else[/COLOR]
[COLOR=silver]     lngRet = 0[/COLOR]
[COLOR=silver]End Select[/COLOR]

And you can add whatever you need but remember not to overlap numbers like you did in your first 1 and 5 and 5 and 10 example.

I agree with Paul as the table is much better and more flexible should anything ever need to change. (I was just too slow typing this out as well )