Problem with Combo box cascading

[osimini via AccessMonster.com]

Headache

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

I have tables for Employees, 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

 

[Ken Snell [MVP]]

Show us the Row Source queries for the two combo boxes that are in the two
subforms.

In gneral, one can reference a control on a main form from a subform this
way:
=[Parent].[cboEmployeeID]

The above is likely what you'll need to use as a syntax for the WHERE clause
in the two combo boxes' Row Source queries.

 

[osimini via AccessMonster.com]

Show us the Row Source queries for the two combo boxes that are in the two
subforms.

In gneral, one can reference a control on a main form from a subform this
way:
=[Parent].[cboEmployeeID]

The above is likely what you'll need to use as a syntax for the WHERE clause
in the two combo boxes' Row Source queries.

Headache

[quoted text clipped - 41 lines]

work.
It is giving me headache. I have done everything possible

Here are the ROWSOURCES:

Row Source: SELECT tlkpEmployees.EmployeeID, tlkpEmployees.EmployeeName,
tlkpEmployees.EmployeeFirst, tlkpEmployees.EmployeeLast FROM tlkpEmployees;

fsubCasesEvents
Row Source: SELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.
EmployeeName FROM tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.
EmployeeID=tblCases.CaseOwnerID;

Ource: fsubReminder
Row SSELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.EmployeeName FROM
tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.EmployeeID=tblCases.
CaseOwnerID;

 

[Ken Snell [MVP]]

OK. Change the two subform comboboxes' RowSource queries to this:

fsubCasesEvents
Row Source: SELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.
EmployeeName FROM tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.
EmployeeID=tblCases.CaseOwnerID
WHERE tlkp.Employees.EmployeeID = [Parent].[cboEmployeeID];

Row Source: fsubReminder
SELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.EmployeeName FROM
tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.EmployeeID=tblCases.
CaseOwnerID
WHERE tlkp.Employees.EmployeeID = [Parent].[cboEmployeeID];


Note that you may get a parameter box from ACCESS (for
[Parent].[cboEmployeeID] ) when you first open the main form. Should that
occur, then we'll need to change the approach for doing this. Post back and
let us know.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Show us the Row Source queries for the two combo boxes that are in the two
subforms.

In gneral, one can reference a control on a main form from a subform this
way:
=[Parent].[cboEmployeeID]

The above is likely what you'll need to use as a syntax for the WHERE
clause
in the two combo boxes' Row Source queries.

Headache

[quoted text clipped - 41 lines]

work.
It is giving me headache. I have done everything possible

Here are the ROWSOURCES:

Row Source: SELECT tlkpEmployees.EmployeeID, tlkpEmployees.EmployeeName,
tlkpEmployees.EmployeeFirst, tlkpEmployees.EmployeeLast FROM
tlkpEmployees;

fsubCasesEvents
Row Source: SELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.
EmployeeName FROM tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.
EmployeeID=tblCases.CaseOwnerID;

Ource: fsubReminder
Row SSELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.EmployeeName FROM
tlkpEmployees INNER JOIN tblCases ON tlkpEmployees.EmployeeID=tblCases.
CaseOwnerID;