how to keep or alter message displayed when code is run

[bbrazeau]

Hi I have the following code that runs when my Inventory transactions form is opened. The code's purpose is when the user opens it from a button on my Order details form is to automatically subtract the quantity ordered from inventory. It works, but gives a message saying the open form action was cancelled and gives no clue that it was successful. Is there a way to stop this message and replace it with confirmation that the action was successful?

Private Sub Form_Load()
Dim lngProduct As Long
Dim intPos As Integer
Dim lngProductQty As Long
If Not IsNull(Me.OpenArgs) Then
' Position of pipe
intPos = InStr(Me.OpenArgs, "|")

If intPos > 0 Then
' Retrieve Product from 1st part of string
lngProduct = Left$(Me.OpenArgs, intPos - 1)
' Retrieve ProductQty from the end of string


lngProductQty = Mid$(Me.OpenArgs, intPos + 1)
DoCmd.GoToRecord , , acNewRec
'Assign 1st value to control
Me.Product.SetFocus
Me.Product = lngProduct
Me.TransactionType.SetFocus
Me.TransactionType = "Remove"
'Assign end value to control
Me.TransactionQty.SetFocus
Me.TransactionQty = lngProductQty
End If
DoCmd.Save
DoCmd.Close
Else
End If
End Sub

[Homegrownandy]

There is no message box in that code, and there is also no open form action in that code.

Does it only trigger the message when a button is pressed?

[Minty]

Firstly you don't need to set focus on controls before setting their values, in fact it can lead to some undesired side effects(possibly your problem).

You move to a new record after opening the form, if this forms sole purpose is to add records open it in Add mode instead.

DoCmd.Save doesn't save your record, it saves any changes to the form.
Use the records Dirty property to check if any changes have been made and save if needed;

If Me.Dirty Then Me.Dirty = False

[Micron]

I think it's because the form is being closed in the Load event. When you open a form, a sequence of events occurs Open>Load>Resize>Activate>Current. I believe if you close the form during one of these latter events, you'll get that message, which is probably why the Cancel for the Load event was provided. I think it's odd to close a form from any of these events for that form.

[June7]

Are you saving calculated balance? Aggregate data should be calculated from raw data when needed. Review http://allenbrowne.com/AppInventory.html

Where does the subtraction calc take place?

Please post code snippets between CODE tags to retain indentation and readability, especially longer ones.

[davegri]

I believe OP is showing wrong code. He describes problem occurs when button on form is clicked, which is not code shown.
The code shown appends a three-field record to a table using a form as an untouched vector. I would have used an append query.

[bbrazeau]

Thanks, davegri, that worked!

[orange]

bbrazeau,

How does davegri's suggestion resolve the issue as per the Title of your post
how to keep or alter message displayed when code is run

Could you tell us the issue and solution in simple terms?

[bbrazeau]

Hi Orange,
Short of it. It doesn't solve that problem directly, but by using his method the query makes a new entry in the "InventoryTransaction" table without the need to open/add record/close the form for doing so. See as davegri mentioned the code shown was not the button code, but the code that happens when the button on the form opens the "InventoryTransaction" form. So now that button just appends a new record to the "InventoryTransaction" table instead of opening, adding, then closing the form. I might add that there are 2 different forms I add records to the "InventoryTransaction' table from. One is OrderDetail (remove), and one is Production (add). See the DB is for an injection molding plant so we both sell and manufacture the products. I probably could have it automatically add and remove from inventory as orders are placed or production runs requested, but sometimes orders might be changed in between when they are placed and shipped so this gives the opportunity to hold off actually changing inventory until the order actually ships. Also sometimes production makes more or less than a run called for so this gives the opportunity to hold off until the run is done and QC ok's the qty to add to inventory.

[orange]

Thanks for the update --glad you have it working.