Ken Snell's bombo spinner control

[Anne]

I found this code on Ken Snell's website, which is great for use by one of my
clients, where the user can scroll to the next employee, using this format.
Works great, however, I would like to add some code, where, when the last
employee has been reached, the arrow gets un-highlighted.

Here is the code for the arrow down:
Private Sub cmdDownCbo_Click()
' ------------------------------------------------
'
' Code written by Ken Snell, Microsoft MVP
' 12 June 2005
' Copyright Ken Snell
'
' This code and the combo box and spin button
' controls can be used in any application as
' long as the "copyright" notice is unchanged.
'
' The code cannot be published or distributed
' by any other person except as part of a
' developed ACCESS database application.
'
' ------------------------------------------------
Dim lngCboItem
On Error Resume Next
lngCboItem = Me.EmployeeSelect.ListIndex + 1
If lngCboItem <= Me.EmployeeSelect.ListCount - 1 Then _
Me.EmployeeSelect.Value = Me.EmployeeSelect.ItemData(lngCboItem)
Me.EmployeeSelect.SetFocus
Call EmployeeSelect_AfterUpdate
End Sub

http://www.cadellsoftware.org/SampleDBs.htm

 

[Ken Snell \(MVP\)]

Here is how you would modify the code (by "unhighlighted", I assume you mean
that the down spin arrow loses the focus):

Private Sub cmdDownCbo_Click()
' ------------------------------------------------
'
' Code written by Ken Snell, Microsoft MVP
' 12 June 2005
' Copyright Ken Snell
'
' This code and the combo box and spin button
' controls can be used in any application as
' long as the "copyright" notice is unchanged.
'
' The code cannot be published or distributed
' by any other person except as part of a
' developed ACCESS database application.
'
' ------------------------------------------------
Dim lngCboItem
On Error Resume Next
lngCboItem = Me.EmployeeSelect.ListIndex + 1
If lngCboItem <= Me.EmployeeSelect.ListCount - 1 Then _
Me.EmployeeSelect.Value = Me.EmployeeSelect.ItemData(lngCboItem)
Me.EmployeeSelect.SetFocus
Call EmployeeSelect_AfterUpdate
' ***next lines of code are new
If lngCboItem = Me.EmployeeSelect.ListCount Then
Me.cmdUpCbo.SetFocus
End If
' ***end of new code
End Sub

 

[Ken Snell \(MVP\)]

If "unhighlighted" means the button is disabled, then the code would need to
be changed a bit more, and you'd need to add a code step to the "up" arrow's
procedure. Let me know if this is what you meant, and if yes, I'll reply
with the modified code.

 

[Anne]

Yes, I do mean disabled or/and some message box with somthing like "last
records reached". A similar code would, of course, have to be applied to the
up arrow..
Thanks,
Anne

 

[Ken Snell \(MVP\)]

I have posted new a new version of the sample database at
http://www.cadellsoftware.org/SampleDBs.htm#Cbospin -- the sample database
now contains the original form and a new form that has the automatic
enabling/disabling built into the form.

I did not put a message box into the code, but if you want to add one (I
don't think one is needed?), you can add it to the subroutine that handles
the enabling/disabling of the buttons -- just put your desired message box
into the appropriate subblock of the If..ElseIf..Then block there.

 

[Ken Snell \(MVP\)]

I have posted new a new version of the sample database at
http://www.cadellsoftware.org/SampleDBs.htm#Cbospin -- the sample database
now contains the original form and a new form that has the automatic
enabling/disabling built into the form.

I did not put a message box into the code, but if you want to add one (I
don't think one is needed?), you can add it to the subroutine that handles
the enabling/disabling of the buttons -- just put your desired message box
into the appropriate subblock of the If..ElseIf..Then block there.

 

[Anne]

Works like a charm,
Cool,
Thank you so much. This is such a nice control. For my particular purpose,
the user has a combo box, from which she chooses a foremen, and the second
combo show the employees who work for him, this is where is use the spinner
control. This company has about 15 foremen. This such a time saver, instead
of having select an employees from the combo box, she can now scroll to the
next employee and know when the last employee is reach.

You are right, the message box is not needed.
Thank you very much.
Anne

 

[Ken Snell \(MVP\)]

Thanks for the comments; I'm glad that it's useful! Good luck.

 

[Anne]

Now actually checking through data, I find that the arrow down button does
not work after changing from one forman to the next. Here are the events:
ForemanSelect combo:

Private Sub ForemanSelect_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT [cboEmployeesActive].[EmployeeID],
[cboEmployeesActive].[EmployeeName],[cboEmployeesActive].[ForemanID]" & _
"FROM cboEmployeesActive " & _
"WHERE [ForemanID] = " & Me.ForemanSelect.Value
Me.EmployeeSelect.RowSource = sEmployeeSource
Me.EmployeeSelect.Requery
End Sub

The EmployeesSelect combo:
Private Sub EmployeeSelect_AfterUpdate()
Dim rs As DAO.Recordset
Me.ForemanSelect.Requery
Me.EmployeeSelect.Requery
If Not IsNull(Me.EmployeeSelect) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![EmployeeSelect], 0))
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
''if txtfixedrate.Value = 2
End Sub

