Solved Link Text Box to another Text Box (1 Viewer)

[althaherm]

HiAll,

I have a form that contains a drop-down text box with the values "Bad, Acceptable, Good, V Good" and I have another drop-down text box that contains "illumination is very low, illumination is low, illumination is acceptable" and so on.

what I am trying to do is when I select "Bad" in text box 1 the text box 2 value will change to "Illumination is very low" and so on.

Can anyone help me with how to do it kindly?

 

[MajP]

drop-down text box

In all software applications these are called combo boxes. Are these combobox row source values coming from a table or are they from a value list? Can you explain a little more what you are doing and why. There are lots of ways to do this, but I am trying to understand why you would have two linked comboboxes.

 

[althaherm]

In all software applications these are called combo boxes. Are these combobox row source values coming from a table or are they from a value list? Can you explain a little more what you are doing and why. There are lots of ways to do this, but I am trying to understand why you would have two linked comboboxes.

Dear MajP,

Thank you for your replay, I have this set up so I can give more information and details at the same time I need to ensure that whoever is doing data entry to the form will know what does "Bad" stands for, so when he or she chose "Bad" immediately will see the explanation of "bad".


Ps: Yes it is all coming from a tables.

Thanks in adavance.

 

[MajP]

I would have a small table (not knowing what else is in your database)

Something like this
tblChoices tblChoices

ID	ChoiceValue	ChoiceDescription
1​	Bad	Illumination Very Low
2​	Acceptable	Illumination Low
3​	Good	Illumination good
4​	Very Good	Brightly Illuminated

Then I can base my combo off my table. I can just store the ID 2 (which represents Acceptable)

Then you can display like this when you make your choice

Notice that it only displays one column after you choose a value.

To give you a better answer we would need to see a sample database or get more information on your form and how it relates to your tables.

 

[althaherm]

I would have a small table (not knowing what else is in your database)

Something like this
tblChoices tblChoices

ID	ChoiceValue	ChoiceDescription
1​	Bad	Illumination Very Low
2​	Acceptable	Illumination Low
3​	Good	Illumination good
4​	Very Good	Brightly Illuminated

Then I can base my combo off my table. I can just store the ID 2 (which represents Acceptable)

Then you can display like this when you make your choice
View attachment 90434
Notice that it only displays one column after you choose a value.

To give you a better answer we would need to see a sample database or get more information on your form and how it relates to your tables.

Thanks very much, I will try that and see how it will turn.
attached one of the tables, if you see the Violation Rate (Green) is linked to the Green text box under (Extremely Low) it worked well in this because its number to text but when I am trying text to text it is not working.

 

[MajP]

Looking at the picture are you talking about the Green Fields. If that is the case I think even if you had two columns to choose, you still want to have a visible choice as well. I would have a combobox to make my choice, then the second textbox can be a dlookup or simpler to include in the forms query.

 

[althaherm]

Looking at the picture are you talking about the Green Fields. If that is the case I think even if you had two columns to choose, you still want to have a visible choice as well. I would have a combobox to make my choice, then the second textbox can be a dlookup or simpler to include in the forms query.

I used the following expression and it worked well with the form I sent but it did not work in the "Bad" thing form.

=IIf([ViolationRate]=1,"Extremely Low",IIf([ViolationRate]=2,"Low",IIf([ViolationRate]=3,"Medium",IIf([ViolationRate]=4,"High",IIf([ViolationRate]=5,"Extremely High")))))

 

[MajP]

I really do not recommend such a long winded, inflexible, hard wired approach. Look at my example. I can add, delete, modify as needed without some complicted iif that is impossible to debug. Do not do that.

 

[althaherm]

I really do not recommend such a long winded, inflexible, hard wired approach. Look at my example. I can add, delete, modify as needed without some complicted iif that is impossible to debug. Do not do that.

Thanks for the advice, I am just a beginner so am getting some access info here there, and so on.
I will try to understand how you did the example you sent me and if I manage to understand it, will be great.

 

[MajP]

Can you post an example of your database? There are other ways this can be done as well. However, it will depend on what value you are saving into your table. From you picture I assume you are saving violation rate and the text below it. You could also do a dlookup and make the field a calculated control. You could even do it in code when the record changes or when the combo box choice changes. What I did was probably the easiest and least amount of effort I just linked the description into the forms query. Then I can use the description directly. No code.
Sorry for being critical, but if you type that iif you hard wired the database. Any update requires you to redo the code. It is always better to store data like this in a table so when you want to change you just update a record in the table, and not redo code.

 

