BruceS said:
Dirk,
Maybe I am doing it the hard way...
There is a list of production machines with various specs stored in a
table. I use a query to select specific ones compatible with the
task being done, and load that result as the RowSource for the combo,
sorted by the "common name". (One of them is "Matilda", believe it
or not.) The terminal on which the form runs has a mouse, but the
operators vastly perfer to use the touchscreen, so I need a way for
the operator to scroll up or down the list by touching a button until
they reach the machine they want to use. The "show list" button on
the combo box control is not big enough to use on the touchscreen
and, even if it was, it'd be difficult for them to touch the proper
line entry in the list.
Current logic reads the up/down button click then
increments/decrements the index to change the values shown on the
screen and stored in variables. I pull the values from .Column(2),
.Column(3), etc. to use in controlling the selected machine.
I can't just assign the value of the next item because I don't know
what it is. The MachID field (value for the combo box) is not
sequential. I guess I could open a recordset and move up or down
through it, but that seemed like a lot more coding effort than just
dropping the combo box on the form.
You've been doing VBA a lot longer than me so, if you have a
suggestion for accomplishing the same thing a better way, I'd love to
hear it. Always willing to learn!
How about using functions like these?
'----- start of code -----
Public Function AdvanceCombo(cbo As Access.ComboBox) As Long
' Advances the argument combo box to the next entry in its list,
' and returns the ListIndex value of that entry. If the combo box
' is already at the last entry, the value and ListIndex of the combo
' remain unchanged.
Dim lngNext As Long
With cbo
lngNext = .ListIndex + 1
If lngNext < .ListCount Then
.Value = .ItemData(lngNext)
AdvanceCombo = lngNext
Else
AdvanceCombo = .ListIndex
End If
End With
End Function
Public Function RetreatCombo(cbo As Access.ComboBox) As Long
' Retreats the argument combo box to the previous entry in its list,
' and returns the ListIndex value of that entry. If the combo box
' is already at the first entry -- or is not set to any entry -- the
' value and ListIndex of the combo remain unchanged.
Dim lngPrevious As Long
With cbo
lngPrevious = .ListIndex - 1
If lngPrevious >= 0 Then
.Value = .ItemData(lngPrevious)
RetreatCombo = lngPrevious
Else
RetreatCombo = .ListIndex
End If
End With
End Function
'----- end of code -----
Note -- I just wrote those functions, so they've only been lightly
tested, and obviously any error-handling is left as an exercise for the
reader. <g>
With those functions in a standard module, you could code the event
procedures for your "up" and "down" buttons as simply as this:
'----- start of code -----
Private Sub cmdComboUp_Click()
AdvanceCombo Me.cboMachine
End Sub
Private Sub cmdComboDown_Click()
RetreatCombo Me.cboMachine
End Sub
'----- end of code -----
Or you could even forego the event procedures entirely, and set the
buttons' OnClick event properties to simple function expressions:
=AdvanceCombo([cboMachine])
=RetreatCombo([cboMachine])
BTW, I know you MVPs don't hear this often, but you're a tremendous
resource. Thanks for being there.
You're very welcome, Bruce. Thank you for the kind word.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
