Balance field on form

[BobNTN]

I know it's on here but I can't find it.

Have a payment form (FrmCustPay) showing name, CID, etc.
Have Subform to enter payments.

I am trying to add a text box that shows the balance of the customer (using QryCIDBal) based on the CID field.

My query:

SELECT TblCustInfo.CID, TblCustInfo.Cancel, nz([SumOfChargeamt],0) AS Charges, nz([SumOfCreditamt],0) AS Payments, nz([SumOfChargeamt],0)-nz([SumOfCreditamt],0) AS RunBalance
FROM (TblCustInfo LEFT JOIN qryTotalCharges ON TblCustInfo.CID = qryTotalCharges.CID) LEFT JOIN qryTotalPayments ON TblCustInfo.CID = qryTotalPayments.CID
WHERE (((TblCustInfo.CID) Like [Forms]![FrmCustPay].[CID]) AND ((TblCustInfo.Cancel)="n"));

The text box name is CIDBalance and source is QryCIDBal.runbalance

All I get in the box is #Name.

I can execute the query and it prompts for the CID and once input, works fine.

What am I overlooking, please ?

 

[pbaldy]

A textbox can't have a separate query as a source (separate from the form's source). Is that what you've got? You'd have to use a DLookup to get the value from the query, or place the value there with code, using a recordset on that query.

 

[BobNTN]

A textbox can't have a separate query as a source (separate from the form's source). Is that what you've got? You'd have to use a DLookup to get the value from the query, or place the value there with code, using a recordset on that query.

Yes Paul, I'm afraid so. Just thought I could use the steps I used in another app to do this without a bunch of code.
Oh well, just a thought.
Thanks

 

[pbaldy]

No problem. A DLookup should work:

=DLookup("RunBalance", "QryCIDBal")

 

[BobNTN]

That goes in the control source field of the text box ?
then I don't need the "Like' statement in the query ?

 

[pbaldy]

You would still need that in the query, or you could add a similar criteria to the DLookup and take it out of the query. More info on DLookup syntax here:

http://www.mvps.org/access/general/gen0018.htm

 

[BobNTN]

That worked fine. Much more simple than I thought.

One other question.
In a query, if you change the joins of tables for that query, does it change the joins of those tables overall or just for the query ?

 

[pbaldy]

Just for that query.

 

[BobNTN]

As always, thanks Paul.

How is the great Vegas ? PresBO has hurt the gambling world with his rhetoric , or so the casino owners say.

I've got to get out there this year.

 

[pbaldy]

I'll be there next week for my employer (we have operations there). Business certainly took a big hit, though I suspect it was more due to the economy than his rhetoric. It seems to be picking back up slowly. Come on out; we need you!!

 

[BobNTN]

I love Vegas.
But have to wait for good deals.

As a local, you should be able to get me a great deal!

BTW, really like your website

 

[pbaldy]

Not sure I'm considered a local, living 400 miles away and all.

They do some pretty aggressive marketing in the summer season though. You can get some great deals. Many (most?) of the casino websites have rate calendars where you can see when the best deals are.

Thanks for the website compliment. I'm trying to make it as helpful as I can. Obviously I'm not too good in the "make it look good" department.

 

[BobNTN]

You would still need that in the query, or you could add a similar criteria to the DLookup and take it out of the query. More info on DLookup syntax here:

http://www.mvps.org/access/general/gen0018.htm

I looked at that but not sure how I would refer to the CID field of the form (criteria) and the query result field (RunBalance)

Not having to have the "Like" statement in the query WOULD allow me to use the Balance field in different forms with virtually the same DLookup statement

 

[pbaldy]

Post your attempt and we'll fix it. What's the data type of CID?

 

[BobNTN]

CID is an autonumber field

The DLookup I used is what you gave me =DLookUp("RunBalance","QryCIDBal")

What I was saying, if I take the "Like" statement out of the query to use only the DLookup, then it seems to me I would need two different criteria in the DLookup, one for whatever CID is displayed in the form, then one for the query result based on that CID.

Tried

DLookup("RunBalance", "QryCIDBal2", "Criteria = " & forms!FrmCustPay!CID)

All I get is #Name

(QryCIDBal2) is same query without the Like statement

 

[pbaldy]

Does this work if you take the CID criteria out of the query?

=DLookUp("RunBalance","QryCIDBal", "CID = " & [Forms]![FrmCustPay].[CID])

 

[BobNTN]

Yes
that was stupid of me to not replace the word 'criteria' with the actual fieldname.
Thanks Paul, again

Now I have one more problem putting 2 queries together I will post over in queries.

Thanks again. You are a gentleman and scholar.

 

[pbaldy]

Happy to help. My old instructors would debate the "scholar" part, and many of my friends would debate the "gentleman" part, but "man" seems safe.

 

[BobNTN]

Make the pic on your site a little bigger so it can be appreciated more.

 

[pbaldy]

Clicking on it will bring up a bigger version. You want an even bigger one?