Having Problem withCascading Combo Box

[osimini1]

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

 

[TheAceMan1]

How are ya osimini1 . . .

Typically, the [blue]Row Source[/blue] of cascade comboboxes are based on a query with Criteria that looks at the parent or [blue]dependent combobox[/blue]. With this achieved, its a simple matter of requerying the combo's to show the proper data when a selection is made in the dependent combo. That is to say [blue]the parent combo requeries the combo's dependent on its value in criteria! [blue]The trick here is proper form referencing of the combo's[/blue] ... in criteria.

Despite the good info you've given, you havn't given the info required to complete this secnario. That is, the [blue]name & location of the dependent combo's[/blue], and wether [blue]frmCases[/blue] is independent or not a subform of [blue]frmCustomer[/blue].

So ... the following code assumes that [blue]frmCases[/blue] is independent and each subform combo has criteria for EmployeeID as follows:

[blue]WHERE [EmployeeID] = Forms!frmCases![[purple][B][I]ComboboxName[/I][/B][/purple]][/blue]

and the [blue]After Update[/blue] event of the combo on [blue]frmCases[/blue] would be:

[blue]   [fsubcaseEvent].Form![[purple][B][I]ComboboxName[/I][/B][/purple]].Requery
   [fsubReminder].Form![[purple][B][I]ComboboxName[/I][/B][/purple]].Requery[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[osimini1]

Thank you very much
OK Here is maore explanation.

Both fsubCases and fsubReminder are subforms to frmCases which is the main form.

1) All three forms are linked by as follows.

frmCases.CasesID = fsubCasesEvent.CaseID.
frmCasesCaseID = fsubReminder.CaseID

2. frmCases is an independent form with cboEmployeeID.
both fsubCaseEvents and fsubReminder forms with cboEmployeeID

3. frmCustomers not required and please disregard frmCustomer

4. Also, please can you explain more on the peace of code that you included "WHERE [EmployeeID] = Forms!frmCases![ComboboxName]". Where will this code go. please help

 

[TheAceMan1]

osimini1 . . .

Post the [blue]RowSource[/blue] of the three combo's.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[osimini1]

Here is the RowSources of the three combo boxes.

frmCases
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;

 

[TheAceMan1]

osimini1 . . .

The SQL's of the combo's in your subform sparked me to go back and take a deeper lookat your table structure. Its hard to say but it looks like you have two [blue]many to many[/blue] relationships here. My problem is I can't get any logic to stick. Some insight into how their linked in the [blue]relationships[/blue] window would help. You can also hop over to forum700 and see if your structure can be better done.

Moving on ... in the [blue]After Update[/blue] event of cboEmployeeID on frmCases, copy/paste the following:

[blue]   Dim rsEvent As Property, rsRemind As Property, SQL As String
   
   Set rsEvent = [fsubCasesEvents].Form!cboEmployeeID.Properties("RowSource")
   Set rsRemind = [fsubReminder].Form!cboEmployeeID.Properties("RowSource")
   
   
   SQL = "SELECT DISTINCT tblCases.CaseOwnerID, tlkpEmployees.EmployeeName " & _
         "FROM tlkpEmployees " & _
         "INNER JOIN tblCases ON tlkpEmployees.EmployeeID=tblCases.CaseOwnerID"
   
   If Not IsNull(Me!EmployeeID) Then
      SQL = SQL & " WHERE tlkpEmployees.EmployeeID=" & _
                          Forms!frmCases!cboEmployeeID & ";"
   Else
      SQL = SQL & ";"
   End If
   
   rsEvent = SQL
   rsRemind = SQL
   
   Set rsEvent = Nothing
   Set rsRemind = Nothing[/blue]

Perform your testing!

A good question at this point would be: What are you using the subforms combo's for?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]