Spin button refresh

[Abhean]

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.

Private Sub BtnClearRocheQR_Click()
    'set searchbar to blank
    Me.comboSearchRocheQR = Null
    'Put focus back on searchbar
    Me.comboSearchRocheQR.SetFocus
End Sub
 
Private Sub SpinButton8_Updated(Code As Integer)
    'refresh the page so user can see the change
    Refresh
End Sub
 

Is there something else I need to be using to use instead of the spinbutton or code modification to take care of this error?

[twinnyfo]

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:

Private Sub SpinButton8_Updated(Code As Integer)
On Error GoTo EH
    'refresh the page so user can see the change
    Call Me.Refresh
EH:
    If Err.Number = 2115 Then Resume Next
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!

[Abhean]

Sweet! Thank you so much Twinnyfo (once again) for your awesome help.

[ADezii]

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.

Private Sub txtSpinDemo_AfterUpdate()
Dim txt As Access.TextBox
 
Set txt = Me![txtSpinDemo]
 
'Make sure Field is NOT NULL and is Numeric
If IsNull(txt) Or Not IsNumeric(CStr(txt)) Then
    Exit Sub
Else
  'Is Value entered into Text Box is not within the MIN and MAX Limits
  'of the Spin Button, then notify User
  If Val(txt.Value) < Me![SpinButton4].Min Or Val(txt.Value) > Me![SpinButton4].Max Then
    MsgBox "The Range of Values should be between " & Me![SpinButton4].Min & " and " & _
            Me![SpinButton4].Max & "!", vbExclamation, "Invalid Entry"
    txt.Value = Null
  Else
    'If you get here, all is OK so write Value back to Spin Button
    Me![SpinButton4].Value = Me![txtSpinDemo].Value
  End If
End If
End Sub

[Abhean]

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.

Private Sub Spinbutton8_spindown()
    'set usagedate
    MsgBox "Changes saved?"
End Sub
 
Private Sub Spinbutton8_spinup()
    'set usagedate
    MsgBox "Changes saved?"
End Sub
 

Happy happy, thanks!

[ADezii]

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:

Private Sub SpinButton1_SpinUp()
  MsgBox "Spin Up"
End Sub
 

Private Sub SpinButton1_SpinDown()
  MsgBox "Spin Down"
End Sub

[twinnyfo]

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.

[ADezii]

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).

[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:

Option Compare Database
Option Explicit
 
Private Sub Form_Current()
    Me.Spinbutton8 = Me.txtTextBox
    'This assumes text box is bound
    'Vice versa if spin button is bound
End Sub
 
Private Sub Spinbutton8_Updated(Code As Integer)
    Me.txtValue = Me.Spinbutton8
End Sub
 
Private Sub txtTextBox_AfterUpdate()
    Me.Spinbutton8 = Me.txtTextBox
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.

[NeoPa]

Always happy to oblige Twinny - But enough with the tautology!!

[twinnyfo]

Awwww, Dad! I'm just trying to have some fun!

:-)