Solved Listbox, not displaying combobox text (1 Viewer)

[allen675]

Hello fellow Access peep's,

I have a list box on my main menu which has a query behind it showing me all leads that require a follow-up. The list box contains First name, surname, lead date & status. All but the status column are displaying correctly. The value however in the status column shows a number rather than the text value.

The status value when entering a new lead into the DB is done by selecting from a combobox. The combobox gets it's info from a table called status. I suspect the problem to be that the list is picking up the StausID value and somehow I need to reference the column of the combobox that contains the text I want to display. If this is the case I'm not sure how to do this therefore I am here asking for your help.

Thanks in advance

 

[Gasman]

Sounds like you are using table lookups? :-(
Remove them and process the lookup yourself. The ID is what should be stored, not the description.

 

[allen675]

Sounds like you are using table lookups? :-(
Remove them and process the lookup yourself. The ID is what should be stored, not the description.

Gasman,

Appreciate the prompt response.

Your going to have to assume your talking to a noob here and so therefore I say, you what????

Is table lookup the method used when using the Access wizard?

 

[allen675]

Gasman,

Appreciate the prompt response.

Your going to have to assume your talking to a noob here and so therefore I say, you what????

Is table lookup the method used when using the Access wizard?

Gasman,

I follow now what you are saying after a think about it.

I've used look up tables as I need the end user to be able to add and remove items from the combobox so unfortunately I don't think your suggestion is going to work?

 

[Gasman]

No, you can still use lookups, but you do them yourself. That way you know what is being stored.
Read this link http://access.mvps.org/access/lookupfields.htm

 

[allen675]

No, you can still use lookups, but you do them yourself. That way you know what is being stored.
Read this link http://access.mvps.org/access/lookupfields.htm

Okay thank you had a read of the link there certainly seems to be some disadvantages to lookup fields.

So my next question is how, if I'm not too use the wizard?

 

[Pat Hartman]

You really, really don't want to allow updates to the list on the fly. If you have a list that is changable, it is best to use a table to manage it. You have better control. Here's a link to a mini-app I import into all my applications which is used to manage lists. It is probably overkill for what you need but if you have more than one list to manage, it will actually save you time so do take a look at it.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[Pat Hartman]

There is no advantage to table level lookups. They are a crutch for novices who don't know how to create a query. They only lead to problems. Lookups should be done with combos on a form. Do not confuse the two. Users NEVER view tables and queries directly. Only the programmer works with tables and queries. The user works only with forms and reports.

 

[allen675]

You really, really don't want to allow updates to the list on the fly. If you have a list that is changable, it is best to use a table to manage it. You have better control. Here's a link to a mini-app I import into all my applications which is used to manage lists. It is probably overkill for what you need but if you have more than one list to manage, it will actually save you time so do take a look at it.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

Hi Pat,

Thanks for your suggestion.

I'm happy with the DB as it stands it's just this bit I really need to sort.

 

[Pat Hartman]

If you want the user to be able to add items to the list using a form, the list needs to be a table. The sample I linked to does that.

If you want to stick to the table level lookups, that is your choice. Just be prepared to work around them whenever they get in your way as they are now.

 

[allen675]

If you want the user to be able to add items to the list using a form, the list needs to be a table. The sample I linked to does that.

If you want to stick to the table level lookups, that is your choice. Just be prepared to work around them whenever they get in your way as they are now.

Well thankfully this is the only one I currently need to and likely the only one I will ever need to navigate. So if I could just suss this little problem out then I will be laughing

 

[Pat Hartman]

Why are you using combos to display the status' on one form but textboxes on the other? Always using combos solves the problem without having to modify the query.

 

[allen675]

Why are you using combos to display the status' on one form but textboxes on the other? Always using combos solves the problem without having to modify the query.

Sorry I don't follow, I've not mentioned anything about a text box!?

 

[Pat Hartman]

I think I was replying to a different thread. Please just ignore the remark and I'll try to figure out where I should have posted

For you, I thought Gasman had provided a solution with the link but he didn't. The solution is to bind the listbox to a query that joins to the lookup table where you select the text value. This is how you would always work with the lookup value if you didn't have the table level lookup.

 

[mike60smart]

Hello fellow Access peep's,

I have a list box on my main menu which has a query behind it showing me all leads that require a follow-up. The list box contains First name, surname, lead date & status. All but the status column are displaying correctly. The value however in the status column shows a number rather than the text value.

The status value when entering a new lead into the DB is done by selecting from a combobox. The combobox gets it's info from a table called status. I suspect the problem to be that the list is picking up the StausID value and somehow I need to reference the column of the combobox that contains the text I want to display. If this is the case I'm not sure how to do this therefore I am here asking for your help.

Thanks in advance

Hi

In the Listbox Row Source click on the 3 Dots at the end of the Row (...)
This opens the Query Builder
Add the Status table
Add the Status Field to the Grid
Delete the StatusID field from the Grid
The List box will now display the data required