Solved Difficulties in creating "copy" type records

[MikeT1941]

Good evening all

This must be very basic, but I am still no better than I ever was at VBA but would like to crack this one with some help.
The purpose is to record a payment into the bank as a "Client Deposit" and then use that data to create two ledger entries.
The first one deducts the amount from the Client Deposit(40050000) account and the second adds the same amount to the Rental income account (40200000)
Both are dated 42 days before the start of the holiday rental, when the deposit become non-refundable. So there are a total of three ledger entries of which two will be excluded from reconciliation in due course.
Should I set the variables as global and use a separate subroutine?

I hope the error/s will be obvious from the code, but can supply more if required.
Any assistance appreciated
Mike

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim NewAddDate, NewDate As Date
 Dim DoICopyRecord As Variant
 Dim tt1, tt2, tt3, tt4, tt5, tt6, tt7, tt8, tt9, tt10, tt11, tt12 As Variant
Dim rstbanksubformquery As Recordset
Set rstbankentries = CurrentDb.OpenRecordset(name:="banksubformquery", Type:=RecordsetTypeEnum.dbOpenDynaset)

newBankOtherCategory = 10100000

On Error GoTo Err_Click

DoCmd.RunCommand acCmdSelectRecord

DoCmd.RunCommand acCmdCopy
    If NewBankCategory = 40050000 And (Me.NewRecord) Then
Debug.Print BankID

        tt2 = Me.BankDate
        tt3 = Me.BankMemo & "   Ledger Entry only"
        tt4 = Me.BankTransaction
        tt5 = Me.CofANewCode
        tt6 = Me.BankMethod
        tt7 = Me.BankBookingID
        tt8 = Me.BankReceivedFrom
        tt9 = Me.CofA_AccountID
        tt10 = Me.bankReconciledFlag
        tt11 = 40050000
        tt12 = 10100000 'normally 10100000 current account
        NewAddDate = DateAdd("d", [Text32], -42)
        NewDate = Me.BankDate 'saves date for pasting to record in BankEntries
    End If




 With rstbankentries
        .AddNew


![BankDate] = tt2
![BankMemo] = tt3
![BankTransaction] = -tt4
![CofANewCode] = tt5
![BankMethod] = tt6
![BankBookingID] = tt7
![BankReceivedFrom] = tt8
![CofA_AccountID] = tt9
![bankReconciledFlag] = tt10
![NewBankCategory] = 40050000
![newBankOtherCategory] = tt12
   .Update
    End With

    Requery



'
'  With rstbankentries
'        .AddNew
'![BankDate] = tt2
'![BankMemo] = tt3
'![BankTransaction] = tt4
'![CofANewCode] = tt5
'![BankMethod] = tt6
'![BankBookingID] = tt7
'![BankReceivedFrom] = tt8
'![CofA_AccountID] = tt9
'![bankReconciledFlag] = tt10
'![NewBankCategory] = 40200000
'![newBankOtherCategory] = tt12
'   .Update
'    End With
'
'    Requery

 
Exit_Click:
Exit Sub

Err_Click:
MsgBox Err.Description
Resume Exit_Click
 
 End Sub

 

[The_Doc_Man]

Should I set the variables as global and use a separate subroutine?

That depends, but OFFHAND I would say probably not required. Looks like you want to add three records using a single recordset to do it. From the technical viewpoint, I know of no reason why you couldn't have a single subroutine do three updates to the same recordset. I would add, though, that since you are opening a recordset in the subroutine, you should close it before you leave the same sub. Otherwise you would get a buildup of "dangling" recordsets, which would probably lead to resource or memory errors of some kind or another.

I'm not up on banking regulations so the appropriateness of this is not something I would answer. BUT I see that you are making those entries to the same recordset and using the same "tt" values for most of the fields, and THAT is kind of suspicious. The implication is that you are using them for different purposes, but other than changing the sign of tt4 for one case and adding the "Ledger Entry" tag, I don't immediately see any differences in the records that you would have added. It appears to be duplication of effort since you could easily make queries for the two entries you say would NOT be reconciled. If you did that, they wouldn't be reconciled because they wouldn't be in the table in the first place. Therefore, not understanding what you would have done with what I see as superfluous entries, I might not be the best person to advise you.

 

