Combined "person" field on form

[Remster]

Folks

I expect this one has been asked a hundred times before, but I can't find it anywhere.

I have two tables, one of which is a "person" table consisting of fields for employee number (primary key), last name and first name. The second table contains a person field that has a many-one relationship to the employee number field in the person table.

I also have a form that's to be used for inputting data to the second table. Now, since the people inputting the data will know the people whose names are to be input by first and last name but not necessarily by employee number, I want the values in my combo box list to have the following format: last_name, first_name (employee_number). But how can I then have only the employee number entered into the second table?

Thanks



Remster

[pbaldy]

You would have a row source along the lines of:

SELECT employee_number, last_name & ", " & first_name & " (" & employee_number & ")" AS FullName
FROM TableName

You'd set the bound column property of the combo to 1, which would make the employee number the value that was saved. You'd likely want the column widths property to be something like:

0"; 2"

to hide the employee number field.

[Remster]

Thanks for such a swift reply! I'll give it a crack when I get to work tomorrow and let you know how I fare.

[Remster]

I think I've nailed it. I have this as my Row Source:

SELECT [Last name] & ", " & [First name] & " (" & [Employee number] & ")", [Employee number] FROM People

I then set my Column Count to 1 and my Bound Column to 2.

This seems to work. Can you envisage any problems?

[pbaldy]

Why would you have a column count of 1 when there are 2? You won't be able to reference that column if you need to. The way to hide it is with a column width of zero.

[Remster]

Ah, I didn't understand, but I've followed your suggestion now (I couldn't quite get it to work earlier).

One more question for you:

Is it possible to populate a combo box with data from more than one table?

[pbaldy]

Sure; the row source can be a query that gets data from multiple tables. You can use a saved query as the row source or click on the ellipsis to the right to be taken into the query builder.

[Remster]

Hmm, what I can't work out is how to get the right items in my list. Here's my code:

SELECT Forms.[Form reference], [Operational Instructions].[OI number], Procedures.[Procedure code] FROM Forms, [Operational Instructions], Procedures;

But the combo box displays only OI numbers several times each (Bound Column is set to 2).

Sorry if this is really basic, but I'm new to this.

[Remster]

I should add that the only alternative I've been able to produce is three parallel columns, which isn't what I want either. I want all the data from all three fields to be available in one list.

[Remster]

I think I might have it:

SELECT [Form reference] FROM Forms UNION SELECT [OI number] FROM [Operational Instructions] UNION SELECT [Procedure code] FROM Procedures;

Does that look right? I plan to give it a go tomorrow.

[pbaldy]

The UNION query looks right, given your desired output. Your effort in post 8 would have produced a Cartesian product without joins between the tables.

[Remster]

Well, it joins my fields as desired. However, most of the values are hyperlinks, and once I use 'UNION' I no longer get this in the combo box ...

Remster

... but instead I get this ...

Remster#http://www.remster.com

I realise that the latter is what I keyed in to generate my hyperlinks in the first place. But is there anything I can add to the SQL to get the right appearance in my combo box? I've read somewhere that this problem can be resolved by using "nested ANDs and ORs",but I'd have no idea how to achieve this.

[Remster]

Got it!

SELECT HyperlinkPart([Form reference],0) FROM Forms UNION SELECT HyperlinkPart([OI number],0) FROM [Operational Instructions] UNION SELECT HyperlinkPart([Procedure code],0) FROM Procedures;

[pbaldy]

Ah good, glad you found the answer. I don't use hyperlink fields, and wasn't familiar with that function.

[Remster]

As it happens, I had to make it even more complicated, because HyperlinkPart was sending only the display text to my table. So I used some of what you taught me in answer to my initial question (I created two columns and hid the first one):

SELECT [Form reference], HyperlinkPart([Form reference],1) AS FormHyperlinks FROM Forms UNION ALL SELECT [OI number], HyperlinkPart([OI number],1) AS OIHyperlinks FROM [Operational Instructions] UNION ALL SELECT [Procedure code], HyperlinkPart([Procedure code],1) AS ProcedureHyperlinks FROM [Procedures];