Dependent combo boxes issue

[Bellyjeans]

Hi everybody,

I have a complex problem and I was wondering if somebody would be able
to help me.

I have three combo boxes on a form: the first is called cboFaculty,
the second is called cboDepartment, and the third is called
cboDivision. The information for these combo boxes are pulled from
three tables: tblFaculty, tblDepartment, and tblDivision. These three
tables have a relationship with a "master" table, if you will, called
"tblStaffMain".

The three combo boxes in question are dependent on eachother, i.e. if
you make a selection in cboFaculty, cboDepartment is populated based
on the selection made; cboDivision is populated based on the selection
made in cboDepartment. In a nutshell, it goes cboFaculty-

cboDepartment->cboDivision. The results of these combo boxes are

saved into the tblStaffMain table. (cboFaculty is saved in a field
called "Faculty", cboDepartment is saved in a field called
"Department", and cboDivision is saved in a field called "Division").

I have the following coding on the combo boxes' After Update event to
make them dependent on each other:

cboFaculty:

Dim DeptSource As String

DeptSource = "SELECT [tblDepartment].[DeptID]," & _
" [tblDepartment].[DeptID]," & _
" [tblDepartment].[Department] " & _
"FROM tblDepartment" & _
"WHERE [FacultyID] = " & Me.cboFaculty.Value
Me.cboDepartment.RowSource = DeptSource
Me.cboDepartment.Requery
Me.cboDivision.Requery


cboDepartment:

Dim DivisionSource As String

DivisionSource = "SELECT [tblDivision].[DivisionID]," & _
" [tblDivision].[DivisionID]," & _
" [tblDivision].[Division] " & _
"FROM tblDivision" & _
"WHERE [DeptID] = " & Me.cboDepartment.Value
Me.cboDivision.RowSource = DivisionSource
Me.cboDepartment.Requery
Me.cboDivision.Requery


The combo boxes seem to be working fine upon first glance, but my
problem lies when I close out of the form and open it back up. When I
open it back up, two strange things occur:

a) The selection that I had made in cboFaculty remains, but the
selections that I had made in cboDepartment and cboDivision are gone.
Their values are, however, still stored in tblStaffMain.

b) When click on the second combo box, cboDepartment, the list is
gone. The list reappears if I click on another selection in
cboFaculty and click back into the selection that I had made before.

From there, the combo boxes are filtering the selections properly
until I close out again and reopen - then I have the same problem. My
question is how do I make the values in cboDepartment and cboDivision
remain displayed when I close out of the form and reopen it?

 

[Jeff Boyce]

If the same value remains in the control from record to record, chances are
good that the control is unbound.

If the values listed in each combobox depends on previous choices, and if
you leave the form (and therefore empty the comboboxes), why would you
expect to have the form reopen with the same values? Same values as what?!
Which record do you want the form to be pointed at?

Are you using the form exclusively for data entry, or are you allowing
editing of existing records? If the latter, how is the user selecting which
record to edit?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi everybody,

I have a complex problem and I was wondering if somebody would be able
to help me.

I have three combo boxes on a form: the first is called cboFaculty,
the second is called cboDepartment, and the third is called
cboDivision. The information for these combo boxes are pulled from
three tables: tblFaculty, tblDepartment, and tblDivision. These three
tables have a relationship with a "master" table, if you will, called
"tblStaffMain".

The three combo boxes in question are dependent on eachother, i.e. if
you make a selection in cboFaculty, cboDepartment is populated based
on the selection made; cboDivision is populated based on the selection
made in cboDepartment. In a nutshell, it goes cboFaculty-

cboDepartment->cboDivision. The results of these combo boxes are

saved into the tblStaffMain table. (cboFaculty is saved in a field
called "Faculty", cboDepartment is saved in a field called
"Department", and cboDivision is saved in a field called "Division").

I have the following coding on the combo boxes' After Update event to
make them dependent on each other:

cboFaculty:

Dim DeptSource As String

DeptSource = "SELECT [tblDepartment].[DeptID]," & _
" [tblDepartment].[DeptID]," & _
" [tblDepartment].[Department] " & _
"FROM tblDepartment" & _
"WHERE [FacultyID] = " & Me.cboFaculty.Value
Me.cboDepartment.RowSource = DeptSource
Me.cboDepartment.Requery
Me.cboDivision.Requery


cboDepartment:

Dim DivisionSource As String

DivisionSource = "SELECT [tblDivision].[DivisionID]," & _
" [tblDivision].[DivisionID]," & _
" [tblDivision].[Division] " & _
"FROM tblDivision" & _
"WHERE [DeptID] = " & Me.cboDepartment.Value
Me.cboDivision.RowSource = DivisionSource
Me.cboDepartment.Requery
Me.cboDivision.Requery


The combo boxes seem to be working fine upon first glance, but my
problem lies when I close out of the form and open it back up. When I
open it back up, two strange things occur:

a) The selection that I had made in cboFaculty remains, but the
selections that I had made in cboDepartment and cboDivision are gone.
Their values are, however, still stored in tblStaffMain.

b) When click on the second combo box, cboDepartment, the list is
gone. The list reappears if I click on another selection in
cboFaculty and click back into the selection that I had made before.

From there, the combo boxes are filtering the selections properly
until I close out again and reopen - then I have the same problem. My
question is how do I make the values in cboDepartment and cboDivision
remain displayed when I close out of the form and reopen it?