Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 Received 300
    Qty on backorder = ([QtyOrdered]
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backorders Calculation Revisited (A2K)

    Let's say the command button is named cmdAdd.

    Private Sub cmdAdd_Click()
    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. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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,
    Cheers,
    Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backorders Calculation Revisited (A2K)

    Hans,

    Excellent points to ponder. I appreciate your following up.

    Cheers,
    Cheers,
    Andy

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers,
    Andy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.<!t>[2Date]<!/t> = Me.<!t>[2Date]<!/t> + Me.<!t>[Received]<!/t>
    Me.Recalc
    Me.Refresh
    Me.Received = Null
    RunCommand acCmdRecordsGoToNext
    Me.Received.SetFocus
    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. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    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
  •