subforms and relationship

[phineas629]

Hi all,



Everyone's been so helpful on here. I tried everything but, I am really stuck.I've read through plenty of manuals, done searches in the forum and read online articles to no avail.

Here's the relationship:

[person] 1 --- ∞ [employee] 1--- ∞ [invoice details] ∞ ---1 [invoice]


I have made a form "Invoice" /subform "Invoice_Details." The subform displays multiple line items employee id, bill, dates, etc. which makes one invoice.

I want to know the actual person's name on the invoice details, not the person's employee ID. How can I do this?

Thanks.

[boblarson]

You store the ID like it is doing but you use a COMBO box on the subform which has the ROW SOURCE of it based on a select statement which uses the person table as its source and it includes the ID first and the name second and then the column count is set to 2 and the column widths are set to 0";2" which would hide the ID column but show the name.

[phineas629]

Hi Bob,

What should I set the control source to? Also the names are split into first, middle, last. Should i try to make a query to put names together. I'm still very new to this.

Thanks

[boblarson]

Control Source is the ID field you are storing the ID in for the person.

For creating a field to display all parts you can use

PersonName:[LastNameFieldHere] & ", " & [FirstNameFieldHere] & " " & [MiddleNameFieldHere]

and this will show like

Larson, Robert X

so you could add a sort on Last, First, and Middle names in that order so they would be easy to find in that list.