change textfield content

[Steven L via AccessMonster.com]

I have a form with several textfields in it that shows the result of a query.
Most of these textfields contain numbers. However, these numbers represent
values/properties/strings; for example, in the textfield named 'sex', 1
represents 'Female', 2 represents 'Male' and 3 represents 'Uknown'. The
numbers were inputted like this by the data inputter for ease and speed, but
it makes my job a little more difficult.

I have a seperate lookup table that matches the numbers to their values; i.e.
I have a table with fields 'number' and 'sex' where the record with number=1
has sex=Female. Now what I want is for the form to display these meaningful
values instead of the numbers, but I'm not sure how to go about this.

Is there a way I can get the form to look up the aforementioned table and
then change the value in the textfield? Does changing the textfield change
the underlying table?

Steve

 

[Klatuu]

The easy way to do this and make the data entry person happy is to change the
control from a text box to an option group and assign the values for each
possibilty to an option button and give it the appropriate label.
For those fields that have more than 3 or 4 different values, I would change
from the text box to a combo box with two columns. One column for the number
which should be the bound column and have a column width of 0 so the user
doesn't see it, and another column that has the text in it.
To do it with text boxes would require some cludgy (advanced technical term
you won't find in Help) coding.

 

[John Nurick]

I'd go with combo boxes. If the data entry person is happy entering
numbers I suspect they'd be very unhappy to have to start using option
buttons, which are great for some purposes but lousy for high-speed
touch-typing data entry.

If the values are like
1 Male
2 Female
3 Unknown
setting the width of the first column of the combo box to 0 means the
user could enter the values by typing M, F or U, which is usually no
harder than 1, 2 or 3. If the values aren't easily distinguished by
their first couple of characters, e.g.
1 UK citizen
2 EU citizen resident in the UK for more than 24 months
3 EU citizen resident in the UK for more than 6 months
4 EU citizen temporarily resident in the UK
5 ...
I feel it would be better to display both columns, so the data entry
person could continue to type just the number.

 

[Steven L via AccessMonster.com]

Sorry, sorry, sorry, I MEANT comboboxes, I have comboboxes. And I've done the
two column thing with with the zero size first column.

However, now when the results of the query are displayed in a subsequent form
I get the values of the bound column, i.e. the numbers. Is there a way to
tell the form to take the combobox's second column value?

I'd go with combo boxes. If the data entry person is happy entering
numbers I suspect they'd be very unhappy to have to start using option
buttons, which are great for some purposes but lousy for high-speed
touch-typing data entry.

If the values are like
1 Male
2 Female
3 Unknown
setting the width of the first column of the combo box to 0 means the
user could enter the values by typing M, F or U, which is usually no
harder than 1, 2 or 3. If the values aren't easily distinguished by
their first couple of characters, e.g.
1 UK citizen
2 EU citizen resident in the UK for more than 24 months
3 EU citizen resident in the UK for more than 6 months
4 EU citizen temporarily resident in the UK
5 ...
I feel it would be better to display both columns, so the data entry
person could continue to type just the number.

The easy way to do this and make the data entry person happy is to change the
control from a text box to an option group and assign the values for each

[quoted text clipped - 23 lines]

 

[Klatuu]

You can refer to any column in a combo box row by using the Column property.
Note it is zero based, so the first column would be Me.MyCombo.Column(0), the
second would be Me.MyCombo.Locumn(1), etc.

I don't really disagree with John; however, the reason I said use an option
group for a very few options is a data entry person can modify them using the
space bar, so 2 to 4 options is no slower than using a combo.

Sorry, sorry, sorry, I MEANT comboboxes, I have comboboxes. And I've done the
two column thing with with the zero size first column.

However, now when the results of the query are displayed in a subsequent form
I get the values of the bound column, i.e. the numbers. Is there a way to
tell the form to take the combobox's second column value?

I'd go with combo boxes. If the data entry person is happy entering
numbers I suspect they'd be very unhappy to have to start using option
buttons, which are great for some purposes but lousy for high-speed
touch-typing data entry.

If the values are like
1 Male
2 Female
3 Unknown
setting the width of the first column of the combo box to 0 means the
user could enter the values by typing M, F or U, which is usually no
harder than 1, 2 or 3. If the values aren't easily distinguished by
their first couple of characters, e.g.
1 UK citizen
2 EU citizen resident in the UK for more than 24 months
3 EU citizen resident in the UK for more than 6 months
4 EU citizen temporarily resident in the UK
5 ...
I feel it would be better to display both columns, so the data entry
person could continue to type just the number.

The easy way to do this and make the data entry person happy is to change the
control from a text box to an option group and assign the values for each

[quoted text clipped - 23 lines]

Steve

 

[Steven L via AccessMonster.com]

Hi, thanks for all the help, but I think you're slightly misunderstanding
what I'm asking for. Hopefully the following example will clear up my meaning.


I have a combobox called, say, 'district', with two columns, one containing
integers and one containing the district names. The source of the two colums'
value come from two different tables, [combined simple] and variableValues.
The query looks like this:
SELECT DISTINCT [combined simple].c_18district, variableValues.c_18district
FROM [combined simple], variableValues
WHERE [combined simple].c_18district=[variableValues].[number]
ORDER BY [combined simple].c_18district;

So it gets all the values of [combined simple].c_18district and makes them
the source of the first column. It also looks up a table variableValues that
looks a little like this:

number | c_18district
-----------------------------------
1 | East
2 | Western
3 | Outer Suburbs

From there it looks up the number in column one of the combobox and puts the
name of the district in column two accordingly.

This populates the combobox and works fine.

NOW, when I hit a command button I run a query, the results of which appear
in another form, called, say, queryResults. One of the fields in this form is
c_18district (the number), but I want the name instead. Is there a way to use
the number in the textfield to lookup the variableValues table again and
replace the text?

You can refer to any column in a combo box row by using the Column property.
Note it is zero based, so the first column would be Me.MyCombo.Column(0), the
second would be Me.MyCombo.Locumn(1), etc.

I don't really disagree with John; however, the reason I said use an option
group for a very few options is a data entry person can modify them using the
space bar, so 2 to 4 options is no slower than using a combo.

Sorry, sorry, sorry, I MEANT comboboxes, I have comboboxes. And I've done the
two column thing with with the zero size first column.

[quoted text clipped - 34 lines]

 

[Klatuu]

Got the question, Steven. I think you didn't get the answer, but given the
additional information, I can be more specific.
In the query you describe, add a column to it. Use the value in the combo
box column in the Field: row of the query.

Exp1: [forms]![MyFormName]![MyComboBox.Column(1)]

Hi, thanks for all the help, but I think you're slightly misunderstanding
what I'm asking for. Hopefully the following example will clear up my meaning.


I have a combobox called, say, 'district', with two columns, one containing
integers and one containing the district names. The source of the two colums'
value come from two different tables, [combined simple] and variableValues.
The query looks like this:
SELECT DISTINCT [combined simple].c_18district, variableValues.c_18district
FROM [combined simple], variableValues
WHERE [combined simple].c_18district=[variableValues].[number]
ORDER BY [combined simple].c_18district;

So it gets all the values of [combined simple].c_18district and makes them
the source of the first column. It also looks up a table variableValues that
looks a little like this:

number | c_18district
-----------------------------------
1 | East
2 | Western
3 | Outer Suburbs

From there it looks up the number in column one of the combobox and puts the
name of the district in column two accordingly.

This populates the combobox and works fine.

NOW, when I hit a command button I run a query, the results of which appear
in another form, called, say, queryResults. One of the fields in this form is
c_18district (the number), but I want the name instead. Is there a way to use
the number in the textfield to lookup the variableValues table again and
replace the text?

You can refer to any column in a combo box row by using the Column property.
Note it is zero based, so the first column would be Me.MyCombo.Column(0), the
second would be Me.MyCombo.Locumn(1), etc.

I don't really disagree with John; however, the reason I said use an option
group for a very few options is a data entry person can modify them using the
space bar, so 2 to 4 options is no slower than using a combo.

Sorry, sorry, sorry, I MEANT comboboxes, I have comboboxes. And I've done the
two column thing with with the zero size first column.

[quoted text clipped - 34 lines]

Please respond in the newgroup and not by email.

 

[Steven L via AccessMonster.com]

But the problem there is that the value of [forms]![MyFormName]![MyComboBox.
Column(1)] doesn't change. Therefore in the results form the c_18district
textfield will always display the value of [forms]![MyFormName]![MyComboBox.
Column(1)].

What I want is for the value of c_18district to change as I'm scrolling
through the result records.

One method I came up with is to make the c_18district textfield invisible,
create another textfield and then use switch statements in vb to insert the
appropriate text in the new textfield. I find that this method is somewhat
ugly however and feel there's got to be a better solution. Any ideas?

Got the question, Steven. I think you didn't get the answer, but given the
additional information, I can be more specific.
In the query you describe, add a column to it. Use the value in the combo
box column in the Field: row of the query.

Exp1: [forms]![MyFormName]![MyComboBox.Column(1)]

Hi, thanks for all the help, but I think you're slightly misunderstanding
what I'm asking for. Hopefully the following example will clear up my meaning.

[quoted text clipped - 42 lines]