Dynamic unbound textbox on a continous form

  • Thread starter Thread starter Jess
  • Start date Start date
J

Jess

I have a continuous form with a bound textbox displaying numbers (txtnumber).
My form also has an unbound textbox –txtNumberSize- in its detail section
whose content must vary according to txtnumber. I would like txtNumberSize to
show “between 100 and 1000†when txtnumber is greater than 100 but less than
1000; “negative†when txtnumber is less than zero; “zero†when the txtnumber
is zero; and “bigger than 1000†when the showed number is bigger than 1000.
For the sake of brevity I have listed these four cases but there are actually
more. How can I accomplish this without touching the underlying table my form
is based on?

Thanks in advance
 
You can calculate the value by using an expression in the controlsource of
the unbound textbox:

=Iif([txtnumber] < 0, "Negative", "Positive")

But in your case, because that expression might get to be fairly long and
confusing based on your conditions, it may be prudent to move this to a
function instead. The function will need to be public (in a standard module,
not a form module, and declared as Public Function...), in which case you
refer to the function in the textbox's control source:

=fGetTextSize([txtnumber])

and in a module:

Public Function fGetTextSize(lngNum As Long) As String
Dim Ret As String

If lngNum < 0 Then Ret = "Negative"
If lngNum = 0 Then Ret = "0"
If (lngNum > 0) AND (lngNum < 1000) Then Ret = "Between 1 and 999.99"
'etc, etc

fGetTextSize = Ret
End Function


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Jess said:
I have a continuous form with a bound textbox displaying numbers (txtnumber).
My form also has an unbound textbox –txtNumberSize- in its detail section
whose content must vary according to txtnumber. I would like txtNumberSize to
show “between 100 and 1000” when txtnumber is greater than 100 but less than
1000; “negative” when txtnumber is less than zero; “zero” when the txtnumber
is zero; and “bigger than 1000” when the showed number is bigger than 1000.
For the sake of brevity I have listed these four cases but there are actually
more. How can I accomplish this without touching the underlying table my form
is based on?
Click to expand...


I suggest that you create a table (named Ranges) to
translate numbers to the descriptive text. The table could
have 3 fields:

Low High Descr
-9999 -1 Negative
0 0 Zero
1 100
101 999 Between 100 and 1000
1000 99999 bgger than 1000

Then your text box could use am expression like:

=DLookup("Descr", "Ranges", txtnumber & " Between Low And
High")

Or, you could join that table to your data table in the
form's record source query.
 
Back
Top