Cascading combo boxes in subform

[JaineyBlue]

Still working on this damn vehicle database and need help with combo boxes please!
Have three forms - Vehicles, Trailers, Equipment. Each of the forms has embedded subforms, one being Location [subAutomLoc], which has the user group info for each vehicle. The user group info is 4 cascading combo boxes (branch, department, section1, section2). I've managed to get the combo boxes working in the Vehicles form by using the following in rowsource in the child comboboxes:
SELECT DISTINCT CostCentresTbl.Department FROM CostCentresTbl WHERE (((CostCentresTbl.Branch)=[Forms]![Form-Vehicles].[Form]![fsubAutomLoc]![Branch])) ORDER BY CostCentreTbl.Department;
And so forth with Sections 1 and 2.

My problem is, this works great in the Vehicles form but won't work in the Equipment or Trailers forms because it references the choice of Branch you make in the Vehicles form. Is there any way I can have the subform work in all 3 forms?

 

[Andrzejek]

If I had 3 forms with 4 combo boxes on each Form, I would move all logic to populate these combos into a Module and pass a reference to whatever combo I want to populate:

Public Sub FillBranchCombo(ByRef cbo As ComboBox)

rst.Open "Select Branch From tblBranch Order By SomeField"

With cbo
    .Clear
    While Not rst.Eof
        cbo.AddItem rst!Branch.Value
    Loop
    If .ItemCount < 0 Then
        .ListIndex = 0
    End If
End With

End Sub

And then, if I need Department combo filled, call this logic from cboBranch_Click event and pass some arguments:

Public Sub FillDeptCombo(ByRef cbo As ComboBox, ByRef BranchID As Integer)

rst.Open "Select Departemt From tblDept Where BranchID = " & BranchID & " Order By SomeDeptField"

With cbo
    .Clear
    While Not rst.Eof
        cbo.AddItem rst!Departemt.Value
    Loop
    If .ItemCount < 0 Then
        .ListIndex = 0
    End If
End With

End Sub

and so on...

Call these pieces of logic from your 3 different forms.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JaineyBlue]

If the info in the combo boxes is from the same table (costcentrestbl has fields named Branch, Department, Section1, Section2) do I need to identify Table.Field in the module?
Getting a compile error "Argument not optional" in the subform code where calling the subroutine.

 

[Andrzejek]

>Branch, Department, Section1, Section2 [come] from the same table
Looks like you do not have normalized data base :-(
But, yes - you can just access the same table in your Module's code to populate your different combos.
>do I need to identify Table.Field in the module?
Well, your code needs to know which field to show in a combo box, right?
>compile error
It is very hard to guess without looking at your code :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

If your data looks something like this:

[pre]
CostCentresTbl
Branch Department Section1 Section2
West Dept1 Sect1 1 Sect2 1
West Dept1 Sect1 1 Sect2 2
West Dept1 Sect1 2 Sect2 1
West Dept2 Sect1 1 Sect2 2
East Dept1 Sect1 1 Sect2 1
East Dept2 Sect1 1 Sect2 1
Sounth Dept1 Sect1 1 Sect2 1
[/pre]
then, to populate your 4 combo boxes:

[pre]
cboBranch:
Select Distinct Branch
From CostCentresTbl
Order By 1

cboDepartment:
Select Distinct Department
From CostCentresTbl
Where Branch = whatever is selected in cboBranch
Order By 1

cboSection1:
Select Distinct Section1
From CostCentresTbl
Where Branch = whatever is selected in cboBranch
And Department = whatever is selected in cboDepartment
Order By 1

cboSection2:
Select Distinct Section2
From CostCentresTbl
Where Branch = whatever is selected in cboBranch
And Department = whatever is selected in cboDepartment
And Section1 = whatever is selected in cboSection1
Order By 1[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JaineyBlue]

The table is similar to what you have, except in section 2 where each section is unique. But the code above is essentially what I had to begin with. My problem is this is a subform that is embedded into 3 different main forms, so if I choose the Branch in one main form it doesn't work in the other two.
So please forgive my ignorance (I am actually just a vehicle mechanic who is managing a large fleet with this) as I am completely self-taught with this stuff. Do I put this code into the new module and then call the subroutine into the subform?

 

[Andrzejek]

Let's start simple.
Start a new Access with just one Form, place a combo box on the Form, name it [blue]cboMyCombo[/blue]
In the Form_Load event place a code:
(or, if you also place a command button on the form, place this code in its _click event)

[green]'call a Sub in the Module and pass a combo box [/green]
Call [red]FillCombo[/red]([blue]cboMyCombo[/blue])

and in the Module, place this code:

Option Explicit

Public Sub [red]FillCombo[/red](ByRef [blue]cbo[/blue] As ComboBox)
[green]'Sub accepts a reference to a combo box and fills it[/green]
With cbo
    .Clear
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Ten"
    .AddItem "Last"
    .ListIndex = 0 [green]'Show first item[/green]
End With

End Sub

That should allow you to populate your combobox in the module no matter which Form you call [tt]FillCombo[/tt] from, as long as you pass a reference to a combo box on your form.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JaineyBlue]

On the .Clear I am getting compile error Method or data member not found

 

[Andrzejek]

What about if you put this code in the Form where you have your cboMyCombo combo box:

With cboMyCombo
    .Clear
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Ten"
    .AddItem "Last"
    .ListIndex = 0 
End With

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JaineyBlue]

I attached a link to a somewhat stripped down version of the database. I have tried a bunch of different things, including what you have suggested to get these combo boxes working. The subform is fsubAutomLoc and i want it to work in three main forms that it is embedded in (the buttons Maintain Vehicles, Maintain Equipment, Maintain Trailers on the switchboard).
Apologies for the massive amount of useless garbage in this database. I left this department for 5 years and the people who replaced me have no idea what they are doing and have saved lots of useless and redundant queries, etc. I am slowly cleaning it up but trying not to break linked forms and reports.

 

[Andrzejek]

Believe it or not, I am not an Access person. I do mostly VB6 and (straight) VBA
So, hopefully someone more knowledgeable about Access will jump in and help.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JaineyBlue]

Thank you for the help so far!