Drop Down List

[DMGoldie]

Hi,

I have a Job DB. I also have a Client DB. When entering a new Job the first field the user needs to enter is Client ID. Client ID has a Drop Down List associated with it. Once the correct Client ID is selected the following fields are filled:

- Client Name

- Contact Name

- Contact Phone Number

The problem is this - I would like the Drop Down list to display the Client Name AND the Contact Name. Currently this is not possible because the first field in the Drop Down List needs to be the Client ID in order to correctly propagate the 3 files listed above.

Any ideas?

Thanks,

dmg

[Fitch]

Make a calculated field in the Client table, Client Name & " - " & Contact Name. Use that field as field 2 in the value list.

[DMGoldie]

I tried that. The table structure is such:

- Job

- Client

- Contacts

I am pulling the data from the Contacts table. Contacts does not have "Client Name" in its table. If I use a calculation to create a Client Name field the Drop Down List doesn't work because the field "Client Name" cannot be indexed

[comment]

A client has (or can have) many contacts, right? If so, selecting the client cannot select a specific contact, unless one is picked at random. You could, if you wanted, select a specific contact directly - and the client would be picked "along the way". Most people, I think, would prefer to pick the client first, then pick the contact from a reduced list of the selected client's contacts only.

[Fitch]

1. The first field for the value list has to be indexed, the second one doesn't.

2. You're picking the client ID, so make the calc in the client table.

3. I assume that you're wanting to show the first related contact for that client. That does seem kind of random, unless your relationship is sorted by priority or title or something.