By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Spin button refresh

Abhean
P: 19
Working on a form in access for medical inventory system. This form I want to allow the user to use up and down arrows (spinbutton) to modify the "OnHand" quantity.

The spin button works fine. I placed a refresh in SpinButton_Updated so that the user could see the qty being changed. However, if the user simply wishes to change the amount by hand from the text box I have a Runtime '2115' that is thrown saying that a macro or function set to the BeforeUpdate or Validation rule property is preventing access from saving data. The error is thrown at the refresh command.

Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnClearRocheQR_Click()
  2.     'set searchbar to blank
  3.     Me.comboSearchRocheQR = Null
  4.     'Put focus back on searchbar
  5.     Me.comboSearchRocheQR.SetFocus
  6. End Sub
  7.  
  8. Private Sub SpinButton8_Updated(Code As Integer)
  9.     'refresh the page so user can see the change
  10.     Refresh
  11. End Sub
  12.  
Is there something else I need to be using to use instead of the spinbutton or code modification to take care of this error?
3 Weeks Ago #1

✓ answered by twinnyfo

ADezii,

You got me into experimentation mode!

Yes, it is possible to have two different controls bound to the same underlying field--which is how I duplicated OP's error in the first place.

However, I would recommend the following as a proposed new best answer, as it eliminates the need for Error Trapping and works within the requirements of the different controls.

First, either the text box or the spin button (but not both) should be bound to the underlying field. The other control should be unbound.

Then, your code is as simple as the following:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Me.Spinbutton8 = Me.txtTextBox
  6.     'This assumes text box is bound
  7.     'Vice versa if spin button is bound
  8. End Sub
  9.  
  10. Private Sub Spinbutton8_Updated(Code As Integer)
  11.     Me.txtValue = Me.Spinbutton8
  12. End Sub
  13.  
  14. Private Sub txtTextBox_AfterUpdate()
  15.     Me.Spinbutton8 = Me.txtTextBox
  16. End Sub
As you can see, we are simply applying the new value of the updated control to the other control whenever either is updated. A whole lot more less clumsier than the previous solution.

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
The best way to manage this is to use error handling to negate that error--since there is nothing that is really causing anything to break if you accept the value entered manually:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SpinButton8_Updated(Code As Integer)
  2. On Error GoTo EH
  3.     'refresh the page so user can see the change
  4.     Call Me.Refresh
  5. EH:
  6.     If Err.Number = 2115 Then Resume Next
  7. End Sub
I also added the "Me." prefix to your call to Refresh. It is always better to be more explicit, so that the DB knows exactly what it is you are Refreshing.

However, I cannot explain "why" the controls behave this way.

Hope this hepps!
3 Weeks Ago #2

Abhean
P: 19
Sweet! Thank you so much Twinnyfo (once again) for your awesome help.
3 Weeks Ago #3

ADezii
Expert 5K+
P: 8,615
You should also check and see if the Value entered into the Text Box is not NULL, is Numeric, and is within the acceptable Range (MIN <==> MAX) of the Spin Button Control. To accomplish all that and actually write the Value back to the Spin Button Control, I created a simple Demo for. Obviously, it makes assumptions about Control Names and the Code exists in the AfterUpdate() Event of the Text Box.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSpinDemo_AfterUpdate()
  2. Dim txt As Access.TextBox
  3.  
  4. Set txt = Me![txtSpinDemo]
  5.  
  6. 'Make sure Field is NOT NULL and is Numeric
  7. If IsNull(txt) Or Not IsNumeric(CStr(txt)) Then
  8.     Exit Sub
  9. Else
  10.   'Is Value entered into Text Box is not within the MIN and MAX Limits
  11.   'of the Spin Button, then notify User
  12.   If Val(txt.Value) < Me![SpinButton4].Min Or Val(txt.Value) > Me![SpinButton4].Max Then
  13.     MsgBox "The Range of Values should be between " & Me![SpinButton4].Min & " and " & _
  14.             Me![SpinButton4].Max & "!", vbExclamation, "Invalid Entry"
  15.     txt.Value = Null
  16.   Else
  17.     'If you get here, all is OK so write Value back to Spin Button
  18.     Me![SpinButton4].Value = Me![txtSpinDemo].Value
  19.   End If
  20. End If
  21. End Sub
3 Weeks Ago #4

Abhean
P: 19
while we are on spin buttons. Is there a setting between the "up arrow" and the "down Arrow"?

Meaning, if I wanted to assign a value to a field if they click the up arrow, but another value when down arrow was clicked.

Edited:
Found the answer
spinup and spindown function.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Spinbutton8_spindown()
  2.     'set usagedate
  3.     MsgBox "Changes saved?"
  4. End Sub
  5.  
  6. Private Sub Spinbutton8_spinup()
  7.     'set usagedate
  8.     MsgBox "Changes saved?"
  9. End Sub
  10.  
Happy happy, thanks!
3 Weeks Ago #5

ADezii
Expert 5K+
P: 8,615
The Spin Button Control has two Events that will allow you to execute two different actions depending on which Button was clicked. I am referring to the SpinUp() and SpinDown() Events. An example follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SpinButton1_SpinUp()
  2.   MsgBox "Spin Up"
  3. End Sub
  4.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub SpinButton1_SpinDown()
  2.   MsgBox "Spin Down"
  3. End Sub
3 Weeks Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,158
Also, to add more discussion to this issue....

Thisis only a guess, but I surmise that you have both the SpinButton Control and the TextBox Control bound to the same field in the underlying record source? In such cases, you may be asking for the described problem.

A "more better" way to design it is to have one or the other bound to the underlying record source, but not both. Then, whenever you update one, you update the other through VBA beneath the form. This "may" alleviate the problem altogether, since you are not conflicting the table with trying to write to teh same field with two different controls.

Just a hunch--I haven't tested this. None of my applications use incremental adjustments to data like this.

Just more fodder for thought.
3 Weeks Ago #7

ADezii
Expert 5K+
P: 8,615
but I surmise that you have both the SpinButton Control and the TextBox Control bound to the same field in the underlying record source?
I don't think that both Controls can be Bound at the same time (have the same Control Source).
3 Weeks Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,158
ADezii,

You got me into experimentation mode!

Yes, it is possible to have two different controls bound to the same underlying field--which is how I duplicated OP's error in the first place.

However, I would recommend the following as a proposed new best answer, as it eliminates the need for Error Trapping and works within the requirements of the different controls.

First, either the text box or the spin button (but not both) should be bound to the underlying field. The other control should be unbound.

Then, your code is as simple as the following:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Me.Spinbutton8 = Me.txtTextBox
  6.     'This assumes text box is bound
  7.     'Vice versa if spin button is bound
  8. End Sub
  9.  
  10. Private Sub Spinbutton8_Updated(Code As Integer)
  11.     Me.txtValue = Me.Spinbutton8
  12. End Sub
  13.  
  14. Private Sub txtTextBox_AfterUpdate()
  15.     Me.Spinbutton8 = Me.txtTextBox
  16. End Sub
As you can see, we are simply applying the new value of the updated control to the other control whenever either is updated. A whole lot more less clumsier than the previous solution.
3 Weeks Ago #9

NeoPa
Expert Mod 15k+
P: 31,302
Always happy to oblige Twinny - But enough with the tautology!!
3 Weeks Ago #10

twinnyfo
Expert Mod 2.5K+
P: 3,158
Awwww, Dad! I'm just trying to have some fun!

:-)
3 Weeks Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.