Query on Form displaying data from subform

[kvest]

I am working on a payments portion of a database. I have designed a Payment Entry form with an embeded subform (from a payments table) which shows all previous payments made on this account. The main form allows new payments to be entered as they are recieved at a walk up window or via mail.

I would like to add a box on the main form that shows the total payments recieved to date. I have designed a query that totals that information, but have no idea how to get it to appear on the main form and run without the user inputing the account number. Is there a way to get this query to launch and run once the record is loaded AND retrieve the account number from record that the user ends up on? Users search for accounts by using numerous fields (Name, Due date, etc...) since no one ever remembers or has their account number.

I assume setting the query to run On Load would work, but getting it to only display the current account is beyond me...

Any help would be great!!! I could setup a button that launches the query and then make them enter in the account number, but that is tacky....

Thanks

 

[PHV]

HAve a look at the DLookUp function you can use in the Current event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kvest]

PHV,
I have what is listed below and am getting a "Compile error - Expected list seperator or )." It highlights the ", " part after "SumOfAmountPaid....

Me!MonitorFeesPaid = DLookup(["SumOfAmountPaid]", "QryMonitoringFeePayments", "BookingNumber = " & Me!BookingNumber)

What am I missing? Am I even close?

 

[PHV]

And what about this ?
Me!MonitorFeesPaid = DLookup("SumOfAmountPaid", "QryMonitoringFeePayments", "BookingNumber = " & Me!BookingNumber)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kvest]

PHV,
I just figured that out...thanks for the quick post. Now I am getting a Data Type mismatch error. I assume I need some DIM statements, but those are a bit Greek to me also.

I tried..

Dim MonitorFeesPaid as Long

and that didnt work. Do I need to declare all of them?

Thanks in advance...

 

[PHV]

If BookingNumber is not defined as numeric:
Me!MonitorFeesPaid = DLookup("SumOfAmountPaid", "QryMonitoringFeePayments", "BookingNumber=[tt]'"[/tt] & Me!BookingNumber & [tt]"'"[/tt])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kvest]

That has it.....Great. It appears to not update that amount when other payments are made though. I assume that is a rqry function or something. I tried runing the query manually and it doesnt update either...

I will research that....Thanks a ton.

 

[kvest]

Nevermind...my error I entered the new (or changed) fees payments in the wrong column. A star for You!!!!