Hi all
Does anyone know anything about an ActiveX Control
ciaXPSpin30.XPSpin30
I find them good except that you can't change some of the values.
ie Max, Min etc
The max is set at 10 and i would like to set it to 3000 and the Min to 2000
Active X controls and Access do not play great together. If this is just for you that may be OK, but may be unlikely to port over to someone else. I have no idea what that control is. If this is just a spin button you may be better off just rolling your own. You can also try the MSFORMS spin control, but still the same AX issues. Should be easy enough. What exactly are you wanting to do?
Ok. So use the Microsoft Forms 2.0 Spin Control as already suggested or , if you have it, the MS UpDown control.
But take note of MajP's warnings if you intend to distribute this. ActiveX controls may not work in all versions and some people/organisations block their use
You may also get your own use blocked.
I have a lot of dbs using MSFORMS control on my work computer. Then we went to Windows 10/AC2016 and no longer available, and I do not have permissions to register them. So they are no longer usable applications.
My recommendation would be
1. to brute force and just make a bunch of these using command buttons.
2. Or make one of these in a subform and use a a subform for each simulating a user control.
3. Or make a class module that traps the events so all you have to do is initialize it and set the properties.
MajP
You may already be aware of this but in case not...
Although the Microsoft Forms 2.0 Object Library doesn't appear in the VBA reference list by default when using Windows 10, it is installed and can be added to the list by browsing to the location
Depending on the machine, you may find it at:
C:\Windows\System32.FM20.dll
C:\Program Files\Microsoft Office\Root\VFS\SystemX86\FM20.dll
It should work 'out of the tin' as its already registered so no permissions required
So I rolled my own Spinner and for Access purposes it is better than any Active X, because all the functionality is incorporated into the class module. Requires no additional coding to synch the spinner with the value in the text box. Further it captures the oncurrent event of the form so as you change records the value of the spinner equals the default value of the control. You can set your increment, min value, max value.
This is how you would use it. This goes in the form for each of your "spinners". To make a spinner you need an up button, down button, and textbox to "bind" to.
Code:
Option Compare Database
Option Explicit
Private WithEvents SpinnerLaid As AccessSpinner
Private WithEvents SpinnerFert As AccessSpinner
Private Sub Form_Load()
Set SpinnerLaid = New AccessSpinner
Set SpinnerFert = New AccessSpinner
SpinnerLaid.InitializeSpinner Me.cmdUpLaid, Me.cmdDownLaid, Me.EggsLaid, 1, 0, 20
SpinnerFert.InitializeSpinner Me.cmdUpFert, Me.cmdDownFert, Me.EggsFert, 1, 0, 30
End Sub
'---------- Then Class module has a change event and passes back the value
Private Sub SpinnerFert_Change(SpinnerValue As Double)
Me.EggsFert.Value = SpinnerValue
End Sub
Private Sub SpinnerLaid_Change(SpinnerValue As Double)
Me.EggsLaid.Value = SpinnerValue
End Sub
1. You need to Declare a spinner as an AccessSpinner using WithEvents
Private WithEvents SpinnerLaid As AccessSpinner
2. In the forms on load you need to initialze it by passing in the
up button, down button, the text box, the increment, the min value, and the maxvalue
increment, min and max are optional
3. Then you can capture the event for each spinner to update your
No other code is needed.
Then you need the following class module. It must be named "AccessSpinner". This should not be edited.
Code:
Option Compare Database
Option Explicit
Private WithEvents mCmdUp As Access.CommandButton
Private WithEvents mCmdDwn As Access.CommandButton
Private WithEvents mTextBox As Access.TextBox
Private WithEvents mForm As Access.Form
Private mSpinnerValue As Double
Private mIncrement As Double
Private mMaxValue As Double
Private mMinValue As Double
Public Event Change(SpinnerValue As Double)
'------------------------------------------ Properties -------------------------------------------------------------
Public Property Get CommandUp() As Access.CommandButton
Set CommandUp = mCmdUp
End Property
Public Property Set CommandUp(CommandButtonUp As Access.CommandButton)
Set mCmdUp = CommandButtonUp
End Property
Public Property Get CommandDown() As Access.CommandButton
Set CommandDown = mCmdDwn
End Property
Public Property Set CommandDown(CommandButtonDown As Access.CommandButton)
Set mCmdDwn = CommandButtonDown
End Property
Public Property Get SpinnerValue() As Double
SpinnerValue = mSpinnerValue
End Property
Public Property Let SpinnerValue(ByVal NewValue As Double)
mSpinnerValue = NewValue
End Property
Public Property Get Increment() As Double
Increment = mIncrement
End Property
Public Property Let Increment(ByVal TheIncrement As Double)
mIncrement = TheIncrement
End Property
Public Property Get minValue() As Double
minValue = mMinValue
End Property
Public Property Let minValue(ByVal TheMinValue As Double)
mMinValue = TheMinValue
End Property
Public Property Get maxvalue() As Double
maxvalue = mMaxValue
End Property
Public Property Let maxvalue(ByVal TheMaxValue As Double)
mMaxValue = TheMaxValue
End Property
Public Property Get TextBox() As Access.TextBox
Set TextBox = mTextBox
End Property
Public Property Set TextBox(TheTextBox As Access.TextBox)
Set mTextBox = TheTextBox
End Property
Public Property Get Form() As Access.Form
Set Form = mForm
End Property
Public Property Set Form(TheForm As Access.Form)
Set mForm = TheForm
End Property
'------------------------------------------ Handled Events -------------------------------------------------------------
Private Sub mCmdUp_Click()
If Me.SpinnerValue + Me.Increment < Me.maxvalue Then
Me.SpinnerValue = Me.SpinnerValue + Increment
Else
Me.SpinnerValue = Me.maxvalue
End If
RaiseEvent Change(Me.SpinnerValue)
End Sub
Private Sub mCmdDwn_Click()
If Me.SpinnerValue - Me.Increment > Me.minValue Then
Me.SpinnerValue = Me.SpinnerValue - Increment
Else
Me.SpinnerValue = Me.minValue
End If
RaiseEvent Change(Me.SpinnerValue)
End Sub
Private Sub mForm_Current()
Me.SpinnerValue = Nz(Me.TextBox.Value, 0)
End Sub
Private Sub mTextBox_Change()
If IsNumeric(Nz(Me.TextBox.Text, 0)) Then Me.SpinnerValue = Nz(Me.TextBox.Text, 0)
End Sub
'------------------------------------------ Class Procedures
Private Sub Class_Initialize()
Me.Increment = 1
Me.minValue = -99999999
Me.maxvalue = 999999999
End Sub
Public Sub InitializeSpinner(CommandUp As Access.CommandButton, CommandDown As Access.CommandButton, SpinnerTextBox As Access.TextBox, Optional Increment = 1, Optional ByVal minValue As Double = -99999999, Optional ByVal maxvalue As Double = 999999999)
Set Me.CommandUp = CommandUp
Set Me.CommandDown = CommandDown
Set Me.TextBox = SpinnerTextBox
Set Me.Form = Me.TextBox.Parent
Me.Increment = Increment
If IsNumeric(Nz(Me.TextBox.Value, 0)) Then Me.SpinnerValue = Nz(Me.TextBox.Value, 0)
Me.maxvalue = maxvalue
Me.minValue = minValue
Me.CommandUp.OnClick = "[Event Procedure]"
Me.CommandDown.OnClick = "[Event Procedure]"
Me.TextBox.OnChange = "[Event Procedure]"
Me.Form.OnCurrent = "[Event Procedure]"
End Sub
Give it a try and see. If you use the "up arrow" "down arrow" image it will look like a Spinner
Glad it works. PM if you have any problems. It has some potentially "overkill" functionality so that you can simulate binding it to a control. It automatically gets the default value from the control, updates on the form current event, and automatically updates if you type in the textbox. These are all features, but could limit its generic use if not desired.
I will probably post this in the code forum, it has some really good advanced vba. Trapping Access object events in custom class, raising custom events, and trapping custom events back in an Access object. Also in my opinion this approach is the closest thing to making a user control in vba.
The other way to do this is to make a subform with a command up, and command down. Most of the code remains same. You would then be limited that each "spinner" would be sized and look the same. Your example showed one larger spinner for a larger textbox. You would be forced then to copy the subform and make an additional subform.
When I post the db I will show both techniques and see which is easier for the user to add a spinner. Should be pretty similar, I think this way is more flexible.
Thanks was able to load it. Then after I posted, I realized that is not what I am really concerned about since I do not really use a lot of MSFORMS. Do not know what I was thinking. What I do use a lot are Microsoft Common Controls mscomctl.ocx. I have some very complicated Tree views with custom classes. I wish Access provided a native tree view because you can do so much with it especially with self referencing data.
The MSCOMCTL does not seem to come anymore with W10. When I downloaded it comes in an executeable for installing and I assume registering and my system at work blocks it.
The MSCOMCTL does not seem to come anymore with W10. When I downloaded it comes in an executeable for installing and I assume registering and my system at work blocks it.
There are various non-ActiveX versions of the treeview control online
The only ActiveX controls I use are the AX web browser control & occasionally the slider.
I'm gradually replacing the latter item as I do application updates but at the moment have no alternative to the AX web browser
Are you saying there is a compatible Treeview control that will work in Access? If so would be interested if you know of some. I seen some attempts using forms but they are really primitive.
I found one https://www.jkp-ads.com/articles/treeview.asp. This is really an interesting concept, basically building a dynamic user form. This is gives me all kinds of IDs. Never thought you would be able to get good performance, but they claim so.
Yes there are several including some that work in 64-bit Access.
Here's one that I tried some time ago https://www.jkp-ads.com/Articles/treeview.asp
I rarely use tree views and can't remember how good it is but it is recommended by Daniel Pineault at the DevHut site and others.
Yes there are several including some that work in 64-bit Access.
Here's one that I tried some time ago https://www.jkp-ads.com/Articles/treeview.asp
I rarely use tree views and can't remember how good it is but it is recommended by Daniel Pineault at the DevHut site and others.
. When I post the db I will show both techniques and see which is easier for the user to add a spinner. Should be pretty similar, I think this way is more flexible.
Yes, I have been using the Treeview control from https://www.jkp-ads.com/Download.asp.
and this is some of the best VBA code I have ever seen. Works better than I would have expected. This has opened my eyes to some things you can do in VBA. Using an MS form has capabilities far beyond an access form for dynamic controls. I have built a class to extend loading the treeview to make it generic.
