How to display data automatically in a datasheet ? 1

[akinia]

Hi everyone

I've a form (Company) which retrieves company information.
In this form I also have a tab control with some tab pages;
in one of them I've a form (Contact:company) with 3 subforms. In one of these subforms
I display the information in a datasheet form about contacts made with
the company by our sellers.
The link between Master and child is companyID.
The Subform record source is:
SELECT ContactPersons.CompanyID, ContactPersons.FirstName,
ContactPersons.LastName,Contact.Seller,...
FROM ContactPersons INNER JOIN Contacts ON
ContactPersons.ContactPersonID = Contacts.ContactPersonID;
Everything works fine.
But, I just want when there is a new contact that the seller only clicks
on the Combo Box which displays the ContactPersonID to fill automatically the
Last and FirstName. Here is the SQL for the Combo:
SELECT ContactPersons.ContactPersonID,[ContactPersons]![FirstName] & " " &
[ContactPersons]![LastName] AS Name FROM ContactPersons WHERE
ContactPersons.CompanyID = [forms]![company]![companyID]
ORDER BY ContactPersons.LastName;
Outside the form ths subforms works fine.
When I run it in the form (Contact:company) I recieve the following message:
"The current field must match the join key '?' in the table that serves
as the 'one' side of the one-to-many relationship.Enter a record in the 'one'
side table with the desired key value, and then make the entry with the
desired join key in the 'many-only' table."

I think there is something to do with the forms but I don't figure it out.
any suggestion and of course solution will be grantly appreciated.

Thanks for your time and answers.

 

[Anvil19]

Hi akinia

When you do an autolookup, you must create a join with a one to many join. For your combo lookup, I would create a query with this join, and use the query as your rowsource.

The many side of the join must be on the right of the join, and the join must have a common field with exact data.

Then save your query, and then "Saveas" your query as a table and add it to your form as a subform. That should do the trick.

Try that

Anvil19