The command or action Save Record isn't available now 1

[TrekBiker]

A client's database has a Customer form with a subform in datasheet view showing a summary of all of the customer's orders placed so far. After selecting an order, clicking the main form's Order button opens an Order Details form showing the full details of the order, with subforms for any purchases and credits. An Add New Order button clears this form and allows a new one to be built up.

For a new customer with no existing orders the Order Details form opens with no OrderID or Customer Name showing. To start populating this the same Add New Order button needs to be clicked. This inserts a new OrderID and the customer name.

But people forget, and when clicking in a subform to start adding order items the Customer Name and OrderID = 0 appear, with the error message 'The command or action Save Record isn't available now'.

Depending what they do next they may get a blank order saved to the customer's record. T want to suppress this and instead warn to click Add New Order. It's not clear what triggers the error message other than the mouse click in a subform.

 

[MajP]

For a new customer with no existing orders the Order Details form opens with no OrderID or Customer Name showing. To start populating this the same Add New Order button needs to be clicked. This inserts a new OrderID and the customer name. But people forget, and when clicking in a subform to start adding order items the Customer Name and OrderID = 0 appear, with the error message 'The command or action Save Record isn't available now'.

Why does the user need to hit a button? Normally this is done with a linked subform linked by customerID and you should be able to edit directly in the subform without need to hit the button. Or make the subform properties to allow additions to false then should be no new records visible in the subform.
How is the subform linked to the main form? What is the code in the add new button? What is the SQL of subform recordsource.

 

[TrekBiker]

The link field for the order items subform is OrderID and its source SQL is

SELECT DISTINCTROW [Order Details].*, [Cask Types].perFirkin, [Quantity]*[perFirkin] AS Firkins, [Quantity]*Round(([Order Details].[UnitPrice]-[Discount]),2) AS NetLineTotal
FROM ([Cask Types] INNER JOIN Products ON [Cask Types].CaskType = Products.CaskType) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID;

The Add New Order button source is

On Error GoTo Err_cmdNewOrder_Click

    If Me![Order Details Subform].Form.RecordsetClone.RecordCount > 0 Then
        AddRecord
        Me![isNewRecord] = "true"
        
       'Allow addition of new purchase items
        Me.Order_Details_Subform.Form.AllowAdditions = True
        Me.Order_Details_Subform.Form.AllowDeletions = True
        Me.Order_Details_Subform.Form.AllowEdits = True
        
     Else
        If OrderID <> 0 Then
            MsgBox "Please add a product to the current order before creating a new one."
        Else
            AddRecord
            Me![isNewRecord] = "true"
            Me.Requery
         End If
    End If

'Set focus to ProductID
    MsgBox "Add a Delivery Date and Method before entering order items, to ensure Loyalty Discount is updated correctly"
    Me.ShipDate.SetFocus

Exit_cmdNewOrder_Click:
    Exit Sub

Err_cmdNewOrder_Click:
    If Err.Number = 3421 Then Exit Sub
    MsgBox Err.Description
    Resume Exit_cmdNewOrder_Click

This is the AddRecord code

Public Sub AddRecord()

   Dim rs As dao.Recordset
   Dim newID As Long
   Set rs = Me.Recordset
   
   newID = DMax("OrderID", "Orders") + 1
   With rs
            .AddNew
            !CustomerID = Me![CustomerID]
            !OrderDate = Date
            !OrderID = newID
            .Update
    End With
    Me.Requery
    
    rs.FindFirst "OrderID = " & newID
    
    If rs.NoMatch Then
      MsgBox "Could not find OrderID = " & newID
    End If
    
    Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")

End Sub

 

[MajP]

so what codes sets allow additions back to false?

 

[TrekBiker]

The Orders form's Load event sets this

Private Sub Form_Load()
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletions = False
End Sub

When the Orders form opens to a new record for a new customer it has blank fields for Customer Name and OrderID.
When clicking in the order details subform the Company Name appears and the OrderID is set to 0. At the same time the error message appears about SaveRecord not being available.
On clicking OK the subform remains closed for Edits, Additions and Deletions. Clicking Add New Order allows additions etc but the OrderID remains 0 and order items can be added. This creates a spurious order for the customer.

 

[TrekBiker]

Should also have said thank you very much so far for responding, MajP.

 

[MajP]

So my guess for what is happening is once you add a new record the very first time it puts the subform into an allow additions state = true.

       'Allow addition of new purchase items
        Me.Order_Details_Subform.Form.AllowAdditions = True
        Me.Order_Details_Subform.Form.AllowDeletions = True
        Me.Order_Details_Subform.Form.AllowEdits = True

I do not see any code after that to set allow additions back to false for the subform after they enter a new record. I would have that code in the the subforms on current or after update. Since the allow additions is left in "true" for the subform the user can now enter the subform and start typing, but there is no event to create the orderID Foreign key. I assume you have a validation rule the enforces referential integrity in the order details so there is no way to save the record without an order ID. Another solution would be leaving it in allow additions and once they enter data ensure that a order ID key is generated.

 

[MajP]

Another solution would be to do this without any code. The subform control is linked to the mainform by OrderID and leave allow additions to true. That automatically creates the foreign key if data is entered.

 

[TrekBiker]

Thanks again. Just for clarity, the sequence of events in coming from the previous Orders by Customers form is to open the Orders form whose source tables have referential integrity set up. These are in place

Private Sub Form_Open(Cancel As Integer)
    If CurrentProject.AllForms("Orders by Customer").IsLoaded Then
        Me![CompanyName] = Forms![Orders by Customer]![CustomerID]
    End If
End Sub

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
   'Opens OrderID if an existing order or sets as a new one
        If CurrentProject.AllForms("Orders by Customer").IsLoaded Then
            If Forms![Orders by Customer].AddNew = "yes" Then
                AddRecord
                Me![isNewRecord] = "true"
                Forms![Orders by Customer].AddNew = ""
            Else
                Me![isNewRecord] = "false"
                If Forms![Orders by Customer]![Orders by Customer Subform].Form.RecordsetClone.RecordCount > 0 Then
                    DoCmd.GoToControl "OrderID"
                    DoCmd.FindRecord Forms![Orders by Customer]![Orders by Customer Subform].Form![OrderID]
                End If
            End If
        Else
        End If
Exit_Form_Activate:
    Exit Sub
Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
End Sub

In the instance of a new customer's first order, which opens up blank and with the mysterious IsNewRecord field showing 'False', I can find no code that runs when just clicking in the order details subform. This subform has the Load procedure I included earlier but it doesn't fire when opening this blank form.

I don't want to use Current to allow edits etc because for existing orders I want the subform to be protected against changes because existing orders have already been processed. Hence the Add New button to do this. Having created a new order the user is forced to save and exit back to the Orders by Customer form, so there's no moving on to a new page of the Orders form directly.

I'd really like to abandon this inherited set of forms and start again!

 

[MajP]

I do not think any of that changes what I think is occurring. I think when the user clicks the button to allow a new order the subform is set to allow additions, but it is never turned back to disallow additions. When it is in that state a user can start creating a new record but there is no code to create a new order ID foreign key. Having referential integrity set is the "problem" not the solution. You either need code and an event to create the order ID foreign key or you need to restrict the user for entering a record until the correct conditions are set.

 

[MajP]

However, I agree that the code is overly complicated and you could redesign far simpler set of forms.

 

[TrekBiker]

Much appreciate your help, MajP, thanks. I'm going to freeze further work on this and rely on using a message to warn the user to be sure to click Add new Order. The present setup works ok if this is done.

I'll set about reconstructing the two forms in a simpler and more conventional way so as to avoid all of the ramifications.