# Thread: Backorders Calculation Revisited (A2K)

1. ## Backorders Calculation Revisited (A2K)

Edited by HansV to provide link to post - it's extremely simple: use the mouse to select the post number in the header of the post, including the square brackets. Press Ctrl+C to copy, then in your new post, press Ctrl+V to paste.

I'm going to use the fact that this is a holiday weekend as an excuse for the blanking out of my mind.

I have somehow lost the original code for a posting I did last October ( <post#=609401>post 609401</post#> ), so I'll try to repeat my sins.

BackOrder Scenario:

Qty Ordered: 1000
Qty on backorder = ([QtyOrdered]

2. ## Re: Backorders Calculation Revisited (A2K)

Let's say the command button is named cmdAdd.

Me.txtB = Me.txtB + Me.txtD
Me.txtC = Me.txtA - Me.txtB
End Sub

Note: if txtC is a calculated control with control source =[txtA]-[txtB] or similar, you don't need to update it in code, it will be done automatically. So in that case, omit the line

Me.txtC = Me.txtA - Me.txtB

3. ## Re: Backorders Calculation Revisited (A2K)

Hans,

Your expertise is only outdone by your patience with old programmers that lose stuff.
Thanks again and have a good weekend,

Cheers,

4. ## Re: Backorders Calculation Revisited (A2K)

Hans,

Just a further note of interest, in order to save the user all of the trouble of clicking on the command button, I coded the following:

Private Sub txtD_Exit(Cancel As Integer)
Me.txtB = Me.txtB + Me.txtD
Me.Recalc
Me.txtD = Null
End Sub

So after they've entered the amount received into txtD, when they Exit, the new BackOrder is recalculated and txtD is nullified so that it's blank for the next time an entry is made. If I didn't do that, the amount would have remained in the field and they could become, no disrespect meant, Konfused!

Cheers,

5. ## Re: Backorders Calculation Revisited (A2K)

Setting txtD to Null is an excellent idea.

But I don't like the idea of using the On Exit event of txtD to update txtB (and hence txtC). If the user enters a value in txtD, then thinks better of it and clicks outside the text box, the damage will have been done. that's why I prefer to use a command button - clicking it is a conscious decision.

You should also check whether txtD has been filled in before updating txtB.

6. ## Re: Backorders Calculation Revisited (A2K)

Hans,

Excellent points to ponder. I appreciate your following up.

Cheers,

7. ## Re: Backorders Calculation Revisited (A2K)

As virtually always, you were correct in using the command button. I now have the following code which works perfectly with respect to all my calculations. I actually put the command button in the header with the title

8. ## Re: Backorders Calculation Revisited (A2K)

I'd create an error handler to trap the error - it has number 2046:

Private Sub butRecalBackOrder_Click()
On Error GoTo ErrHandler

Me.Recalc
Me.Refresh
RunCommand acCmdRecordsGoToNext
Exit Sub

ErrHandler:
Select Case Err
Case 2046
' Cannot go to next record, just continue
Resume Next
Case Else
' Report other errors
MsgBox Err.Description, vbExclamation
End Select
End Sub

9. ## Re: Backorders Calculation Revisited (A2K)

Hans,

Eight minutes! I can't even type that fast. Thanks kindly. I'll go to your solution immediately,

Cheers,

Andy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•