Subform Main form relationship

[RayMilhon]

I have a main form for contract information with a subform for demographic information the 2 tables have a many to many relationship so I included a link table so it becomes a Many to 1 to Many relationship. The issue I'm having is on Data Entry the main form works perfectly but the demographic information is giving me a headache. Since a customer can be linked to multiple contracts and I don't want duplicates in the demographic Data any ideas on the best way to handle data entry. I tried including a find command button on the demographic table but just got an error. Also I realized that the filter may not work as expected anyway since there could be multiple entries that match the search parameters but not all may apply to that specific contract. Not exactly sure how to proceed. Any ideas would be helpful

[ssanfu]

If I understand correctly and not knowing your data:

I'm going to call the demographic information "tblCustomers".

So 1 Contract can have many Customers
and 1 Customer can have many Contracts?

Consider:

tblContracts
-------------
ContractID_PK - Autonumber
ContractDesc - Text
(other fields....)


tblCustomers
--------------
CustomerID_PK - Autonumber
FName - Text
LName - Text
(other fields....)

jnctContractsCustomers (jnct = junction table)
-------------------------
CustContractID_PK - Autonumber
ContractID_FK - Long Integer
CustomerID_FK - Long Integer
(other fields....)

To prevent duplicates, set a compound index on fields "ContractID_FK" & "CustomerID_FK" Set the Unique property to YES.


Forms:
Form "frmContractsAdd_Edit
Form "frmCustomersAdd_Edit
Main form "frmContracts" with sub form "sfCustomers"..... linked on ContractID_PK / ContractID_FK
Main form "frmCustomers" with sub form "sfContracts"......linked on CustomerID_PK / CustomerID_FK


I would require the contract and the customers to be added by the Add/Edit form, then use the main form/sub form to set the relationship between contracts and customers.

[RayMilhon]

Thanks Ssanfu that helps. I've made progress. Still have one issue that I'm not sure is possible If a new record is added I need to link the new record to the contract but the new customer ID is not created until the record is saved because it's an autonumber. Is it possible to force the new record to be created without moving to another record. I looked at the methods for the form and couldn't find one that looked usable? Or should I ask this in a new thread?

[RayMilhon]

Figured it out. using the form.refresh event does what I want. Thanks for your help. Forgot about the refresh Event was looking in the wrong areas.