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

althaherm

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
I would have a small table (not knowing what else is in your database)

Something like this
tblChoices tblChoices

IDChoiceValueChoiceDescription
1​
BadIllumination Very Low
2​
AcceptableIllumination Low
3​
GoodIllumination good
4​
Very GoodBrightly 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
combo.jpg

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

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
I would have a small table (not knowing what else is in your database)

Something like this
tblChoices tblChoices

IDChoiceValueChoiceDescription
1​
BadIllumination Very Low
2​
AcceptableIllumination Low
3​
GoodIllumination good
4​
Very GoodBrightly 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.
 

Attachments

  • ViolationT.png
    ViolationT.png
    29.9 KB · Views: 227

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • Choices.accdb
    760 KB · Views: 251

althaherm

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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. (y)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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.
 

Attachments

  • Database3.accdb
    560 KB · Views: 238

althaherm

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
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

You've got your good things, and you've got mine.
Local time
Today, 06:05
Joined
May 21, 2018
Messages
8,463
I am guessing you may have other rankings, but either way you need one table not two
tblRanking tblRanking

RankingIDLevelofConcernDescriptionCategoryRankingSort
1​
GoodAir_quality_is_satisfactoryAir Quality
1​
2​
ModerateAir quality is acceptableAir Quality
2​
3​
Unhealthy_for_ Sensitive_GroupsAir quality may have minor effectsAir Quality
3​
4​
UnhealthyAir quality may have serious effectsAir Quality
4​
5​
Very_UnhealthyHealth alertAir Quality
5​
6​
HazardousHealth warningAir Quality
6​
7​
GoodClear no contaminantsWater Quality
1​
8​
FairNo contaminants but CloudyWater Quality
2​
9​
PoorHeavy sediments and toxinsWater 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

IDDateWindSpeedWindDirectionrankingID_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

DateWindSpeedWindDirectionLevelofConcernDescription
4/1/2021​
12​
NModerateAir quality is acceptable
3/31/2021​
20​
SUnhealthyAir quality may have serious effects
Sam on the form.
 

Attachments

  • airquality_MajP.accdb
    640 KB · Views: 259

althaherm

New member
Local time
Today, 12:05
Joined
Mar 31, 2021
Messages
22
I am guessing you may have other rankings, but either way you need one table not two
tblRanking tblRanking

RankingIDLevelofConcernDescriptionCategoryRankingSort
1​
GoodAir_quality_is_satisfactoryAir Quality
1​
2​
ModerateAir quality is acceptableAir Quality
2​
3​
Unhealthy_for_ Sensitive_GroupsAir quality may have minor effectsAir Quality
3​
4​
UnhealthyAir quality may have serious effectsAir Quality
4​
5​
Very_UnhealthyHealth alertAir Quality
5​
6​
HazardousHealth warningAir Quality
6​
7​
GoodClear no contaminantsWater Quality
1​
8​
FairNo contaminants but CloudyWater Quality
2​
9​
PoorHeavy sediments and toxinsWater 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

IDDateWindSpeedWindDirectionrankingID_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

DateWindSpeedWindDirectionLevelofConcernDescription
4/1/2021​
12​
NModerateAir quality is acceptable
3/31/2021​
20​
SUnhealthyAir 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
 

Users who are viewing this thread

Top Bottom