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

Update from field after changes in subform

Update from field after changes in subform

(OP)
I have an Orders form with subform to show existing purchases and allow new ones to be added.

An unbound field TotalFirkinsInPeriod on the main form shows the total volumes purchased over a defined period up to the current date, using source

=DLookUp("TotalInPeriod","qryFirkinsTotalInPeriod")

When adding new items to the subform I want this field to update after each new entry. It only updates on closing and reopening the form.

I tried including in the subform's AfterUpdate event

- Me.Parent.TotalFirkinsInPeriod.Recalc, which gave Run-time error 438, 'Object doesn't support this property or method'

- Forms!Orders.Requery, which did nothing

Suggestions?

RE: Update from field after changes in subform

(OP)
Sorry, 'Update form field...'

RE: Update from field after changes in subform

How about

CODE --> VBA

Me.Parent.TotalFirkinsInPeriod.Requery 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update from field after changes in subform

(OP)

Thanks Duane, looks like you're back home.

Tried this but it didn't update the total. I also tried adding 'If Me.Dirty Then Me.Dirty = False' before the requery to force a save but this also didn't make the total update.

RE: Update from field after changes in subform

The code I suggested worked for me. What is the SQL view of qryFirkinsTotalInPeriod?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016


Yes, I have been back home for a couple weeks. Biking isn't nearly as scenic sad

RE: Update from field after changes in subform

(OP)
I've looked closer at the query that feeds the TotalFirkinsInPeriod field and the problem seems to be that using the AfterUpdate event in the subform doesn't cause it to update. Completely closing the form obviously does.

Before closing, even moving the focus out to another control on the form doesn't do an update.

I really want the total to be updating as each new order item is added to the first subform.

RE: Update from field after changes in subform

(OP)
This is the SQL but it may not be too clear what's going on as this is at the end of a string of queries.

SELECT qryFirkinsByCustomerWithinDateRange.CustomerID, Sum(qryFirkinsByCustomerWithinDateRange.TotalFirkins) AS TotalInPeriod
FROM qryFirkinsByCustomerWithinDateRange
GROUP BY qryFirkinsByCustomerWithinDateRange.CustomerID
HAVING (((qryFirkinsByCustomerWithinDateRange.CustomerID)=[forms]![Orders].[CustomerID]));

The previous query, qryFirkinsByCustomerInDateRange has SQL

SELECT qryFirkinsTotalPerOrder.CustomerID, qryFirkinsTotalPerOrder.CompanyName, qryFirkinsTotalPerOrder.Town, qryFirkinsTotalPerOrder.SumOfFirkins AS TotalFirkins, qryFirkinsTotalPerOrder.ShipDate
FROM qryFirkinsTotalPerOrder
WHERE (((qryFirkinsTotalPerOrder.ShipDate) Between [Forms]![Orders].[StartDate] And Date()));

The process involves calculating how many firkins (beer volume) the current customer has bought over a defined number of weeks including items in the current order, for which items are added via the subform. Achieving a target level qualifies them for a discount, and adding in the current order may be important if it allows the target to be reached. My TotalFirkinInPeriod field calculates correctly for orders up as far as the current one but isn't then updating for new items.

RE: Update from field after changes in subform

Have you confirmed the code is being run by adding a msgbox() or debug.print line in your code?

Have you tried open the debug window and entering:

CODE --> debug

?DLookUp("TotalInPeriod","qryFirkinsTotalInPeriod") 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update from field after changes in subform

(OP)
Tried this in Immediate window.

First added a new order with a single order item that should have increased the total by 1.

?DLookUp("TotalInPeriod","qryFirkinsTotalInPeriod") gave the original total (as displayed on the form) without the extra 1.

Closing order form and reopening to the same order now showed the correct total. So still needs something to force an update.

RE: Update from field after changes in subform

Did you stay on the same record or did you move to another record in the subform to make sure it is saved?

Are your tables in Access?

Can you share your actual code including the Sub/End Sub?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update from field after changes in subform

(OP)

Yes, tables are in Access.

Just to be clear, I'm creating a new order and add purchases in the subform, which is in datasheet view. If I add one record or several the Total field on the main form doesn't update until I close the entire Orders form and reopen it.

This is the subform's AfterUpdate. Each time a new item is added it calculates the firkins volumes for the new order and assigns a volume discount. The part I'm adding calculates an additional loyalty discount based on order history.

CODE -->

Private Sub Form_AfterUpdate()

    Dim Disc As Variant
    Dim TF As Variant
       
   'Calculate the total added firkins every time a new order item is entered
    TF = 0
    With Me.RecordsetClone
        .MoveFirst
            Do Until .EOF
                TF = TF + !Firkins
        .MoveNext
        Loop
    End With
    
   'Variable volume levels - set discount % based on total firkin equivalents 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
    
    With Me.RecordsetClone
        .MoveFirst
            Do Until .EOF
        .Edit
            If !Firkins > 0 Then !DiscountPercent = Disc
            !Discount = !DiscountPercent * !UnitPrice / 100
            !Discount = Int(!Discount * 100 + 0.5) / 100
            !NetLineTotal = !Quantity * (!UnitPrice - !Discount)
            !DiscountAssigned = -1
        .Update
        .MoveNext
        Loop
    End With
    
   'Trying things to get the total firkins to update
    If Me.Dirty Then Me.Dirty = False
'    Forms!Orders.Recalc
'    Me.Parent.FirkinsTotalInPeriod.Requery
                   
End Sub 

RE: Update from field after changes in subform

Have you set a break point in your code to make sure it is doing what you think? From what I see, there is nothing that updates the Firkins field. Are you sure you uncommented the last line?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update from field after changes in subform

(OP)
I've had several attempts at getting this field to update, hence the comment. Incidentally the field is called TotalFirkinsInPeriod so the code I sent was an earlier mistake that I'd corrected. I uncommented it to check again and no change, so not updating the total.

If I create a new order with some order items, leave the form open and run the total's source query (qryFirkinsTotalInPeriod, SQL above) directly from the Navigation Pane it gives me the same total as displayed, ie with the total not having updated to take account of the firkins in the new order.

I just tried requerying the Orders form from a command button but the total still doesn't update. I thought this was supposed to be equivalent to closing and reopening the form but not so for the way it's configured.

RE: Update from field after changes in subform

Again, have you set a breakpoint in your code to step through it or using debug.print. I am concerned that you might be creating an endless loop with updating the record in the afterupdate event.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update from field after changes in subform

(OP)
Thanks for all your time, Duane.

I put a break point in the AfterUpdate event I attached above and it stepped through to the end as intended.

I've just had a horrible thought. One of the criteria for including firkins in the total displayed is that the order date falls within the specified time period. But if I don't add a date before entering order items it's not going to pick them up. The last step in creating the order is a check that the date had been entered before it lets you out, hence the fact that saving and reopening shows the correct updated total.

I'm so sorry to have taken so much of your time but your confirmation that I had the basics right has led me to a happy resolution.

Many thanks and well deserved star.

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