List Box based on query

[Blackwidow]

Argghh.. I can't get this to work! I have a list box based on a query. The query is based on tblSolutionPartner which has partnerID, PartnerName,Expertise, additionalexpertise1,additionalexpertise2,additionalexpertise3.

The Four Expertise fields are lookups from tblSolutionMainCategory.

The problem is when I come to doing the list box, despite the query having the names in, the list box pulls the uniqueID for each field, which is no good...

How can I get it to populate with the solutionmaincategory name instead of the SolutionmaincategoryID???

Any help would be gratefully received

 

[Galaxiom]

I assume you actually want the the value of the textbox to be the ID but display the name.

Your query should get both ID and name in that order.
In the listbox properties:
Bound Column:1
Column Count: 2
Column Widths: 0cm;3cm (zero hides the ID, set the name column width to suit)

 

[Blackwidow]

Re: List Box based on query

No thats not exactly what I mean I'll try to explain better.

I have a table called tblSolutionPartners with lots of field in but the ones I have in my query are:

SolutionPartnerID - Autonumber
SolutionPartner -Text
PrincipleExpertise - Which is a Lookup to tblSolutionMainCategory (MainCategoryID, MainCategory) Bound column 1, column count 2, column width 0cm,3.254cm

Now when I am come to running the query I've based the query on tblSolutionPartners and taken the following:
SolutionPartnerID
SolutionPartner
PrincipleExpertise.


I can see why it is doing it, the list box wants to take the MainCategoryID from the lookup but I want the name..

The query itself shows the name under PrincipleExpertise, but the list box is pulling the MainCategoryID..

I am guessing I have probably set this up wrong from the start? Though not sure, really is quite urgent I finish this though, so any help would be most appreciated!



To add to the complication I also have three additionalExpertise fields 1,2,3 which are also linked in the same way, which I want to show in the list box too

 

[Galaxiom]

I suspect you are right in thinking you need to reconsider the structure.
Usually the case if you find you are making multiple fields for the same kind of data, such as Additional Expertise 1,2,3.

Normally I would expect these to be in a table with a field for expertiseID and a field for the personID it applies to. One record for each person/expertise combination. But maybe you have this and your problem is with the next phase of presenting the data. Can't tell from what you have posted.

You can refer to the field you want from a combo/listbox by:
comboboxname.column(x)
Columns start numbering from zero in this context.

To use this value in a query, make a textbox whose Record Source is this expression and refer to the textbox in the query.