[MikeT1941]

That depends, but OFFHAND I would say probably not required. Looks like you want to add three records using a single recordset to do it. From the technical viewpoint, I know of no reason why you couldn't have a single subroutine do three updates to the same recordset. I would add, though, that since you are opening a recordset in the subroutine, you should close it before you leave the same sub. Otherwise you would get a buildup of "dangling" recordsets, which would probably lead to resource or memory errors of some kind or another.

I'm not up on banking regulations so the appropriateness of this is not something I would answer. BUT I see that you are making those entries to the same recordset and using the same "tt" values for most of the fields, and THAT is kind of suspicious. The implication is that you are using them for different purposes, but other than changing the sign of tt4 for one case and adding the "Ledger Entry" tag, I don't immediately see any differences in the records that you would have added. It appears to be duplication of effort since you could easily make queries for the two entries you say would NOT be reconciled. If you did that, they wouldn't be reconciled because they wouldn't be in the table in the first place. Therefore, not understanding what you would have done with what I see as superfluous entries, I might not be the best person to advise you.

Hi DocMan

Thank you for the tip about closing recordsets- I said I was ignorant.

As to the three entries:
I add a figure say £350 when it arrives so that has to be reconciled with the bank account. That's record 1
About 42 days before these guys start their holiday I can treat their payment as income rather than a deposit because it is no longer refundable. So, I move the money from the Client Deposit (Record 2) account to the Rental income (Record3) account. Because I am inherently lazy, I wish Access to do the entries for me. Hope that helps- apologies if not- mea culpa

Mike

 

[CJ_London]

Seems to me you only need the one transaction record - to record the payment and link to your booking record.

the booking record will contain a date field for the start of the holiday

easy enough then to have a query to determine whether the other side of the transaction is a deposit or rental based on whether today (or the effective date of a report) is on or after the date of holiday start -42

otherwise you are into making 4 records

Dr bank
Cr deposit
Dr deposit
Cr rental

To view the nominal records you can either use a union query or (as I prefer) a counter table to provide 2 rows for each side of the transaction. Counter =0 represents the bank transaction (Dr), counter=1 represents the Cr

same principle would be used if the client cancels and you have to provide a refund

On my phone at the moment but will be on my pc a bit later and can upload an example if of interest

 

[MikeT1941]

CJ
Wow- never even used a Union Query. Would be delighted to see an alternative approach but I don't need it tonight. Please don't stay up late for me!!

Mike

 

[The_Doc_Man]

So, I move the money from the Client Deposit (Record 2) account to the Rental income (Record3) account.

Except that unless I totally missed something, you are putting everything to the same table using the same information? WHAT other accounts? It is not clear on inspection that you are in fact doing ANYTHING other than duplicating a particular record. The USUAL way people do what you describe is not to copy anything but to change a status indicator in the record (perhaps to indicate its availability?) I am not up on the ins and outs of finance so all I'm really saying right now is that your actions look a bit opaque to me.

 

[CJ_London]

here is the example I mentioned - the form uses the counter table method, but I've included a union query as well so you can compare

Benefits of the counter method (from my perspective, others may disagree)

• indexes are still used - matters when you have a lot of data - union queries can be quite slow
• the counter method can be extended for use in an aggregate query to provide opening and closing balances

 

[MikeT1941]

here is the example I mentioned - the form uses the counter table method, but I've included a union query as well so you can compare

Benefits of the counter method (from my perspective, others may disagree)

• indexes are still used - matters when you have a lot of data - union queries can be quite slow
• the counter method can be extended for use in an aggregate query to provide opening and closing balances

Many thanks CJ- I'll get on with it and report back

 

[Pat Hartman]

Dim tt1, tt2, tt3, tt4, tt5, tt6, tt7, tt8, tt9, tt10, tt11, tt12 As Variant

Only the tt12 is specifically defined as a Variant. Assuming you want all the variables to be Variants, the code accidentally works because the default for unspecified variables is Variant. Your Dim statements need to specifically define the data type for EACH variable.

 

[MikeT1941]

