Change Status on Order

**Stephanie53** · 05-02-2013, 01:07 PM

Ok now we are on to the next part of the phase:

When our sales dept click on New Quote/Order we want the status to first say Quote
After customer has accepted the Quote we want it to say Order (the Order creates the invoice)
After completed in Prodcution we want it to say QualityControl
When completed in QualityControl we want to have it say Shipped
When its shipped Accounting can go in and complete the order after they bill the customer so that is when it was say Closed

So I put in Order Details Table and Order Status table and there is a query named Order Status Extended and they are in relation here (i.e. Number/Number)

Now when I open a New Order it goes into Quote Status - - and it stays in that status until Closed It will not change

Here is what I found out in the code:

Code:

Sub SetFormState(Optional fChangeFocus As Boolean = True)
    If fChangeFocus Then Me.Customer_ID.SetFocus
    
    Dim Status As CustomerOrderStatusEnum
        
    Status = Nz(Me![Status ID], Quote_CustomerOrder)
    
    TabCtlOrderData.Enabled = Not IsNull(Me![Customer ID])
    
    Me.cmdCreateInvoice.Enabled = (Status = Quote_CustomerOrder)
    Me.cmdShipOrder.Enabled = (Status = Quote_CustomerOrder) Or (Status = Invoiced_CustomerOrder)
    Me.cmdDeleteOrder.Enabled = (Status = Quote_CustomerOrder) Or (Status = Invoiced_CustomerOrder)
    Me.cmdCompleteOrder.Enabled = (Status <> Quote_CustomerOrder) Or (Status <> Closed_CustomerOrder)
    
    Me.[Order Details_Page].Enabled = (Status = Quote_CustomerOrder)
    Me.[Shipping Information_Page].Enabled = (Status = Shipped_CustomerOrder)
    Me.[Payment Information_Page].Enabled = (Status <> Closed_CustomerOrder)
    
    Me.Customer_ID.Locked = (Status <> Quote_CustomerOrder)
    Me.Employee_ID.Locked = (Status <> Quote_CustomerOrder)
    
    Me.sbfOrderDetails.Locked = (Status <> Quote_CustomerOrder)
End Sub

So then I tried to change it to this:

Code:

Sub SetFormState(Optional fChangeFocus As Boolean = True)
    If fChangeFocus Then Me.Customer_ID.SetFocus
    
    Dim Status As CustomerOrderStatusEnum
        
    Status = Nz(Me![Status ID], Quote_CustomerOrder)
    
    TabCtlOrderData.Enabled = Not IsNull(Me![Customer ID])
    
    Me.cmdCreateInvoice.Enabled = (Status = Order_CustomerOrder)
    Me.cmdShipOrder.Enabled = (Status = QualityControl_CustomerOrder) Or (Status = Shipped_CustomerOrder)
    Me.cmdDeleteOrder.Enabled = (Status = Quote_CustomerOrder) Or (Status = Invoiced_CustomerOrder)
    Me.cmdCompleteOrder.Enabled = (Status <> Invoiced_CustomerOrder) Or (Status <> Closed_CustomerOrder)
    
    Me.[Order Details_Page].Enabled = (Status = Quote_CustomerOrder)
    Me.[Shipping Information_Page].Enabled = (Status = Shipped_CustomerOrder)
    Me.[Payment Information_Page].Enabled = (Status <> Closed_CustomerOrder)
    
    Me.Customer_ID.Locked = (Status <> Quote_CustomerOrder)
    Me.Employee_ID.Locked = (Status <> Quote_CustomerOrder)
    
    Me.sbfOrderDetails.Locked = (Status <> Quote_CustomerOrder)
End Sub

And now this comes up as Debugging:

