469,903 Members | 2,264 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command83_Click()
  2. On Error GoTo Err_Command83_Click
  3.  
  4.     Dim intTemp As Integer
  5.     Dim Vtemp As Integer
  6.     Dim Resultant As Integer
  7.  
  8.     Vtemp = CurrentDb.OpenRecordset("SELECT * FROM Part")(12)
  9.     intTemp = InputBox("Sub Current Stock", "Add Stock", 0) 
  10.     Me.Current_Stock = intTemp + Vtemp
  11.  
  12. Exit_Command83_Click:
  13.     Me.Requery
  14.     Exit Sub
  15. Err_Command83_Click:
  16. MsgBox "Chris has officially cocked up!" & vbCrLf & vbCrLf & "With the Error No: " & Err.Number & vbCrLf & Err.Description
  17.     Resume Exit_Command83_Click
  18.  
Jul 2 '10 #1

✓ answered by 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.)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command83_Click()
  2. On Error GoTo Err_Command83_Click
  3.  
  4.     Dim strTemp As String
  5.     strTemp = InputBox("Sub Current Stock", "Add Stock", 0) 
  6.     If IsNumeric(strTemp) Then
  7.       Me!Current_Stock = Me!Current_Stock + Int(strTemp)
  8.     Else
  9.       MsgBox "Please enter a numeric value."
  10.     End If
  11.  
  12. Exit_Command83_Click:
  13.     Me.Requery
  14.     Exit Sub
  15. Err_Command83_Click:
  16. MsgBox "Chris has officially cocked up!" & vbCrLf & vbCrLf & "With the Error No: " & Err.Number & vbCrLf & Err.Description
  17.     Resume Exit_Command83_Click
  18.  
  19. End Sub
  20.  
I changed the return value from inputBox to a string, so text input will be handled without raising an error.

Expand|Select|Wrap|Line Numbers
  1. 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?

5 2554
gershwyn
122 100+
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.)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command83_Click()
  2. On Error GoTo Err_Command83_Click
  3.  
  4.     Dim strTemp As String
  5.     strTemp = InputBox("Sub Current Stock", "Add Stock", 0) 
  6.     If IsNumeric(strTemp) Then
  7.       Me!Current_Stock = Me!Current_Stock + Int(strTemp)
  8.     Else
  9.       MsgBox "Please enter a numeric value."
  10.     End If
  11.  
  12. Exit_Command83_Click:
  13.     Me.Requery
  14.     Exit Sub
  15. Err_Command83_Click:
  16. MsgBox "Chris has officially cocked up!" & vbCrLf & vbCrLf & "With the Error No: " & Err.Number & vbCrLf & Err.Description
  17.     Resume Exit_Command83_Click
  18.  
  19. End Sub
  20.  
I changed the return value from inputBox to a string, so text input will be handled without raising an error.

Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 2 '10 #2
NeoPa
32,231 Expert Mod 16PB
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!
Jul 2 '10 #3
@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.
Jul 2 '10 #4
FishVal
2,653 Expert 2GB
@Microblitz
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Me.Dirty = False
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSaveRecord
Jul 2 '10 #5
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strTemp As String
  2. Dim Vtemp As Integer
  3. Dim Resultant As Integer
  4.  
  5. strTemp = InputBox$("Sub Current Stock", "Add Stock", 0)
  6.  
  7. If strTemp = "" Or Not IsNumeric(strTemp) Or strTemp = "0" Then
  8.   Exit Sub
  9. End If
  10.  
  11. 'If you get here, you have a legitimate, Numeric Value >= 1
  12. Me.Current_Stock = Val(strTemp) + Vtemp
  13.  'continue...
Jul 3 '10 #6

Post your reply

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

Similar topics

1 post views Thread by oldandgrey | last post: by
1 post views Thread by toedipper | last post: by
2 posts views Thread by Tinius | last post: by
1 post views Thread by Daniel | last post: by
1 post views Thread by dreamy | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.