Cascading Combo Box Issue

[RyanB]

Hi All,

Having a little problem with my combo box setup.

I have 3 combo boxes, 1st is division, 2nd is branch, 3rd is department. They all refer to a table which has 4 fields... they are the 3 I just mentioned and a 4th which is the department code.

The 3rd combo box contains the last to fields in its query with the dept code being bound to a field in another table (the field also appears on the form)

The problem is that from the 2nd combo box onwards the records that it shows arn't using the 'group by' feature that I selected when I created the query and when you select the department in the final combo box it does not enter the code into the field its supposed to.

But say you goto the last combo box first its obvious that the group by is working then and it puts the code into the field correctly, when you use the 2nd combo box first also the group by feature is working but if you then goto the 3rd one it has the same problem that I mentioned before.

Below is my code:
Option Compare Database

Private Sub cbobranch_Click()
If IsNull(Me![cbobranch]) Then
Me![cbodept].Visible = False
Me![lbldept].Visible = False
Exit Sub
Else
Me![cbodept].RowSource = "SELECT [tblDepartment].[Department] " _
& "FROM tblDepartment " _
& IIf(IsNull(Me![cbobranch]), "", "WHERE branch = '" & Me![cbobranch] & "'") _
& "ORDER BY [tblDepartment].[Department];"
Me![cbodept].Requery
Me![cbodept].Visible = True
Me![lbldept].Visible = True
End If
End Sub

Private Sub cbodiv_Click()
If IsNull(Me![cbodiv]) Then
Me![cbobranch].Visible = False
Me![lblbranch].Visible = False
Exit Sub
Else
Me![cbobranch].RowSource = "SELECT [tblDepartment].[Branch] " _
& "FROM tblDepartment " _
& IIf(IsNull(Me![cbodiv]), "", "WHERE Division = '" & Me![cbodiv] & "'") _
& "ORDER BY [tblDepartment].[Branch]"
Me![cbobranch].Requery
Me![cbobranch].Visible = True
Me![lblbranch].Visible = True
End If
End Sub

Private Sub Form_Load()
Me![cbodiv] = Null
Me![cbobranch] = Null
Me![cbodept] = Null
End Sub

---------------------------------------

Can anyone see what i'm doing wrong cause i've been messing with this for hours and can't get it to work.

Cheers,

Ryan

 

[WayneRyan]

Ryan,

You don't want to use the IIf statements here.

Limit the combo selections to list.

Have each combo's RowSource:

sql = "Select Distinct(Division) " & _
      "From   YourTable"

sql = "Select Distinct(Branch) " & _
      "From   YourTable " & _
      "Where  Division = '" & Me.cboDivision & "'"
and:
sql = "Select Department " & _
      "From   YourTable " & _
      "Where  Division = '" & Me.cboDivision & "' And " & _
      "       Branch = '" & Me.Branch & "'"

Then use the AfterEvent of each combo to .Requery the Next combo:

Me.NextCombo.Requery

Wayne

 

[john471]

Fun !

Ryan,

That is a nice little challenge !

I had a play with it, as best as I can make out from your description, and made a mock-up.

I found it best to use the form's onCurrent event, and beforeUpdate events for the Division and Branch drop down boxes, rather than on-click events. Made more sense to me to do it this way.

I extrapolated your description to associate employees with department codes, but you could adjust the example to work for your desired application.

Took the following assumptions:
a) Department codes are unique to a department
b) A Department belongs to one (and only one) Branch
c) A Branch belongs to one (and only one) Division
d) A Division has one or more Branches
e) A Branch has one or more Departments
f) If you are changing the Division, the Branch and Department (and code) must also change
g) If you are chainging the Branch, the Department (and code) must also change
h) As you move between records, you want the corresponding Division, Branch and Department to display (but not necessarily the Department Code)

I've just noticed Wayne Ryan beat me to the post (no punn intended) with a reply, but I will continue with this reply anyway as I've spent some time on it, and it may help U No offence intended to Wayne.

My mock-up, attched is AC97.

Here is the code

Option Explicit

Private mVarCurDiv As Variant
Private mVarCurBranch As Variant

Private Sub Form_Current()
    setDivValue
    setBranchSource
    setBranchValue
    setDeptSource
    setDeptValue
End Sub

Private Sub cboDiv_BeforeUpdate(Cancel As Integer)
    
    If Nz(Me.cboDiv.Value) <> mVarCurDiv Then 'old and new values differ
        'Set the branch RowSource appropriate to the division chosen
        setBranchSource
        'Any previous Branch And Dept values pertained to a different division, so clear them
        Me.cboBranch.Value = Null
        Me.cboDept.Value = Null
    End If
