Problem with combo box

[osimini]

Headache

I have been trying for 2 weeks now to to get this to work no success.

I have tables for E, Cases, CaseEvents, Customers and Reminder.
I have created a form frmCustomer as mainform.Then another form frmCases. Also created two sub forms called: sfrmCasesEvents, fsubReminder.



fsubcaseEvent and fsubReminder are to subforms to frmCases


I am trying to allow the employee to select any employeeID in frmCases cmoEmployeeID field. Once the EmployeeID is selected in frmCases both subforms – fsubCaseEvents and fsubReminder cascade combo boxes are defaulted updated with the EmployeeID selected in the main form frmCases.

I have built cascading combo boxes before but all have been based upon a one to many relationship.

The following 4 tables show details of table

Employee
EmpD (PK) - autonumber
Empname - text

Case
CaseID (PK) - autonumber
EmpID - Number

CaseEvents
EventID – autonumber
CaseID - numver
EmpID - number

Remember
RemID - autonumber
CaseID – number
EmpID - number


I am using Access 2003

Can someone please help before I am having difficulty getting this to work. It is giving me headache. I have done everything possible

[RuralGuy]

Your description switches back and forth between SubForms and ComboBoxes. It got me a little dizzy. Let's pick one and get it working first. Describe just one form and one SubForm and tell us what you have and what you want to happen.

[osimini]

Please see attached for more explanation to helpm in answering my questions

Thanks

[RuralGuy]

That explains the Combox switching. The EmployeeID fields should be LongInteger fields in the other tables as ForeignKeys. They are not LookUp Fields are they? If you have the LinkChild/MasterFields properties of the SubForm control set to the EmployeeID fields in the respective forms then what you want to happen will happen automatically.

[osimini]

Yes, EmployeeID are both lookup fields and foreign keys to the main table. Also, currently I have the LinkChild/Master Fields properties of the subform controls set to CaseID fields in the respective form as link between main and subform.SO are you suggesting that I should change the following:

1. EmployeeID should not be lookup field in the tables for the subforms where they are foreign key.

2. To use both CaseID and EmployeeID or EmployeeID alone as the LinkChild/Master for the subform control.

Please help

[RuralGuy]

The answer to #1 is an unqualified YES. You can have more than one Link Field by separating them with a ";" in the two properties.