Post as Quote Or as Invoice Criteria

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Post as Quote Or as Invoice Criteria

Post by burrina »

Trying to incorporate OnClickEvent into one single action. Either Post as Quote or as Invoice but also check to see if it has already been Posted as a Quote or Invoice.
If user wants to change back from a Quote or Invoice then ask for confirmation.

Here is what I have so far using a OnClickEvent for a Label. Call EnableCtl is in a Module.ModDate and sets the Label to "This is a Quote" or "Posted as a Invoice"
Call TimeCardCounter is a module named ModTimeCardCounter that advances the Invoice number by one.

Code: Select all

If Not IsNull(CustomerID) And Not IsNull(TimeCounter) Then
    Call MsgBox("Order Has Already Been Posted As a Invoice!", vbExclamation, Application.Name)
        Exit Sub
            End If

If Not IsNull(CustomerID) And IsNull(TimeCounter) Then  ' Post as Invoice.
    Call TimeCardCounter
TimeCounter = Format(DLookup("[NextAvailableCounter]", "TTimeCardCounter"), "######")
    End If
DoCmd.RunCommand acCmdSave
    Call EnableCtl
Me.Refresh


Dim strCriteria As String
      strCriteria = "[OrderID] = " & Me![txtOrderID]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Orders SET Orders.OrderType = -1"
                 
DoCmd.SetWarnings True

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Post as Quote Or as Invoice Criteria

Post by HansV »

What exactly is your question?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Post as Quote Or as Invoice Criteria

Post by burrina »

I am trying to incorporate the code into ONE event if possible. If it has not been posted as a Invoice, then ask and proceed, else inform it has already been posted as a Invoice.
Same for a Quote.
Basically ask will this reference a Quote or a Invoice, then which ever one is chosen, proceed and post or check if it has already been posted. Allow changing from Invoice back to Quote and of course ask for confirmation. i.e. Cancel Order.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Post as Quote Or as Invoice Criteria

Post by HansV »

Perhaps this?

Code: Select all

Private Sub Label0_Click()
    Dim strCriteria As String
    strCriteria = "[OrderID] = " & Me![txtOrderID]

    If Not IsNull(CustomerID) And Not IsNull(TimeCounter) Then
        If MsgBox("Order Has Already Been Posted As a Invoice!" & vbCrLf & _
                "Do you want to change it to a quote?", vbQuestion + vbYesNo, _
                Application.Name) = vbNo Then
            Exit Sub
        Else
            ' Code to change invoice to quote
            TimeCounter = Null
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE Orders SET Orders.OrderType = 0 WHERE " & strCriteria
            DoCmd.SetWarnings True
        End If
    ElseIf Not IsNull(CustomerID) And IsNull(TimeCounter) Then  ' Post as Invoice.
        If MsgBox("Do you want to change this quote to an invoice?", _
                vbQuestion + vbYesNo) = vbNo Then
            Exit Sub
        Else
            Call TimeCardCounter
            TimeCounter = Format(DLookup("[NextAvailableCounter]", _
                "TTimeCardCounter"), "######")
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE Orders SET Orders.OrderType = -1 WHERE " & strCriteria
            DoCmd.SetWarnings True
        End If
    End If

    DoCmd.RunCommand acCmdSave
    Call EnableCtl
    Me.Refresh
End Sub
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Post as Quote Or as Invoice Criteria

Post by burrina »

I get a Write Conflict Notice. Save Changes, Copy To Clipboard, Drop Changes. I am the ONLY one the db.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Post as Quote Or as Invoice Criteria

Post by HansV »

Does the Orders table happen to be (part of) the record source of the form?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Post as Quote Or as Invoice Criteria

Post by burrina »

Thanks! I added Me.Dirty = False and it worked. YES, the Orders table is the Record Source for the form.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Post as Quote Or as Invoice Criteria

Post by HansV »

If so, can't you simply replace

Code: Select all

            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE Orders SET Orders.OrderType = 0 WHERE " & strCriteria
            DoCmd.SetWarnings True
with

Code: Select all

            Me.OrderType = 0
and similar for the one with -1?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Post as Quote Or as Invoice Criteria

Post by burrina »

In the Orders table the validation rule is either 1 or -1 for simplicity sake. That would work though.

Many Thanks for the help!