Solved Synchronising a List Box to a Combo Box (1 Viewer)

[davegoodo]

I have a combo box and a list box on a form. I am having trouble with making a selection in the Combo Box and getting the List Box to display the appropriate message. The following details explain the situation:

Combo box name: cboClarity, when clicked it will display one of three values: "Average", "Good" or "Poor".
When a choice is made I would like the list box, called txtExplainer, to display a detailed explanation of what that choice means.

At present when cboClarity is clicked on the first option "Average", the list box will display the appropriate message, but if any other option is selected, i.e. "Good" or "Poor" Access just beeps and won't display any other explanation. I'm guessing that this is because the combo and the list box are not synchronised. The source table tblClarity has 3 fields: ClarityID (Autonumber), Clarity (Short Text) and Explainer (Short Text). The data for the table I put in manually.

I don't know the answer but I imagine the the possible SQL would look like this:

Select ClarityID, Clarity, Explainer
From tblClarity
Where ClarityID = Whichever clarity value is chosen i.e. Average, Good or Poor, (or the respective autonumber which matches those choices)

Somehow the cboClarity value is passed (when a choice is made) to the List Box - txtExplainer, to display more detailed info about the choice.
This is where I don't know and I'm asking for help as to how I link the combo box returned value to the list box to display the relevant explanation.

I imagine it also needs a ReQuery Refresh method somewhere in the AfterUpdate event to keep the data choices in sync.
I would appreciate your wisdom on all of this topic please?
Thank you.

 

[CJ_London]

Why a listbox? Why not a text box? If your combo brings through the explanation in say the third column the text box controlsource would be =cboClarity.column(2)

 

[mike60smart]

I have a combo box and a list box on a form. I am having trouble with making a selection in the Combo Box and getting the List Box to display the appropriate message. The following details explain the situation:

Combo box name: cboClarity, when clicked it will display one of three values: "Average", "Good" or "Poor".
When a choice is made I would like the list box, called txtExplainer, to display a detailed explanation of what that choice means.

At present when cboClarity is clicked on the first option "Average", the list box will display the appropriate message, but if any other option is selected, i.e. "Good" or "Poor" Access just beeps and won't display any other explanation. I'm guessing that this is because the combo and the list box are not synchronised. The source table tblClarity has 3 fields: ClarityID (Autonumber), Clarity (Short Text) and Explainer (Short Text). The data for the table I put in manually.

I don't know the answer but I imagine the the possible SQL would look like this:

Select ClarityID, Clarity, Explainer
From tblClarity
Where ClarityID = Whichever clarity value is chosen i.e. Average, Good or Poor, (or the respective autonumber which matches those choices)

Somehow the cboClarity value is passed (when a choice is made) to the List Box - txtExplainer, to display more detailed info about the choice.
This is where I don't know and I'm asking for help as to how I link the combo box returned value to the list box to display the relevant explanation.

I imagine it also needs a ReQuery Refresh method somewhere in the AfterUpdate event to keep the data choices in sync.
I would appreciate your wisdom on all of this topic please?
Thank you.

Dave
Take a look at the attached.

 

[davegoodo]

Why a listbox? Why not a text box? If your combo brings through the explanation in say the third column the text box controlsource would be =cboClarity.column(2)

Sorry, my mistake. It is a text box, I got my terminologies mixed up. It is actually a text box.

I've opened your archive Combo.zip, that solves it perfectly. That is great, thank you for replying with that example.

That answers my question thanks.

 

[CJ_London]

wasn't my example (it was Mike's) but uses the same method I suggested

 

[davegoodo]

I tried putting =[cboClarity].[Column](2) into the Control Source. My combo box is not working properly.
I'll attach the file I'm working on. cboClarity is on frmClarity.

 

[davegoodo]

I found why it was not working, I overlooked putting the SQL into the RowSource. It is now working fine. Is there a protocol when a thread is finished? I'd like to close this case now. I found the "Solved" button at the top of the thread and clicked it so this thread is now finished from my perspective, thanks.

 

[mike60smart]

I found why it was not working, I overlooked putting the SQL into the RowSource. It is now working fine. Is there a protocol when a thread is finished? I'd like to close this case now. I found the "Solved" button at the top of the thread and clicked it so this thread is now finished from my perspective, thanks.

Your main table "RacingData" has all sorts of problems.

1. "BTID" should be set as an Autonumber Primary Key and NOT a Number DataType.
2. You should not use "Date" for a field name as it is a recerved word in Access. Rename this field to MeetingDate.
3. You should not have spaces in fieldnames.
4. The table needs to be normalised so that you can create a Main Form based on MeetingDates with a Subform containing
related Meetings. This is known as a 1 to Many relationship.

 

[davegoodo]

Thanks for the feedback. I was using this as a practice database, but I recognise the faults you have pointed out.
It was created a couple of years ago (not too sure exactly) but I thought it was good to learn combo boxes properly.
I shouldn't have numbers leading field names like "1 plus" as well as the space.
I appreciate your feedback, it has been very helpful.
Thanks
PS, I've included the reply pressing the button below (I forgot to click on the Reply button).

 

[davegoodo]

Your main table "RacingData" has all sorts of problems.

1. "BTID" should be set as an Autonumber Primary Key and NOT a Number DataType.
2. You should not use "Date" for a field name as it is a recerved word in Access. Rename this field to MeetingDate.
3. You should not have spaces in fieldnames.
4. The table needs to be normalised so that you can create a Main Form based on MeetingDates with a Subform containing
related Meetings. This is known as a 1 to Many relationship.

Sorry I forgot to hit the reply button, but that is my reply above (another error). I'm pretty new to the forum, been a member for a fair while but
haven't gotten into Access for a couple of years until last year, so I'm learning a lot from feedback on the forum.
Thanks again.