I am probably missing some requery, because the drop down of the employee
select combo does display the employees. If I select the first employee, the
arrow up is available, but the arrow down is not. If I click the arrow up,
then the arrow down becomes available.
Any ideas on how to fix that?
Anne

 

[Ken Snell \(MVP\)]

I've read through your code, not sure if I'm following it correctly, but...
why do you do a requery of the EmployeeSelect combo after you select an
employee in that combo box?

I've tried to reproduce what you're seeing, but to no avail. Would you be
willing to email me a copy of the database that is displaying this behavior
so that I can test it directly?

--

Ken Snell
<MS ACCESS MVP>

Now actually checking through data, I find that the arrow down button does
not work after changing from one forman to the next. Here are the events:
ForemanSelect combo:

Private Sub ForemanSelect_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT [cboEmployeesActive].[EmployeeID],
[cboEmployeesActive].[EmployeeName],[cboEmployeesActive].[ForemanID]" & _
"FROM cboEmployeesActive " & _
"WHERE [ForemanID] = " & Me.ForemanSelect.Value
Me.EmployeeSelect.RowSource = sEmployeeSource
Me.EmployeeSelect.Requery
End Sub

The EmployeesSelect combo:
Private Sub EmployeeSelect_AfterUpdate()
Dim rs As DAO.Recordset
Me.ForemanSelect.Requery
Me.EmployeeSelect.Requery
If Not IsNull(Me.EmployeeSelect) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![EmployeeSelect], 0))
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
''if txtfixedrate.Value = 2
End Sub

I am probably missing some requery, because the drop down of the employee
select combo does display the employees. If I select the first employee,
the
arrow up is available, but the arrow down is not. If I click the arrow up,
then the arrow down becomes available.
Any ideas on how to fix that?
Anne

Thanks for the comments; I'm glad that it's useful! Good luck.

 

[Anne]

Ken, thanks for your quick reply.
Today, I cannot recreate it. Yesterday, before my post, I added to the
EmployeeSelect combo the requery of the foremanSelect. But it did not seem to
make any difference.
But when I opened the form today, it works. The requery of the formanSelect
apparently was necessary.

Perhaps I am not doing something correct when making changes to a form.
Should I compile the code after every change? I have noticed that sometimes,
when I make a change to a form, just going from design view back to forms
view, does not always effectively change the form, especially when changing
fields. Sometime I have to exit the form and open it again. What is the
proper way of doing making changes to a form?
Anne

I've read through your code, not sure if I'm following it correctly, but...
why do you do a requery of the EmployeeSelect combo after you select an
employee in that combo box?

I've tried to reproduce what you're seeing, but to no avail. Would you be
willing to email me a copy of the database that is displaying this behavior
so that I can test it directly?

--

Ken Snell
<MS ACCESS MVP>

Now actually checking through data, I find that the arrow down button does
not work after changing from one forman to the next. Here are the events:
ForemanSelect combo:

Private Sub ForemanSelect_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT [cboEmployeesActive].[EmployeeID],
[cboEmployeesActive].[EmployeeName],[cboEmployeesActive].[ForemanID]" & _
"FROM cboEmployeesActive " & _
"WHERE [ForemanID] = " & Me.ForemanSelect.Value
Me.EmployeeSelect.RowSource = sEmployeeSource
Me.EmployeeSelect.Requery
End Sub

