Combo Box Source from Same Table

[Guest]

Hi,

I have a table tblPeople -

ID (AutoKey)
LastName
FirstName
Phone
HeadofHousehold (Yes/No)
HeadID

it should hold the names of people, and if they are of the same family, just
the head of household should have the phone, the rest, should "point" to him
using Head ID.

So the data should be:
ID LastName First Name Phone HeadofHousehold Head ID
== ====== ======= ==== =========== ======
1 Smith John 555-555-5555 YES
2 Smith Jane NO
1
3 Smith Child NO
1
4 Peeves George 512-222-2222 YES

Etc...

I would like the HEAD ID to be a combo box, and use the following query:
SELECT tblPeople.ID, tblPeople.LastName, tblPeople.FirstName FROM tblPeople
where tblPeople.HeadofHousehold = YES;

Now what is happening is that it the combo box shows ONLY the IDs...
so I get a list:
1
4

Whereas I want it to show:
1 Smith John
4 Peeves George

Any idea how to make it happen?

 

[RuralGuy]

On the Format tab of the ComboBox set the ColumnCount = 3,
ColumnWidths = 1";1";1", ListWidth = 3"

 

[Guest]

Thanks! - that did it

 

[Larry Linson]

SELECT tblPeople.ID, tblPeople.LastName, tblPeople.FirstName FROM
tblPeople
where tblPeople.HeadofHousehold = YES;

Now what is happening is that it the combo box shows ONLY the IDs...
so I get a list:
1
4

Whereas I want it to show:
1 Smith John
4 Peeves George

Any idea how to make it happen?

In your Combo Box properties, select a Column Count of 3 (in the Format
tab), and a Bound Column of 1 (in the Data Tab).

I'd use a calculated Field so that the name showed as "Smith, John",
"Peeves, George" -- the following is the SQL of the RowSource I use in a
very similar combo box:

SELECT [LastName] & ", " & [FirstName] AS EmpName, Employees.EmployeeID
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;

with Columns = 2, Bound Column = 2, and Column Widths = 1.5"; 0", it only
shows the names but is bound on the EmployeeID column. There's no need to
display the ID value -- it's only for your own internal use. It would show
the user just

Smith, John
Peeves, George

Larry Linson
Microsoft Access MVP