Display query results in a form 2

[ccs2]

I have a DVD rental dbase which has four related tables: tblCustomer, tblCustomerLoan, tblLoanBatch and tblDVD. The main data entry form (frmCustomerLoan) is based on a select query (qryLoans) using a subform (frmLoanBatch Subform) for DVD's taken out by a customer.

I have created a query (qryTotal) which adds (sums) the rental for each DVD taken out by individual customers. If you put in a Loan number in the parameter box it will do this.

What I want to do is display this on the data entry form in a text box. I have put in a reference to the query in the properties of the
text box:

=[qryTotal]![SumOfRental]

and set up the Criteria in the query to refer to the LoanNumber field on the data entry form:


[Forms]![frmCustomerLoan].[LoanNumber]

All to no avail. I get an error message when opening the form telling me there is a missing or broken reference but here I am stuck as I am unsure as to whether I need to install another feature from Access or if I have made a mistake somewhere else. Any suggestions?

 

[Remou]

I think you need to look at DlookUp and DSum. For example,
[tt]=DlookUp("SumOfRental","qryTotal","LoanNumber=" & [Forms]![frmCustomerLoan].[LoanNumber])[/tt]

 

[ccs2]

Thanks, I had already suspected Dlookup from random searches but was unsure how to implement. Do I put this coding into the Properties for the text box on my form?

Cavan

 

[Remou]

Yes, as the control source, or simply type into a textbox, or yet again, use the expression builder.

 

[ccs2]

It works!! Thanks for your help.

Cavan