Putting three list boxes once selected into one column

[Phil Maskell]

Hi,
Just joined, am looking for some expert advice and hoping I can get it from here.
I have a MS Access 2003 database with several tables, queries, forms etc.
My question is I have a simple form which has 5 boxes on it listing information from tables and queries. One of the boxes is linked to a lookup where I can get it to dispaly three values but once the user selects which one they want it only displays the first value. I want it to display all three under the one column so to speak. The Lookup lists Order, Forenames and Surname but only lists the Order when selected. Is there any way I can get all three values under the one column rather than having to put three boxes with three lookup's and then the user having to do it three times to get the information?
Grateful for any advice
thanks

[sierra7]

Hi Phil,
You need to create a query which includes the three fields and use this as the basis of your RowSource for the combo. You can either build the query in the combo or save a query and give it a name then enter the name as the Rowsource.

To display three columns you must set the ColumnCount to 3, set the widths, try "2;2;2cms" to start with, then set the total Width from Auto to 6cms.

There are issues with Bound Column and whether you need hidden columns (where width =0) but this should get you started.

Have fun!
S7

[Phil Maskell]

Have created a query which gives the 158 records in three columns titled Order, Forename and Surname, that works great and lists all the details. My problem is when I use that query in the form, I want those three fields from a drop down to automatically be inserted once selected. e.g. Order, Forename, Surname, user selects and then all three bits of information from the three columns is filled in. At the moment my form has 5 fields on it Order (which has the three drop downs) Date Attended and Date Next due, once the user selects the drop down it is only putting the Order number in and not all the information. Any clearer?

[NeoPa]

Ah, I was afraid this might be what you were after when I answered your other thread. This question is sufficiently different to be allowed as a separate question, but nevertheless I believe keeping it all together may have made things a little clearer and easier to deal with. No matter.

Only one column can be bound to the control. As such, unless you create a query where all three values are joined together into a single string value, it is not possible to return all values in a single control. It is possible, using .Columns() and some VBA code, to populate other controls on your form whenever an update (selection) is made to the ComboBox.

However, and this is a very big however, it seems that what you are attempting to do is contrary to Normalised Database theory, and as such is strongly advised against. Check out this very important and seminal subject for databases (Database Normalisation and Table structures). I cannot express how important such an understanding is to doing anything worthwhile with databases.

In a normalised database you would expect to store only a link to the record of the other table in the one being maintained. It is almost never a good idea to copy such data across en masse.

[Phil Maskell]

Thanks for your response, having finally 'cracked' it, you can do it in a query, I have created an expression drawing the fields from the table, i.e.

Expr1: [Person Details]!Order & [Person Details]!Forenames & [Person Details]!Surname

Thanks for the responses

[NeoPa]

Spaces in there would make it more recognisable, but we don't know your exact requirement so what you have may be adequate. The important things is that you have what you need :-)