Code:

 
Sub SetFormState(Optional fChangeFocus As Boolean = True) <----this is in Yellow 
    If fChangeFocus Then Me.Customer_ID.SetFocus
    
    Dim Status As CustomerOrderStatusEnum
        
    Status = Nz(Me![Status ID], Quote_CustomerOrder)
    
    TabCtlOrderData.Enabled = Not IsNull(Me![Customer ID])
    
    Me.cmdCreateInvoice.Enabled = (Status = Order_CustomerOrder)
    Me.cmdShipOrder.Enabled = (Status = QualityControl_CustomerOrder) Or (Status = Shipped_CustomerOrder)
    Me.cmdDeleteOrder.Enabled = (Status = Quote_CustomerOrder) Or (Status = Invoiced_CustomerOrder)
    Me.cmdCompleteOrder.Enabled = (Status <> Invoiced_CustomerOrder) Or (Status <> Closed_CustomerOrder)
    
    Me.[Order Details_Page].Enabled = (Status = Quote_CustomerOrder)
    Me.[Shipping Information_Page].Enabled = (Status = Shipped_CustomerOrder)
    Me.[Payment Information_Page].Enabled = (Status <> Closed_CustomerOrder)
    
    Me.Customer_ID.Locked = (Status <> Quote_CustomerOrder)
    Me.Employee_ID.Locked = (Status <> Quote_CustomerOrder)
    
    Me.sbfOrderDetails.Locked = (Status <> Quote_CustomerOrder)
End Sub

**June7** · 05-02-2013, 01:30 PM

What is the error message?

**Stephanie53** · 05-02-2013, 02:19 PM

its a debugging error (see bottom code) that comes up when i changed all the Quote to what it suppose to be when clicking on the cmd's and wont let me go further and it also show where it says Order_CustomerOrder in blue. I am not sure what I did wrong per the way we want to have it done with this code above

**ssanfu** · 05-02-2013, 02:42 PM

It looks like you have a control on the form named "Quote_CustomerOrder".
Do you also have a control named "Order_CustomerOrder"?

**Stephanie53** · 05-02-2013, 03:20 PM

Database56.zip

I am trying everywhere to see it in this form and I am not seeing it other then in this code. I have attached the DB

TIA :-)

**ssanfu** · 05-02-2013, 11:38 PM

In the standard module, you have an enumeration:

Code:

Public Enum CustomerOrderStatusEnum
    Quote_CustomerOrder = 0
    Invoiced_CustomerOrder = 1
    Shipped_CustomerOrder = 2
    Closed_CustomerOrder = 3
End Enum

You modified these two lines:

Code:

 Me.cmdCreateInvoice.Enabled = (Status = Order_CustomerOrder)     
 Me.cmdShipOrder.Enabled = (Status = QualityControl_CustomerOrder) Or (Status = Shipped_CustomerOrder)

The two terms in RED are NOT in the enum statement..... which is causing the error.

However, you do have something similar in the table "Order Status" (I really hate spaces in object names

)

Code:

Status ID
Status Name

0
Quote

1
Order

2
QualityControl

3
Shipped

4
Closed

But the table doesn't have "Invoiced".

The table "Status ID" values do not match some of the the enumerated values.

**Stephanie53** · 05-06-2013, 08:15 AM

Ok I have made the changes accordingly to what you said to the following:

Code:

Public Enum CustomerOrderStatusEnum
    Quote_CustomerOrder = 0
    Order_CustomerOrder = 1
    QualityControl_CustomerOrder = 2
    Invoiced_CustomerOrder = 3
    Shipped_CustomerOrder = 4
    Closed_CustomerOrder = 5
End Enum

Now my Code for the Order Form reads the following with no more errors:

Code:

