Batch Update of Subform Records

[dmarsh16946]

Have main form storing contact details, with subform storing payments. I'm coding the payments as they are added but want also to do the same to several records as a batch.

In more detail :-

Payments have to be flagged as 01 if the first after a set date, then 17 thereafter. I'm using an after update procedure to do this, a slight complication being the set date may change, leading to a second or subsequent 01 payments.

The subform AfterUpdate event includes this :

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
.Edit
If !PaymentType = "Direct Debit" Then
If !DateReceived = DLookup("DateReceived", "qryFirstAfterAgreementDate") Then
!TransactionCode = "01"
Else
If !DateReceived < DLookup("DateReceived", "qryFirstAfterAgreementDate") Then
GoTo Jump
ElseIf !DateReceived > DLookup("DateReceived", "qryFirstAfterAgreementDate") Then
!TransactionCode = "17"
End If
End If

End If
.Update
Jump:
.MoveNext
Loop
End With

I'd like to be able to do the same check for a batch of records. This is because some people pay a fixed amount monthly and I'm using an append query to identify them and apply the payments to the data source for the subform.

Trouble is this doesn't trigger the 01/17 allocation.

Suggestions gratefully received.

 

[lameid]

If you want to do this for a group of recordsk, you should use an update query. At first glance, I was thinkging you would want to nest IIF functions to do some calculations but it looks like everything you have is criteria. It also looks like for that criteria you are using queries with the criteria embedded in them. Chances are you can modify these queries (or duplicate them) and use them in IN criteria to get the reult you want. It is hard to say with out seeing the SQL behind them.

 

[PHV]

What is the SQL code of qryFirstAfterAgreementDate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmarsh16946]

Answering PHV's question :

Before the SQL, some clarification just in case.

The form name is frmSubsAndDonations.

The query looks at the data source for the payments subform using these criteria and taking the Top record, ie first after the Agreement Date.

Dates : >=[forms]![frmSubsAndDonations].[DDAgreementDate]
PaymentType : Direct Debit
ContactID : [forms]![frmSubsAndDonations].[ContactID]

*******

SELECT TOP 1 qryInPayments.DateReceived, qryInPayments.ContactID, qryInPayments.PaymentType
FROM qryInPayments
WHERE (((qryInPayments.DateReceived)>=[forms]![frmSubsAndDonations].[DDAgreementDate]) AND ((qryInPayments.ContactID)=[forms]![frmSubsAndDonations].[ContactID]) AND ((qryInPayments.PaymentType)="Direct Debit"))
ORDER BY qryInPayments.DateReceived;

 

[PHV]

Trouble is this doesn't trigger the 01/17 allocation
WHAT isn't trigger ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I think I'm confused somewhere along the line...

What are the names of your main and sub forms? I think you are wanting to update the recordsource of the subform but the query you provide looks like it is looking at the main form for parameters based on the syntax used. Can you also provide the SQL for the form recordsource you want to update?

 

[dmarsh16946]

Sorry guys for not making it clear, here's more detail.

Main form : frmSubsAndDonations
This is based on qryConatcts and has fields for
- ContactID
- DDAgreementDate (DD being Direct Debit)


Subform : sfmSubscriptions
This is based on qryInpayments and has fields for
- ContactID
- PaymentDate
- Payment Type (Donation, Subscription)
- TransactionCode (01 or 17)

If entering payments manually the code decides if a payment is the first after the current DDAgreementDate (TransactionCode 01) or later ones (TransactionCode 17). The subform's AfterUpdate procedure takes care of this, allowing people to enter payments in any date order. At the same time it caters for the DDAgreement date being changed if a person cancels one agreement then later starts a new one.

To do this it compares the payment entry date with any previous dates after the current DDAgreementDate, finding the first of these with the parameter query I sent last time, ie using the current ContactID and DDAgreementDate on the main form. If this happens to be the currently entered payment it gets an 01 code, otherwise a 17. An 01 from a previous agreement is left as an 01. This all works OK.

My question relates to the second way of adding payments, where some people elect to pay a fixed monthly amount. I flag these, then use an append query to select them and add the chosen amounts to each monthly payer's record in one hit, ie the payments table source for qryInPayments.


INSERT INTO tblInPayments ( DateReceived, Status, Amount, PaymentType, TransactionCode, ContactID, GiftAid )
SELECT [Enter date for payments] AS DateReceived, "Monthly Donation" AS Status, qryContacts.MonthlyDonationAmount, "Direct Debit" AS PaymentType, "" AS TransactionCode, qryContacts.ContactID, qryContacts.GiftAidActivated
FROM qryContacts
WHERE (((qryContacts.MonthlyDonation)=Yes));

A command button on the main form runs this append query (separate question is getting the current record to update if it happens to be showing a monthly payer, but for now I just use a manual form refresh).

Doing it this way means the payments don't see the code to do the 01/17 allocations, as this code is in the subform's AfterUpdate.

I'm asking if when running the append query I can also include this 01/17 allocation for all monthly payers - instead of having to step through and manually refresh these people's records.

This sounds horribly complicated on reading through, sorry.

 

[dmarsh16946]

Hi Again

I have now circumvented the problem since this trail looks to have gone cold - thanks so much for your interest.

I'm now setting a flag on the main form when a first payment is made after the current set date. The code running the batch update is on a new form that shows just the monthly payers in a subform, setting the transaction code for each payment to 01 if the First Payment date flag hasn't been set, or 17 if it has.