How to Update record from message box

[Microblitz]

Hi All,

I started messing about with VBA yesterday (I'm a newbie at this language) and have run across a problem which i hope you can help me with.

I'm writing a little stock control program in clicking a command button opens a message box which asks for a number of items to add to the currently displayed record in the main form (called Parts).

You's think this would be simple and i have some code which i have cobbled together which I would work if I could keep the record from zipping back to record 1 everytime I save to the "current_Stock" field.

The main form has previous/next record buttons and a fairly standard "search by dropdown box" which all seems to be working.

So what I'm working toward is ;

Select record by search or prev/next button
Click Command button to add inventory stock
Enter value in message box
Value is added to current record/current_stock field
messagebox closes revealing updated record.

Private Sub Command83_Click()
On Error GoTo Err_Command83_Click
 
    Dim intTemp As Integer
    Dim Vtemp As Integer
    Dim Resultant As Integer
 
    Vtemp = CurrentDb.OpenRecordset("SELECT * FROM Part")(12)
    intTemp = InputBox("Sub Current Stock", "Add Stock", 0) 
    Me.Current_Stock = intTemp + Vtemp
 
Exit_Command83_Click:
    Me.Requery
    Exit Sub
Err_Command83_Click:
MsgBox "Chris has officially cocked up!" & vbCrLf & vbCrLf & "With the Error No: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_Command83_Click
 

[gershwyn]

I'm not sure why you're going back to a previous record, but I would change a few things about your code. Your main form is bound to "Parts", right? Then you should be able to add to the current_stock field directly, without mucking about with recordsets (which I suspect is somehow related to your problem.)

Private Sub Command83_Click()
On Error GoTo Err_Command83_Click
 
    Dim strTemp As String
    strTemp = InputBox("Sub Current Stock", "Add Stock", 0) 
    If IsNumeric(strTemp) Then
      Me!Current_Stock = Me!Current_Stock + Int(strTemp)
    Else
      MsgBox "Please enter a numeric value."
    End If
 
Exit_Command83_Click:
    Me.Requery
    Exit Sub
Err_Command83_Click:
MsgBox "Chris has officially cocked up!" & vbCrLf & vbCrLf & "With the Error No: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_Command83_Click
 
End Sub
 

I changed the return value from inputBox to a string, so text input will be handled without raising an error.

MsgBox "Chris has officially cocked up!"

As I am also a Chris, I may start adding to this my error handling routines. Though it does raise the question, which is worse - an official or an unofficial cock up?

[NeoPa]

I'm struggling to work out what you're saying your problem is, so I'll just offer a snippet of advice. Generally, rather than requiring an InputBox call, you could design the form with an unbound TextBox (or ComboBox in some circumstances) control where the operator could enter their value. This has various benefits, including the continued visibility of the value as well as much better control of it (You can specify the sort of data to accept etc). You can even hide it at certain times if that is important, so that it only shows when you feel it's appropriate.

Welcome to Bytes!

[Microblitz]

@gershwyn
I have reverted the official to unofficial error:)

Took a look at your code and it works but the record jumps back to record one in the database as soon as that message box is closed. There must be somthing else causing the issue.

I'm just reverting back to an earlier evrsion to see if its somthing that I added in my search to clear the fault.

[FishVal]

@Microblitz

Me.Requery

points form to a first record after loading recordset. It is not a bug, it is a feature.
If you need to just save a current record you could use one of the following:

Me.Dirty = False

DoCmd.RunCommand acCmdSaveRecord

[ADezii]

If you insist on maintaining this approach, then you should provide some form of minimal Validation on the Value entered in the Input Box, as in:

Dim strTemp As String
Dim Vtemp As Integer
Dim Resultant As Integer
 
strTemp = InputBox$("Sub Current Stock", "Add Stock", 0)
 
If strTemp = "" Or Not IsNumeric(strTemp) Or strTemp = "0" Then
  Exit Sub
End If
 
'If you get here, you have a legitimate, Numeric Value >= 1
Me.Current_Stock = Val(strTemp) + Vtemp
 'continue...