wrong column order when displaying query in a list box

[merlin777]

I am displaying a query on a form by putting it in a list box. This is perfect for my needs because it's a simple, tidy look and I don't need to change any data using it.



However, although the query displays correctly in datasheet view, in the list box the columns are in the wrong order.

Also, where the query displays a linked value rather than the id number for a field, the list box just displays the id number (for instance, one of the fields in my main table is 'therapistid' and is a number that links to the 'tbltherapist' but actually displays a therapist name and so does the query whereas the list box shows the number).

any idea what I'm doing wrong?

[June7]

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the descriptive text in listbox, its RowSource will have to be a query that joins tables.

[alansidman]

I think that the issue may be in the properties for the list box. Here is a link to some tutorials. Scroll down to the section for combo boxes. It uses the same principle for the list boxes.

http://www.datapigtechnologies.com/AccessMain.htm

[alansidman]

crossposted: http://www.mrexcel.com/forum/microso...y-subform.html

[merlin777]

wow - these tutorials are fantastic.

[June7]

Use an SQL statement in the listbox or combobox RowSource to explicitly specify column order.

[merlin777]

before you make the order explicit, where does it derive the order from by default?

[merlin777]

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the descriptive text in listbox, its RowSource will have to be a query that joins tables.

Could you bear to give me an example please?

[June7]

Just build a query that joins the data table with the lookup source table. You can open the query builder by clicking the ellipsis (...) in the RowSource property.

[merlin777]

by 'join' do you mean use any expression in a query field that contains fields from the 2 tables (e.g. concatenate the 2 fields) or is there a specific way to join them?

[June7]

Join tables by linking on the key fields. This is not concatenation. This is basic Access functionality using the query builder.

[merlin777]

excuse my clumsy question. I used concatenation as an example of a function to put in the query that uses both tables - didn't mean concatenation in the sense of 'joining' tables.

Specifically where do I link the key fields, please?

[June7]

Did you search Access Help?

http://www.opengatesw.net/ms-access-...stent_font_use

Create the link line by click on one field in a table and drag to field in other table.

[merlin777]

Thanks for the link. I do use help but sometimes it's trick if you don't use the right key words.

[merlin777]

I just wanted to say thanks to all who have responded over th last day or two. I've spent most of the day really playing and hammering away and although I will still have questions the mists are starting to clear. Thanks!