AddNew sometimes not working 1

[TrekBiker]

I'm working on a database from another designer. This has a Customer form that summaries Orders in a subform. Highlighting an order and clicking an Orders button opens that order in a separate form that in a subform shows order items, or for new orders allows new ones to be entered. All this works fine and details of existing orders display correctly.

The problem arises with some new orders, initiated by running this code to add a new order for the current customer. OrderID in the Orders table isn't an AutoNumber so the code increments the last value used.

Public Sub AddRecord()
  With Me.RecordsetClone
            .AddNew
           	!CustomerID = Me![CustomerID]
           	!OrderDate = Date
           	!OrderID = DMax("OrderID", "Orders") + 1
            .Update
            .Bookmark = .LastModified
            Me.Bookmark = .Bookmark
            Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")
    End With
End Sub

Sometimes this does not open a new blank record, but stays on the previous one even though I can see that !OrderID has incremented properly.

 

[MajP]

I would think you would have to requery and move to the new record.

Public Sub AddRecord()
   dim newID as long
   newID = DMax("OrderID", "Orders") + 1
   With Me.Recordsetclone
            .AddNew
           	!CustomerID = Me![CustomerID]
           	!OrderDate = Date
               	!OrderID = newID
            .Update
    End With
    me.requery
    me.recordset.FindFirst "OrderID = " & newID
    Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")
End Sub

 

[TrekBiker]

Thanks MajP, tried this and it produced an unexpected result. Instead of opening a new blank record it showed the earliest numbered one for the Customer. Debug.Print showed that newID was indeed the one that should have been showing, but for some reason the wrong one was being displayed. Using the navigation buttons did allow the new one to be displayed but this wouldn't be user friendly.

I suspect something else is causing this so am going back a stage.

 

[MajP]

When you say a new blank record do you actually mean the newly created record with
orderID = DMax("OrderID", "Orders") + 1
CustomerID = Me![CustomerID]
OrderDate = Date
?

So the form should move to the new orderID. It might not be moving because there is a problem in the findfirst statement or the newID was never created. Can you verify it actually finds the newID.

Public Sub AddRecord()
   dim rs as dao.recordset
   dim newID as long
   set rs = me.recorset
   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

 

[TrekBiker]

Yes, what I need is a new order with the details subform blank but these values added

orderID = DMax("OrderID", "Orders") + 1
CustomerID = Me![CustomerID]
OrderDate = Date

I've tried your new procedure and it looks very good so far! I notice that on clicking my Add New Order button it does a blink with the current order still visible then moves to a new one ready for order details to be added. Stepping through the code it's the line

Rs.FindFirst "OrderID = " & newID

that is doing this. I'll report back if still running into difficulties but many thanks so far.

 

[MajP]

The blink is because you create a new record and then requery. So the requery will take you to the first record and then move to the last.