Moving to new subform record

[TrekBiker]

I have an Orders form that contains an Order Details subform in Datasheet view

One of the fields in the subform is Quantity; this has an AfterUpdate to calculate a discount and Net Total for this line item, and these also appear in the current record. Since these latter fields are calculated I'd like to move down to a new record once Quantity has been added.

I've tried Docmd.GoToRecord,,acNewRecord in the AfterUpdate but it throws up 'error 2105, can't go to specified record'. I've also tried other suggestions but without success.

It would be even better if updating an existing line item row in Order Details moved focus off it, either to the next record down or a new one.

Any guidance much appreciated.

 

[missinglinq]

First question...does the Form open in Read-Only mode, or can you actually add a New Record?

Second question...is the Form's AllowAdditions Property set to Yes?

If it is not Read-Only and the AllowAdditions Property is set to Yes, then

Private Sub Quantity_AfterUpdate()
 DoCmd.GoToRecord , , acNewRec
End Sub

should work. Notice that the code is in the AfterUpdate event of the Control named Quanity, not the AfterUpdate event of the Form. Also, the correct syntax is

acNewRec

not

acNewRecord

as you posted.


Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[TrekBiker]

Thanks for responding, Missinglinq.

You're absolutely right, but it was my typing - I did have acNewRec in the code.

DoCmd.GoToRecord , , acNewRec does move me to a new record if adding new data, but calculations made by other lines of code in the Quantity AfterUpdate don't show.

This is the full code, which runs line by line, calculates the current total volume (firkins) for an order and applies different discounts if the volume exceeds different threshold levels.

Private Sub Quantity_AfterUpdate()

    Dim Disc As Variant
    Dim TF As Variant

   'Calculate the total firkins every time a new order item is entered
    TF = 0
    With Forms!Orders.[Order Details Subform].Form.RecordsetClone
        .MoveFirst
            Do Until .EOF
                TF = TF + !Firkins
        .MoveNext
        Loop
    End With

   'Variable volume levels - set discount % based on total firkins in order
    If TF < Me.Parent.VDLevel1 Then Disc = Me.Parent.DP1
    If TF >= Me.Parent.VDLevel1 And TF < Me.Parent.VDLevel2 Then Disc = Me.Parent.DP2
    If TF >= Me.Parent.VDLevel2 And TF < Me.Parent.VDLevel3 Then Disc = Me.Parent.DP3
    If TF > Me.Parent.VDLevel3 Then Disc = Me.Parent.DP4

   'Calculate discounts based on total firkins
        With Forms!Orders.[Order Details Subform].Form.RecordsetClone
            .MoveFirst
                Do Until .EOF
                .Edit
                    If !Firkins > 0 Then !DiscountPercent = Disc
                Debug.Print !DiscountPercent
                    !Discount = !DiscountPercent * !LineTotal / 100
                    !Discount = Int(!Discount * 100 + 0.5) / 100
                .Update
             .MoveNext
             Loop
        End With

        DoCmd.GoToRecord , , acNewRec
                        
End Sub

A more serious problem is that if Quantity is changed in one of the earlier line items (ie not the bottom one in the datasheet) I get a Write Conflict. Choosing Drop Changes gives the 2105 run-time error. Choosing Save Record does move down to a new one and the additional code runs properly.

Is it a case of somehow saving the new or changed record before the Docmd runs?

 

[MajP]

Untested, but I think I would do it a little simpler.

Private Sub Quantity_AfterUpdate()
    Dim Disc As Variant
    Dim TF As Variant
    dim strSql as string
    dim Par as access.form
    
    set Par = me.parent
   'Calculate the total firkins every time a new order item is entered
    TF = dsum("Firkins","SomeQuery","OrderID = " & Me.parent.orderID)
    
   'Variable volume levels - set discount % based on total firkins in order
    If TF < Par.VDLevel1 Then 
       Disc = par.DP1
    ElseIf TF < Par.VDLevel2 Then 
       Disc = Par.DP2
    elseIf TF < Par.VDLevel3 Then 
       Disc = Par.DP3
    elseIf TF >= Par.VDLevel3 Then 
       Disc = Par.DP4
    end if
   
   'Calculate discounts based on total firkins
    strSql = "Update sometable set DiscountPercent = " & Disc & " where Firkins = 0 and OrderID = " & me.parent.OrderID   
    CurrentDb.execute strSql 
    me.requery
    DoCmd.GoToRecord , , acNewRec
End Sub