inserting multiple columns from combo list

[andre]

Hey everybody,

I am a newbie to access. I trying to pull employee firstName, lastName, and
middleInitial from a table and insert them into one employee field in a
timecard table.

I get the drop down list to show complete names in the 3 columns but when I
choose a name I only get the first name inserted into the employee field.

Evidently the query is finding everything but I can't get them to
cancatenate into the field.

Any help is greatly appreciated.

Thanks,

Andre

 

[Klatuu]

Use the Column property of the combo box. The column reference is 0 based,
so assuming your columns are First, Middle, Last and you want to concatenate
them in that order:

Me.MyTextBox = Me.MyCombo.Column(0) & " " & Me.MyCombo.Column(1) & " " &
Me.MyCombo.Column(2)

 

[Ron2006]

There are multiple things going on at the same time for a combo box.

A) There is a "Bound" Column. This is the ONE field from the query
that will be saved in a field if the combo box is bound to a field in
a table. It is also the field value that will be returned if you
simply address the combobox field (example msgbox me.comboboxname)

B) There is the number of columns which tells you HOW Many fields are
being returned from the query.

C) There is the FIELD Lengths which define how much space (if any)
each field in that combo box will use in the display when you press
the arrow at the end of the combo box. Once you chose one of the
entries, ONLY the first field that has an actual length will show in
the combobox space, and this may or may NOT be the value that is saved/
stored (item A above)

D) There is the "Limit to List" property. This will say whether or not
an entry can be made in the box that is NOT in the underlying query in
the field that is visible. (the first non 0 lengh field returned from
the query.


You can address any and ALL fields of data returned from the query by
using the .column property as Dave showed you in the prior post.

Dave's answer assumed that you were trying to load a separate txtbox
with the information from the selected emplyee. That will work BUT the
only item that will show in the combo box - given the way you are
pulling the info with the query - is one of the name fields. in which
case you will have a problem if there are multiple entries for the
same single field value showing. (For instance Smith when there are
multiple lastname Smiths in the emplyee list.)

If you want to show all three in the "COMBOBOX" then you will need to
create a dynamic field in your query that concatenates the three into
a single field and have that be the visible and maybe bound field.

All of the above is why - for an employee table - the employee number
is usually the bound field and first displayed field and the name etc
are also shown with the dropdown BUT then loaded for display into a
separate txt field. That way you will always have the right person's
information saved in your record and displayed EVEN when she gets
married/divorced and changes her name.

And the part number is saved/shown so that when the description
changes your tables still work.

Ron

 

[andre]

Thanks a bunch