Combo Box in a Report

K

keeverman

Hi, I have set up a combo box to show a first name, last name, and suffix.
The only part of this I can get to show up in anything I do is the first name.

The raw source is showing: SELECT Contacts.ID, Contacts.FirstName,
Contacts.LastName, Contacts.Suffix, * FROM Contacts ORDER BY
Contacts.[LastName];

The control source is showing: Client (which is the title of the column).

I have tried everything to get all three columns to show but nothing is
working.

I even read back through other questions regarding this issue and tried a
couple of suggestions that dealt with this but nothing helped.

I am using Access 2007

Thank you!
 
P

pietlinden

Hi, I have set up a combo box to show a first name, last name, and suffix..  
The only part of this I can get to show up in anything I do is the first name.

The raw source is showing:  SELECT Contacts.ID, Contacts.FirstName,
Contacts.LastName, Contacts.Suffix, * FROM Contacts ORDER BY
Contacts.[LastName];

The control source is showing:  Client (which is the title of the column).

I have tried everything to get all three columns to show but nothing is
working.

I even read back through other questions regarding this issue and tried a
couple of  suggestions that dealt with this but nothing helped.

I am using Access 2007

Thank you!
Click to expand...

This column isn't set up as a lookup in your table, is it? If it is,
you should change it. Lookups are a disaster! (What you see is NOT
what you get!) You should be able to do this with a simple query.

Your table structure should be something like
ContactID,
FirstName,
LastName,
Suffix,
...

BTW, using
"SELECT * FROM <table>" means "return/show data from all fields in the
<table/query>", so having "SELECT *" and then having more column names
from the table is redundant. You will get a second column with the
same data in it.
 
K

Ken Sheridan

Kathy:

A combo box only shows the first visible column, so you'd have to set it up
like this so that the first name, last name and suffix values are
concatenated into a single column, and the bound first column is hidden:

RowSource: SELECT ID, FirstName & " " & LastName & " " & Suffix FROM
Contacts ORDER BY LastName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

However, you should really only do it that way in a form. It will work in a
report, but its not the best approach. A better way would be to add the
Contacts table to the report's RecordSource query, joining it to the current
table on the Client/ID columns. Then in the 'field' row of a blank column in
the query design grid put (as a single line)

ClientFullName: [Contacts].[FirstName] & " " & [Contacts].[LastName] & " " &
[Contacts].[Suffix]

In the report you can then use a text box with its ControlSource property
set to:

ClientFullName

Ken Sheridan
Stafford, England
 
K

keeverman

Thank you, I will try this.

Actually, the Select part is what access automatically did. But it's nice
to know more about it.
--
Kathy


[email protected] said:
Hi, I have set up a combo box to show a first name, last name, and suffix..
The only part of this I can get to show up in anything I do is the first name.

The raw source is showing: SELECT Contacts.ID, Contacts.FirstName,
Contacts.LastName, Contacts.Suffix, * FROM Contacts ORDER BY
Contacts.[LastName];

The control source is showing: Client (which is the title of the column).

I have tried everything to get all three columns to show but nothing is
working.

I even read back through other questions regarding this issue and tried a
couple of suggestions that dealt with this but nothing helped.

I am using Access 2007

Thank you!
Click to expand...

This column isn't set up as a lookup in your table, is it? If it is,
you should change it. Lookups are a disaster! (What you see is NOT
what you get!) You should be able to do this with a simple query.

Your table structure should be something like
ContactID,
FirstName,
LastName,
Suffix,
...

BTW, using
"SELECT * FROM <table>" means "return/show data from all fields in the
<table/query>", so having "SELECT *" and then having more column names
from the table is redundant. You will get a second column with the
same data in it.
Click to expand...
 
Top