Update from field after changes in subform 1

[TrekBiker]

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?

 

[TrekBiker]

Sorry, 'Update form field...'

 

[dhookom]

How about

Me.Parent.TotalFirkinsInPeriod.Requery

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

 

[TrekBiker]

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.

 

[dhookom]

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

 

[TrekBiker]

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.

 

[TrekBiker]

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.

 

[dhookom]

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:

?DLookUp("TotalInPeriod","qryFirkinsTotalInPeriod")

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

 

[TrekBiker]

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.

 

[dhookom]

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

 

[TrekBiker]

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.

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

 

[dhookom]

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

 

[TrekBiker]

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.

 

[dhookom]

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

 

[TrekBiker]

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.