I'm so embarrassed at not having replied:
Thanks, Pat for putting me right on the variables
I'm looking at a new approach- to do the manipulation in the report rather than worrying about "accruals" or such like. Shouldn't be long now!

 

[Pat Hartman]

You're welcome It's a common mistake since the concept works in other languages and it sure is a pain to have to add "as variant" for every variable. New features get added to old languages but rarely to old features get "fixed"

 

[MikeT1941]

Well, I said I would update you on what happened, so I will. I have been through a great learning curve in the last few days, although it seems a lifetime!
Thanks to Pat, I now know more about VBA. Doc-Man's comment in #6 and Colin's demo triggered something in my mind- was I going the right way?
Did I need any new records? Answer was NO. I needed to use the report, fed by a query.

Here is the report filter:
(([BankDate] >= #04/01/2022#) AND ([BankDate] < #04/01/2023#) AND Date of transaction
([newbankcategory]>=40050000) AND ([newbankcategory]<=70300000) AND Account category
([newbankothercategory]>=10100000) AND ([newbankothercategory]<=70300000)) Bank Account e.g. 10100000

OR ([newbankcategory]=40050000) AND
([BookStart]> (TempVars![ReportEndDate].value-365)) AND Start of holiday after report start
([BookStart] <= (TempVars![ReportEndDate])) and before report finish


This means that deposits in the report year for the following year
are not included except as Client Deposits ( so are not income) but for holidays in the current
and previous years they count as income from the date of the start as the holiday.

Using the Allen Browne filter as a start, it came to me that if I used an OR I could have two date criteria at the same time, so I did.
That then required that the headings in the report would require tweaking using code,
I know it's not perfect, but it works- at least until I find it making errors. You see, I didn't know quite a few things- TempVars, Expressions to feed reports etc.

So, thank you all very much. How could I get anywhere without help?

Mike

 

[Gasman]

Be aware your dates are for 1st April, not 4th January. Hopefully you use usa date format?

 

[MikeT1941]

Certainly, Gasman; date format as per AB. My financial year is 1st April- 31 March so I believe is correct., but always good to check- thanks.

 

[Pat Hartman]

I can't tell if you have multiple separate criteria sets that you swap or if they are somehow combined so I'm going to add a warning. Whenever a complex condition includes different relational operators such as AND, OR, and NOT, you should ALWAYS use parentheses to disambiguate the order of precedence to ensure that the expression gets executed as you think it should. Access adds stupid, unnecessary parentheses. It is always best to remove them because they just add confusion as you are reading the expression.

Keep in mind that
A = B AND C = D OR C = E
is NOT the same as:
A = B AND (C = D OR C = E)

The first expression is evaluated as:
(A = B AND C= D) OR C = E

 

[MikeT1941]

I can't tell if you have multiple separate criteria sets that you swap or if they are somehow combined so I'm going to add a warning. Whenever a complex condition includes different relational operators such as AND, OR, and NOT, you should ALWAYS use parentheses to disambiguate the order of precedence to ensure that the expression gets executed as you think it should. Access adds stupid, unnecessary parentheses. It is always best to remove them because they just add confusion as you are reading the expression.

Keep in mind that
A = B AND C = D OR C = E
is NOT the same as:
A = B AND (C = D OR C = E)

The first expression is evaluated as:
(A = B AND C= D) OR C = E

yes, thanks Pat, that's one reason doing the various mods took so long as there are more ways of doing brackets wrong than there are of doing them right, but I believe these are now correct- they have not been found out yet but I am keeping my eyes open.

 

[Pat Hartman]

I don't like what you've posted. Having the useless brackets just confuses the issue. I always remove them. AND to keep Access from putting them back, I save the querydef in SQL view and try to remember to never save the querydef if I switch to QBE view for some reason.

 

[MikeT1941]

Fine. I didn't know they were not required. I will try it with them removed.

 

[Pat Hartman]

The only brackets that are necessary are those that you place to control (or simply to clarify) the order of precedence so the condition is evaluated as you intend rather than by using the default rules.

Access adds all those useless brackets because it helps the QBE to convert the SQL String to the graphic representation. That is why for querydefs with complex criteria, I just NEVER put them back into QBE view so that Access won't mess up my formatting.