Joining Multiple Bound Columns

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi,

The form has a Combo box that when it is opened, it
provides a list of items (say, 15 rows, 5 columns). A
SELECT statement is used in the Row Source in properties.

Because it is bound to a particular column, say 1, it
returns the value from that particular column once
selected.

Is it possible to return all of the values by way of
merging all the values in the row together into the one
single combo text box once accepted?

It's similar to using a statement like:

=Forms!abc!CustNo &", "& Forms!abc!CustName

in the default value line, but I'm trying to do the same
with the SELECT statement in a combo box.

Can this be done, may I ask?

Thanks for your help.

Adam
 
You can join them together like this:

SELECT YourID, LastName & ", " & FirstName AS FullName, Street, City & ", "
& State & " " & Zip AS CSZ FROM YourTable"

Your users would then view this as:

1 | McDaniel, Scott | 123 Main | Anytown, SC 11111
2 | Jones, Jim | 111 Main| AnotherTown, GA 12345

Is that what you mean? Or, do you want to return ALL the values associated
with a particular combo row when the user selects a value from the combo? To
do that, add code like this in the AfterUpdate event of your combo:

YourStringVariable = Me.YourCombo.Column(1) & ", " & Me.YourCombo.Column(2)
etc etc

Of course, your combo is bound, so the underlying table will ALWAYS save the
value of the bound column ... and, if you're wanting to save this
concantenated information to THAT column, then I'd say it's time to
re-examine your data structure ... it's never a good idea to store multiple
values in a single column in your database.

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "
 
Thank you Scott.

The first solution is what I've already got. We see the
ID, lastname, firstname once we drop the box open. Once we
select a particular row, the ID number is retured. That is
we only see the ID number in the box.

So, it's the second solution that may work. But, I am now
having doubts about doing this at all because as you
pointed out, having multiple values is not a good idea. I
think it may breach one of the normalisation rules.

What I was trying to do was to crate a "label" type of
data entry so that if we select a particular company, the
name, address, postcode, phone no. would all be extracted
from the underlying table, and posted to one "cell" in a
historical table (ie New Line in Field). It looks that I
should keep the values seperate.

I'll think about it though.

Many thanks

Adam
 
Back
Top