[althaherm]

Can you post an example of your database? There are other ways this can be done as well. However, it will depend on what value you are saving into your table. From you picture I assume you are saving violation rate and the text below it. You could also do a dlookup and make the field a calculated control. You could even do it in code when the record changes or when the combo box choice changes. What I did was probably the easiest and least amount of effort I just linked the description into the forms query. Then I can use the description directly. No code.
Sorry for being critical, but if you type that iif you hard wired the database. Any update requires you to redo the code. It is always better to store data like this in a table so when you want to change you just update a record in the table, and not redo code.

Agree, you have a good point which I did not think about, once again I am learning and honestly, I learned good things just by your replays. I guess I was on the right track when I signed up for this forum.

you will find attached the part of the Air Quality and its related tables. looking forward to hearing more advice.

 

[althaherm]

Agree, you have a good point which I did not think about, once again I am learning and honestly, I learned good things just by your replays. I guess I was on the right track when I signed up for this forum.

you will find attached the part of the Air Quality and its related tables. looking forward to hearing more advice.

Hi,

Any Feedback about the table i sent?

That will help me a lot in reorganizing my database.
Regards.

 

[MajP]

I am guessing you may have other rankings, but either way you need one table not two
tblRanking tblRanking

RankingID	LevelofConcern	Description	Category	RankingSort
1​	Good	Air_quality_is_satisfactory	Air Quality	1​
2​	Moderate	Air quality is acceptable	Air Quality	2​
3​	Unhealthy_for_ Sensitive_Groups	Air quality may have minor effects	Air Quality	3​
4​	Unhealthy	Air quality may have serious effects	Air Quality	4​
5​	Very_Unhealthy	Health alert	Air Quality	5​
6​	Hazardous	Health warning	Air Quality	6​
7​	Good	Clear no contaminants	Water Quality	1​
8​	Fair	No contaminants but Cloudy	Water Quality	2​
9​	Poor	Heavy sediments and toxins	Water Quality	3​

If all you ever do is Air quality then you can get rid of the category field. The sort field allows you to at a later date add more rankings and still sort the list in the way you want.

Now only ever store the RankingID not the other fields. You bring the other fields in with a query.
So in your air quality table you store only the rankID. You do not want to store level of concern and description. This is Database 101.

AirQuality AirQuality

ID	Date	WindSpeed	WindDirection	rankingID_FK
1​	4/1/2021​	12​	N	2​
2​	3/31/2021​	20​	S	4​

But in a query you link rankID_fk to tbl ranks and then you can show both
Query1 Query1

Date	WindSpeed	WindDirection	LevelofConcern	Description
4/1/2021​	12​	N	Moderate	Air quality is acceptable
3/31/2021​	20​	S	Unhealthy	Air quality may have serious effects

Sam on the form.

 

[althaherm]

I am guessing you may have other rankings, but either way you need one table not two
tblRanking tblRanking

RankingID	LevelofConcern	Description	Category	RankingSort
1​	Good	Air_quality_is_satisfactory	Air Quality	1​
2​	Moderate	Air quality is acceptable	Air Quality	2​
3​	Unhealthy_for_ Sensitive_Groups	Air quality may have minor effects	Air Quality	3​
4​	Unhealthy	Air quality may have serious effects	Air Quality	4​
5​	Very_Unhealthy	Health alert	Air Quality	5​
6​	Hazardous	Health warning	Air Quality	6​
7​	Good	Clear no contaminants	Water Quality	1​
8​	Fair	No contaminants but Cloudy	Water Quality	2​
9​	Poor	Heavy sediments and toxins	Water Quality	3​

If all you ever do is Air quality then you can get rid of the category field. The sort field allows you to at a later date add more rankings and still sort the list in the way you want.

Now only ever store the RankingID not the other fields. You bring the other fields in with a query.
So in your air quality table you store only the rankID. You do not want to store level of concern and description. This is Database 101.

AirQuality AirQuality

ID	Date	WindSpeed	WindDirection	rankingID_FK
1​	4/1/2021​	12​	N	2​
2​	3/31/2021​	20​	S	4​

But in a query you link rankID_fk to tbl ranks and then you can show both
Query1 Query1

Date	WindSpeed	WindDirection	LevelofConcern	Description
4/1/2021​	12​	N	Moderate	Air quality is acceptable
3/31/2021​	20​	S	Unhealthy	Air quality may have serious effects

Sam on the form

Thank you, I am now going back to restudy again. looks that I need more time in this so I can improve my skills. all the best