The EmployeesSelect combo:
Private Sub EmployeeSelect_AfterUpdate()
Dim rs As DAO.Recordset
Me.ForemanSelect.Requery
Me.EmployeeSelect.Requery
If Not IsNull(Me.EmployeeSelect) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![EmployeeSelect], 0))
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
''if txtfixedrate.Value = 2
End Sub

I am probably missing some requery, because the drop down of the employee
select combo does display the employees. If I select the first employee,
the
arrow up is available, but the arrow down is not. If I click the arrow up,
then the arrow down becomes available.
Any ideas on how to fix that?
Anne

Thanks for the comments; I'm glad that it's useful! Good luck.

--

Ken Snell
<MS ACCESS MVP>

Works like a charm,
Cool,
Thank you so much. This is such a nice control. For my particular
purpose,
the user has a combo box, from which she chooses a foremen, and the
second
combo show the employees who work for him, this is where is use the
spinner control. This company has about 15 foremen. This such a time
saver, instead of having select an employees from the combo box, she
can
now scroll to the next employee and know when the last employee is
reach.

You are right, the message box is not needed.
Thank you very much.
Anne

 

[Ken Snell \(MVP\)]

Probably good to save the design changes and code changes before going to
form view. Compiling the code usually isn't needed, as ACCESS will do that
when the code is called -- when all changes are done, I then do compile the
code.
--

Ken Snell
<MS ACCESS MVP>

Ken, thanks for your quick reply.
Today, I cannot recreate it. Yesterday, before my post, I added to the
EmployeeSelect combo the requery of the foremanSelect. But it did not seem
to
make any difference.
But when I opened the form today, it works. The requery of the
formanSelect
apparently was necessary.

Perhaps I am not doing something correct when making changes to a form.
Should I compile the code after every change? I have noticed that
sometimes,
when I make a change to a form, just going from design view back to forms
view, does not always effectively change the form, especially when
changing
fields. Sometime I have to exit the form and open it again. What is the
proper way of doing making changes to a form?
Anne

I've read through your code, not sure if I'm following it correctly,
but...
why do you do a requery of the EmployeeSelect combo after you select an
employee in that combo box?

I've tried to reproduce what you're seeing, but to no avail. Would you be
willing to email me a copy of the database that is displaying this
behavior
so that I can test it directly?

--

Ken Snell
<MS ACCESS MVP>

Now actually checking through data, I find that the arrow down button
does
not work after changing from one forman to the next. Here are the
events:
ForemanSelect combo:

Private Sub ForemanSelect_AfterUpdate()
Dim sEmployeeSource As String
sEmployeeSource = "SELECT [cboEmployeesActive].[EmployeeID],
[cboEmployeesActive].[EmployeeName],[cboEmployeesActive].[ForemanID]" &
_
"FROM cboEmployeesActive " & _
"WHERE [ForemanID] = " & Me.ForemanSelect.Value
Me.EmployeeSelect.RowSource = sEmployeeSource
Me.EmployeeSelect.Requery
End Sub

The EmployeesSelect combo:
Private Sub EmployeeSelect_AfterUpdate()
Dim rs As DAO.Recordset
Me.ForemanSelect.Requery
Me.EmployeeSelect.Requery
If Not IsNull(Me.EmployeeSelect) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![EmployeeSelect], 0))
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
''if txtfixedrate.Value = 2
End Sub

I am probably missing some requery, because the drop down of the
employee
select combo does display the employees. If I select the first
employee,
the
arrow up is available, but the arrow down is not. If I click the arrow
up,
then the arrow down becomes available.
Any ideas on how to fix that?
Anne


:

Thanks for the comments; I'm glad that it's useful! Good luck.

--

Ken Snell
<MS ACCESS MVP>

Works like a charm,
Cool,
Thank you so much. This is such a nice control. For my particular
purpose,
the user has a combo box, from which she chooses a foremen, and the
second
combo show the employees who work for him, this is where is use the
spinner control. This company has about 15 foremen. This such a time
saver, instead of having select an employees from the combo box, she
can
now scroll to the next employee and know when the last employee is
reach.

You are right, the message box is not needed.
Thank you very much.
Anne