Sub SetFormState(Optional fChangeFocus As Boolean = True)
    If fChangeFocus Then Me.Customer_ID.SetFocus
    
    Dim Status As CustomerOrderStatusEnum
        
    Status = Nz(Me![Status ID], Quote_CustomerOrder)
    
    TabCtlOrderData.Enabled = Not IsNull(Me![Customer ID])
    
    Me.cmdCreateInvoice.Enabled = (Status = Order_CustomerOrder)
    Me.cmdShipOrder.Enabled = (Status = QualityControl_CustomerOrder) Or (Status = Shipped_CustomerOrder)
    Me.cmdDeleteOrder.Enabled = (Status = Quote_CustomerOrder) Or (Status = Invoiced_CustomerOrder)
    Me.cmdCompleteOrder.Enabled = (Status <> Invoiced_CustomerOrder) Or (Status <> Closed_CustomerOrder)
    
    Me.[Order Details_Page].Enabled = (Status = Quote_CustomerOrder) Or (Status = Order_CustomerOrder)
    Me.[Shipping Information_Page].Enabled = (Status = Shipped_CustomerOrder)
    Me.[Payment Information_Page].Enabled = (Status <> Closed_CustomerOrder)
    
    Me.Customer_ID.Locked = (Status <> Quote_CustomerOrder)
    Me.Employee_ID.Locked = (Status <> Quote_CustomerOrder)
    
    Me.sbfOrderDetails.Locked = (Status <> Quote_CustomerOrder)
End Sub

But now the issue I have when I made those changes is that the links at the top to have the events run for "Create Quote/Invoice" and "Ship Order" are not able to click on at all. Can you show me where it went wrong in making those changes that could effect those links?

Here is the rest of the Code:

Code:

Private Sub cmdCreateInvoice_Click()
    Dim OrderID As Long
    Dim InvoiceID As Long
    
    OrderID = Nz(Me![Order ID], 0)
    
    ' Gracefully exit if invoice already created
    If CustomerOrders.IsInvoiced(OrderID) Then
        If MsgBoxYesNo(OrderAlreadyInvoiced) Then
            CustomerOrders.PrintInvoice OrderID
        End If
    ElseIf ValidateOrder(Invoiced_CustomerOrder) Then
         
        ' Create Invoice Record
        If CustomerOrders.CreateInvoice(OrderID, 0, InvoiceID) Then
            
            ' Mark all Order Items Invoiced
            ' Need to change Inventory Status to SOLD from HOLD
            Dim rsw As New RecordsetWrapper
            With rsw.GetRecordsetClone(Me.sbfOrderDetails.Form.Recordset)
                While Not .EOF
                    If Not IsNull(![Inventory ID]) And ![Status ID] = OnHold_OrderItemStatus Then
                        rsw.Edit
                        ![Status ID] = Invoiced_OrderItemStatus
                        rsw.Update
                        Inventory.HoldToSold ![Inventory ID]
                    End If
                    rsw.MoveNext
                Wend
            End With
            
            ' Print the Invoice
            CustomerOrders.PrintInvoice OrderID
             
            SetFormState
        End If
    End If
End Sub

Private Sub cmdShipOrder_Click()
    If Not CustomerOrders.IsInvoiced(Nz(Me![Order ID], 0)) Then
        MsgBoxOKOnly CannotShipNotInvoiced
    ElseIf Not ValidateShipping() Then
        MsgBoxOKOnly ShippingNotComplete
    Else
        Me![Status ID] = Shipped_CustomerOrder
        
        If IsNull(Me![Shipped Date]) Then
            Me![Shipped Date] = Date
        End If
        eh.TryToSaveRecord
        SetFormState
    End If
End Sub

TIA

Stephanie

**ssanfu** · 05-10-2013, 05:32 PM

I am traveling right now and don't have access to A2010.

1) You changed the enumeration... did you change the table "Order Status" records to match the enumeration?
2) Have you selected a record where the "ship" button should be enabled and set a breakpoint/stepped through the code to see why the button(s) are not being enabled?

The subroutine "SetFormState" looks like it is setting the button enabled/disabled state.

**Stephanie53** · 05-13-2013, 10:02 AM

I have corrected the Order Status and now on to the next button ...since doing this request more buttons have been created to suit the needs of management here (uuuugggghhhhhh)

I change the following:

Code:

Public Enum CustomerOrderStatusEnum
    Quote_CustomerOrder = 0
    Order_CustomerOrder = 1
    Peachtree__CustomerOrder = 2
    Production_CustomerOrder = 3
    QualityControl_CustomerOrder = 4
    Shipping_CustomerOrder = 5
    Accounting_CustomerOrder = 6
    Completed_CustomerOrder = 7
End Enum

I also changed it on the OrderStatus to the following: