Using a query in an expression

[atrium]

Users add transactions via a form after they put in the total of the transaction I want to run a query that gets a running balance of the client's account to make sure that paying this transaction will not over draw the clients account.

What would be the best method of approach?

Any help would be appreciated

 

[atrium]

Further to the above.

This is the code I'm trying to implement

If Me.TransCode <> "" Then
Select Case Me.TransCode
Case 23, 24, 62, 63, 64, 65, 60, 61, 66
If (DSum("TransAmount", "CheckTrustAcctForThisMatterQry", "[Transactions].[MatterId] = " & Me.MatterId])) - Me.AmountTotal < 0 Then

MsgBox ("This transaction will put the trust ledger for this matter into negative")
Me.GetReceiptButt.Enabled = False
Else
Me.GetReceiptButt.Enabled = True
End If
End Select
Me.GetReceiptButt.Enabled = True
End If


Where TransAmount is the result of an expression in the query

Where CheckTrustAcctForThisMatterQry is the query

Where Transactions.MatterId is the common field of the transactions that I want to sum

At the moment I get a compilation syntax error

 

[JHB]

If (DSum("TransAmount", "CheckTrustAcctForThisMatterQry", "[Transactions].[MatterId] = " & Me.MatterId])) - Me.AmountTotal < 0 Then

At the moment I get a compilation syntax error

Yes you have a "]" to much or a "[" to less in your code.

 

[atrium]

Thank you JHB, (blind Freddie should have seen that) I'll make the change and test it.

Cheers

Atrium

 

[atrium]

I now get
Run time error '2471'

The expression you entered as a query parameter produced this error
'[Me].[MatterId]'

the code that is highlighted is

If (DSum("TransAmount", "CheckTrustAcctForThisMatterQry", "[Transactions].[MatterId] = " & Me.MatterId)) - Me.AmountTotal < 0 Then

When I move the cursor over

[Transactions].[MatterId] and & Me.MatterId they both = the same value which is what I want


Any suggestions

 

[JHB]

Show the SQL-String for the query "CheckTrustAcctForThisMatterQry".

 

[atrium]

This is the SQL for CheckTrustAcctForMatterQry