End Sub

Private Sub cboBranch_BeforeUpdate(Cancel As Integer)
    If Nz(Me.cboBranch.Value) <> mVarCurBranch Then 'old and new values differ
        'Set the Department RowSource appropriate to the Branch chosen
        setDeptSource
        'Any previous Dept value pertained to a different Branch, so clear it
        Me.cboDept.Value = Null
    End If
End Sub

Private Sub setDivValue()
    'Division Source is static - all divisions
    If Len(Nz(Me.cboDept.Value)) > 0 Then
        'if we have a department code, set the corresponding division
        Me.cboDiv.Value = DLookup("Division", "tblDepartment", "DepartmentCode='" & Me.cboDept.Value & "'")
    Else
        Me.cboDiv.Value = Null
    End If
    mVarCurDiv = Nz(Me.cboDiv.Value)
End Sub

Private Sub setBranchSource()
    'If we have a division, set the Branch dropdown box's list, to the branches belonging to that division
    If Len(Nz(Me.cboDiv.Value)) > 0 Then
        Me.cboBranch.RowSource = "SELECT DISTINCT tblDepartment.Branch FROM tblDepartment WHERE (((tblDepartment.Division)='" & Me.cboDiv.Value & "'));"
    Else
        Me.cboBranch.RowSource = vbNullString
    End If
    Me.cboBranch.Requery
End Sub

Private Sub setBranchValue()
    'If we have a department code, set the corresponding branch
    If Len(Nz(Me.cboDept.Value)) > 0 Then
        Me.cboBranch.Value = DLookup("Branch", "tblDepartment", "DepartmentCode='" & Me.cboDept.Value & "'")
    Else
        Me.cboBranch.Value = Null
    End If
    mVarCurBranch = Nz(Me.cboBranch.Value)
End Sub

Private Sub setDeptSource()
    'If we have a branch, set the Department dropdown box's list, to the Departments belonging to that Branch
    If Len(Nz(Me.cboBranch.Value)) > 0 Then
        Me.cboDept.RowSource = "SELECT DISTINCT tblDepartment.Department, tblDepartment.DepartmentCode FROM tblDepartment WHERE (((tblDepartment.branch)='" & Me.cboBranch.Value & "'));"
    Else
        Me.cboDept.RowSource = vbNullString
    End If
    Me.cboDept.Requery
End Sub

Private Sub setDeptValue()
    'This is necessary for when moving to a record which has a different dept to the prior record
    'as the Dept RowSource would have had the previous Department's Branch's Department list
    'when the form became current, so it cant show the value until after we modify the Dept RowSource accordingly
    If Len(Nz(Me.cboDept.Value)) > 0 Then
        Me.cboDept.Value = Me.cboDept.Value
    Else
        Me.cboDept.Value = Null
    End If

End Sub

Regards, and HTH

John

 

[john471]

Of course I just failed the next round of "testing"...

you would need to add
mVarCurDiv = Me.cboDiv.Value
and
mVarCurBranch = Me.cboBranch.Value

as below...

Private Sub cboDiv_BeforeUpdate(Cancel As Integer)
    
    If Nz(Me.cboDiv.Value) <> mVarCurDiv Then 'old and new values differ
        mVarCurDiv = Me.cboDiv.Value
        'Set the branch RowSource appropriate to the division chosen
        setBranchSource
        'Any previous Branch And Dept values pertained to a different division, so clear them
        Me.cboBranch.Value = Null
        Me.cboDept.Value = Null
    End If
End Sub

Private Sub cboBranch_BeforeUpdate(Cancel As Integer)
    If Nz(Me.cboBranch.Value) <> mVarCurBranch Then 'old and new values differ
        mVarCurBranch = Me.cboBranch.Value
        'Set the Department RowSource appropriate to the Branch chosen
        setDeptSource
        'Any previous Dept value pertained to a different Branch, so clear it
        Me.cboDept.Value = Null
    End If
End Sub

and you may also want to set the Department rowsource to vbnullstring after changing the Division, becuase the possible Departments are unknown until a new Branch is selected.


Good Luck.

 

[RyanB]

Wow

Wow that works great.

Your assumptions were pretty much correct except for:

A Branch can belong to more than one division
& I only wish for the department code to be saved to the table.

At the moment I have it all working apart from after the department is selected the department code does not show up in the Department Code field and I can't find any reference in the code to how it would be presented...

Thanks for your effort so far it is much appreciated

Ryan

 

[Mile-O]

My solution would be to design your table structure properly and not have Division, Branch, and Department in one table as they are all separate entities.