Synchronized Combo - Boxes Issues

  • Thread starter Thread starter Nina
  • Start date Start date
N

Nina

I have followed the sample provided by Joe Leon on his post of 7/8/06 (just
realized the question was posted in 2006 - I guess I've been putting too many
hours on this computer!! )

I have followed his steps and still am getting an issue when the combo boxes
display the data -


3 Tables ....

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

3 combo boxes, cboStore, cboManager, and cboEmployee

I've used After Update event to synchronize all the 3 comboboxes ... see
below the code ...

Private Sub cboStore_AfterUpdate()

Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery

End Sub


Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT [tblEmployee].[lngEmployeeID] ,
[tblEmployee].[lngManagerID], [tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.cboManager) Then
Cancel = True
MsgBox "Make a selection first"
End If

End Sub

When I open the form all the 3 cbos are blank - when I make my selection on
the first cboStore - it gives me options to choose from on the second
cboManager and after the selection is made it also gives an option on the
third cboEmployee . So pretty much it is working fine but when I decide to go
back to the first cboStore and make a new selection, the third cboEmployee
still display the original option I 've selected. This can cause
issues/confusion for users. I tried to add Me.cboEmployee.Requery to the
first cboStore After Update event but it did not work. I also added the Event
on the Sub Form On Current as below but it did not work either.

When I make a selection on the second cboManager only then the cboEmployee
clears.

I appreciate your suggestions

Another help I would request is how could I save the selections in a table
so that I can run a query to show the data related to this selection in a
subform?

Thanks a lot for your help.


Private Sub Form_Current()
Dim lngLoop As Long
Call cboStore_AfterUpdate
For lngLoop = 1 To 20
DoEvents
Next lngLoop
Call cboManager_AfterUpdate
End Sub

I appreciate any help on this.

Thanks
 
The third combo does not change when you make a seledtion in the first
because there is nothing there to cause the third combo to update. You can
add code to the first combo to update both and to the second to update the
third. That is as simple as calling the Other After Update events.

After this line:
Me.cboManager.Requery

Add:

Call cboManager_AfterUpdate

As to saving to a table, is that necessary? If the query will be run while
the form is open, this is the time to filter using references to the combo
boxes.

--
Dave Hargis, Microsoft Access MVP


Nina said:
I have followed the sample provided by Joe Leon on his post of 7/8/06 (just
realized the question was posted in 2006 - I guess I've been putting too many
hours on this computer!! )

I have followed his steps and still am getting an issue when the combo boxes
display the data -


3 Tables ....

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

3 combo boxes, cboStore, cboManager, and cboEmployee

I've used After Update event to synchronize all the 3 comboboxes ... see
below the code ...

Private Sub cboStore_AfterUpdate()

Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery

End Sub


Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT [tblEmployee].[lngEmployeeID] ,
[tblEmployee].[lngManagerID], [tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.cboManager) Then
Cancel = True
MsgBox "Make a selection first"
End If

End Sub

When I open the form all the 3 cbos are blank - when I make my selection on
the first cboStore - it gives me options to choose from on the second
cboManager and after the selection is made it also gives an option on the
third cboEmployee . So pretty much it is working fine but when I decide to go
back to the first cboStore and make a new selection, the third cboEmployee
still display the original option I 've selected. This can cause
issues/confusion for users. I tried to add Me.cboEmployee.Requery to the
first cboStore After Update event but it did not work. I also added the Event
on the Sub Form On Current as below but it did not work either.

When I make a selection on the second cboManager only then the cboEmployee
clears.

I appreciate your suggestions

Another help I would request is how could I save the selections in a table
so that I can run a query to show the data related to this selection in a
subform?

Thanks a lot for your help.


Private Sub Form_Current()
Dim lngLoop As Long
Call cboStore_AfterUpdate
For lngLoop = 1 To 20
DoEvents
Next lngLoop
Call cboManager_AfterUpdate
End Sub

I appreciate any help on this.

Thanks
Click to expand...
 
Hi Dave,

Thanks for your prompt reply ! I really appreciate !!