SELECT Clients.ClientId, Matters.MatterId, Matters.MatterTitle, Matters.MatterLongNo, Matters.CommisionRate, Clients.ClientFileNumber, Clients.FirstName, Clients.LastName, [Payment methods].PaymentMethodDesc, Transactions.TransDate, Transactions.TransId, Transactions.TransCode, [Transaction Types].TransTypeDesc, First(Transactions.Particulars) AS FirstOfParticulars, Transactions.BankStatementDate, Transactions.MethodOfPayment, Transactions.ReceiptNo, Transactions.ReferenceNo, Transactions.Reversal, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Deposits, IIf(Eval(([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Withdrawls, IIf(Eval([Transaction Types].[Deposit]=True) And ([Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True) And ([Transactions].[Reversal]=True),[AmountTotal],([AmountTotal]*-1)) AS TransAmount, [Transaction Types].TrustLedger, [Transaction Types].Deposit, [Transaction Types].Withdrawl, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Expr1, Format([Transactions.TransDate],"Short Date") AS TrueSortDate
FROM (([Transaction Types] RIGHT JOIN (Transactions RIGHT JOIN Matters ON Transactions.MatterId = Matters.MatterId) ON [Transaction Types].TransTypeId = Transactions.TransCode) LEFT JOIN Clients ON Matters.ClientId = Clients.ClientId) LEFT JOIN [Payment methods] ON Transactions.MethodOfPayment = [Payment methods].paymentmethodsID
WHERE ((([Transaction Types].TrustLedger)=True))
GROUP BY Clients.ClientId, Matters.MatterId, Matters.MatterTitle, Matters.MatterLongNo, Matters.CommisionRate, Clients.ClientFileNumber, Clients.FirstName, Clients.LastName, [Payment methods].PaymentMethodDesc, Transactions.TransDate, Transactions.TransId, Transactions.TransCode, [Transaction Types].TransTypeDesc, Transactions.BankStatementDate, Transactions.MethodOfPayment, Transactions.ReceiptNo, Transactions.ReferenceNo, Transactions.Reversal, [Transaction Types].TrustLedger, [Transaction Types].Deposit, [Transaction Types].Withdrawl, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0), Transactions.Particulars, Transactions.AmountTotal
ORDER BY Clients.ClientId, Matters.MatterId, Transactions.TransDate, DateValue([Transactions].[TransDate]);



atrium

 

[vbaInet]

Why don't you/you could put this in a textbox in the footer section of the form:

=Sum([TransAmount]) - [AmountTotal]

... and check the value of the textbox in your validation code.

By the way, in your SQL you're joining against too many tables for one Record Source. That's indicative that you need to break it down into subforms.

 

[atrium]

I have revisited the qry and it is now

SELECT Matters.MatterId, Transactions.TransId, Transactions.TransCode, Transactions.ReceiptNo, Transactions.Reversal, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Deposits, IIf(Eval(([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Withdrawls, IIf(Eval([Transaction Types].[Deposit]=True) And ([Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True) And ([Transactions].[Reversal]=True),[AmountTotal],([AmountTotal]*-1)) AS TransAmount, [Transaction Types].TrustLedger, [Transaction Types].Deposit, [Transaction Types].Withdrawl, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0) AS Expr1
FROM [Transaction Types] RIGHT JOIN (Transactions RIGHT JOIN Matters ON Transactions.MatterId = Matters.MatterId) ON [Transaction Types].TransTypeId = Transactions.TransCode
WHERE ((([Transaction Types].TrustLedger)=True))
GROUP BY Matters.MatterId, Transactions.TransId, Transactions.TransCode, Transactions.ReceiptNo, Transactions.Reversal, [Transaction Types].TrustLedger, [Transaction Types].Deposit, [Transaction Types].Withdrawl, IIf(Eval(([Transaction Types].[Deposit]=True And [Transactions].[Reversal]=False) Or ([Transaction Types].[Withdrawl]=True And [Transactions].[Reversal]=True)),[AmountTotal],0), Transactions.Particulars, Transactions.AmountTotal
ORDER BY Matters.MatterId;



And I have changed the code to


Dim intTrustBalance As Integer
If Me.TransCode <> "" Then
Select Case Me.TransCode
Case 23, 24, 62, 63, 64, 65, 60, 61, 66
intTrustBalance = (DSum("TransAmount", "CheckTrustAcctForMatterQry", "[Transactions].[MatterId] = " & Me.MatterId))
If (intTrustBalance - Me.AmountTotal) < 0 Then

MsgBox ("This transaction will put the trust ledger for this matter into negative")
Me.GetReceiptButt.Enabled = False
Else
MsgBox ("This transaction will be OK")
Me.GetReceiptButt.Enabled = True
End If
End Select
Me.GetReceiptButt.Enabled = True
End If


I now get an overflow error on the expression at intTrustBalance

Atrium

 

[JHB]

..
I now get an overflow error on the expression at intTrustBalance

Then change the variable type to ex. Long.

Dim intTrustBalance As Long

 

[vbaInet]

JHB has got you covered but why don't you use the simple calculation I showed? Or is it that not all the records get pulled into the form?

 

[atrium]

I have now created a query that isolates one record for each matter containing the MatterId and the SumOfTransAmount. I have a look at the result of the Qry and it's just what I want.

When I'm adding or modifying a transaction I want a field on the form to show the Qry row for the current matter. I am using the following but it comes up with #Name?
I obviously have this wrong

= DLookUp([MatterTrustBalQry]![SumOfTransAmount],[MatterTrustBalQry],[MatterTrustBalQry]![MatterId]= “ & FORMS![MattersFileFrm]![MatterId])

Regards
Atrium

 

[namliam]

Please use code tags when you post code on the forum (see my signature for a link)

Be carefull when (over) using domain functions, a lot of times better solutions can be found (not saying this is one of them since I didnt read the whole thread)

Try this:

= DLookUp([SumOfTransAmount],[MatterTrustBalQry],[MatterId]= “ & FORMS![MattersFileFrm]![MatterId])