INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Moving to new subform record

Moving to new subform record

(OP)
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.

RE: Moving to new subform record

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

CODE -->

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

RE: Moving to new subform record

(OP)
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.

CODE -->

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?

RE: Moving to new subform record

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

CODE -->

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close