I did add the Call cboManager_AfterUpdate but it did not work. under the
first combo box but it did not clear the third one ... :-( .. .Not sure
what to do

Klatuu said:
The third combo does not change when you make a seledtion in the first
because there is nothing there to cause the third combo to update. You can
add code to the first combo to update both and to the second to update the
third. That is as simple as calling the Other After Update events.

After this line:
Me.cboManager.Requery

Add:

Call cboManager_AfterUpdate

As to saving to a table, is that necessary? If the query will be run while
the form is open, this is the time to filter using references to the combo
boxes.

--
Dave Hargis, Microsoft Access MVP


Nina said:
I have followed the sample provided by Joe Leon on his post of 7/8/06 (just
realized the question was posted in 2006 - I guess I've been putting too many
hours on this computer!! )

I have followed his steps and still am getting an issue when the combo boxes
display the data -


3 Tables ....

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

3 combo boxes, cboStore, cboManager, and cboEmployee

I've used After Update event to synchronize all the 3 comboboxes ... see
below the code ...

Private Sub cboStore_AfterUpdate()

Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery

End Sub


Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT [tblEmployee].[lngEmployeeID] ,
[tblEmployee].[lngManagerID], [tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.cboManager) Then
Cancel = True
MsgBox "Make a selection first"
End If

End Sub

When I open the form all the 3 cbos are blank - when I make my selection on
the first cboStore - it gives me options to choose from on the second
cboManager and after the selection is made it also gives an option on the
third cboEmployee . So pretty much it is working fine but when I decide to go
back to the first cboStore and make a new selection, the third cboEmployee
still display the original option I 've selected. This can cause
issues/confusion for users. I tried to add Me.cboEmployee.Requery to the
first cboStore After Update event but it did not work. I also added the Event
on the Sub Form On Current as below but it did not work either.

When I make a selection on the second cboManager only then the cboEmployee
clears.

I appreciate your suggestions

Another help I would request is how could I save the selections in a table
so that I can run a query to show the data related to this selection in a
subform?

Thanks a lot for your help.


Private Sub Form_Current()
Dim lngLoop As Long
Call cboStore_AfterUpdate
For lngLoop = 1 To 20
DoEvents
Next lngLoop
Call cboManager_AfterUpdate
End Sub

I appreciate any help on this.

Thanks
Click to expand...
Click to expand...
 
Change of direction

I have had success with similar combo box problems by setting the rowsource
of a combo box using the On-Enter event of the filtered combo box instead
of the After_update event of the previous box

Whenever you enter the employee , or manager combo box, it will look at the
present values in the two other boxes and set its row source

Using the after-update events usually means that you have to change them in
the order you expected.

Hope this helps



Nina said:
Hi Dave,

Thanks for your prompt reply ! I really appreciate !!

I did add the Call cboManager_AfterUpdate but it did not work. under the
first combo box but it did not clear the third one ... :-( .. .Not sure
what to do

Klatuu said:
The third combo does not change when you make a seledtion in the first
because there is nothing there to cause the third combo to update. You
can
add code to the first combo to update both and to the second to update
the
third. That is as simple as calling the Other After Update events.

After this line:
Me.cboManager.Requery

Add:

Call cboManager_AfterUpdate

As to saving to a table, is that necessary? If the query will be run
while
the form is open, this is the time to filter using references to the
combo
boxes.

--
Dave Hargis, Microsoft Access MVP


Nina said:
I have followed the sample provided by Joe Leon on his post of 7/8/06
(just
realized the question was posted in 2006 - I guess I've been putting
too many
hours on this computer!! )

I have followed his steps and still am getting an issue when the combo
boxes
display the data -


3 Tables ....

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

3 combo boxes, cboStore, cboManager, and cboEmployee

I've used After Update event to synchronize all the 3 comboboxes ...
see
below the code ...

Private Sub cboStore_AfterUpdate()

Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery

End Sub


Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT [tblEmployee].[lngEmployeeID] ,
[tblEmployee].[lngManagerID], [tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.cboManager) Then
Cancel = True
MsgBox "Make a selection first"
End If

End Sub

When I open the form all the 3 cbos are blank - when I make my
selection on
the first cboStore - it gives me options to choose from on the second
cboManager and after the selection is made it also gives an option on
the
third cboEmployee . So pretty much it is working fine but when I decide
to go
back to the first cboStore and make a new selection, the third
cboEmployee
still display the original option I 've selected. This can cause
issues/confusion for users. I tried to add Me.cboEmployee.Requery to
the
first cboStore After Update event but it did not work. I also added the
Event
on the Sub Form On Current as below but it did not work either.

When I make a selection on the second cboManager only then the
cboEmployee
clears.

I appreciate your suggestions

Another help I would request is how could I save the selections in a
table
so that I can run a query to show the data related to this selection in
a
subform?

Thanks a lot for your help.


Private Sub Form_Current()
Dim lngLoop As Long
Call cboStore_AfterUpdate
For lngLoop = 1 To 20
DoEvents
Next lngLoop
Call cboManager_AfterUpdate
End Sub

I appreciate any help on this.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
That works in theory, but it can be confusing to a user because the value
displayed in the box is still incorrect until the third combo gets the focus.

I don't know what the problem is with the OP's code, but the method I
propose works well.
--
Dave Hargis, Microsoft Access MVP


Chegu Tom said:
Change of direction

I have had success with similar combo box problems by setting the rowsource
of a combo box using the On-Enter event of the filtered combo box instead
of the After_update event of the previous box

Whenever you enter the employee , or manager combo box, it will look at the
present values in the two other boxes and set its row source

Using the after-update events usually means that you have to change them in
the order you expected.

Hope this helps



Nina said:
Hi Dave,

Thanks for your prompt reply ! I really appreciate !!

I did add the Call cboManager_AfterUpdate but it did not work. under the
first combo box but it did not clear the third one ... :-( .. .Not sure
what to do

Klatuu said:
The third combo does not change when you make a seledtion in the first
because there is nothing there to cause the third combo to update. You
can
add code to the first combo to update both and to the second to update
the
third. That is as simple as calling the Other After Update events.

After this line:
Me.cboManager.Requery

Add:

Call cboManager_AfterUpdate

As to saving to a table, is that necessary? If the query will be run
while
the form is open, this is the time to filter using references to the
combo
boxes.

--
Dave Hargis, Microsoft Access MVP


:

I have followed the sample provided by Joe Leon on his post of 7/8/06
(just
realized the question was posted in 2006 - I guess I've been putting
too many
hours on this computer!! )

I have followed his steps and still am getting an issue when the combo
boxes
display the data -


3 Tables ....

tblStore:
Primary Key: lngStoreID AutoNumber
strStoreName Text

tblManager:
Primary Key: lngManagerID AutoNumber
lngStoreID Number
strManagerName Text

tblEmployee:
Primary Key: lngEmployeeID AutoNumber
lngManagerID Number
strEmployeeName Text

3 combo boxes, cboStore, cboManager, and cboEmployee

I've used After Update event to synchronize all the 3 comboboxes ...
see
below the code ...

Private Sub cboStore_AfterUpdate()

Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[lngManagerID],
[tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery

End Sub


Private Sub cboManager_AfterUpdate()
Dim sEmployeeSource As String

sEmployeeSource = "SELECT [tblEmployee].[lngEmployeeID] ,
[tblEmployee].[lngManagerID], [tblEmployee].[strEmployeeName] " & _
"FROM tblEmployee " & _
"WHERE [lngManagerID] = " & Me.cboManager.Value
Me.cboEmployee.RowSource = sEmployeeSource
Me.cboEmployee.Requery

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.cboManager) Then
Cancel = True
MsgBox "Make a selection first"
End If

End Sub

When I open the form all the 3 cbos are blank - when I make my
selection on
the first cboStore - it gives me options to choose from on the second
cboManager and after the selection is made it also gives an option on
the
third cboEmployee . So pretty much it is working fine but when I decide
to go
back to the first cboStore and make a new selection, the third
cboEmployee
still display the original option I 've selected. This can cause
issues/confusion for users. I tried to add Me.cboEmployee.Requery to
the
first cboStore After Update event but it did not work. I also added the
Event
on the Sub Form On Current as below but it did not work either.

When I make a selection on the second cboManager only then the
cboEmployee
clears.

I appreciate your suggestions

Another help I would request is how could I save the selections in a
table
so that I can run a query to show the data related to this selection in
a
subform?

Thanks a lot for your help.


Private Sub Form_Current()
Dim lngLoop As Long
Call cboStore_AfterUpdate
For lngLoop = 1 To 20
DoEvents
Next lngLoop
Call cboManager_AfterUpdate
End Sub

I appreciate any help on this.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top