Help passing a query value

[ramrags]

I have a doctor’s database and part of it is a patient close out form. On the form I want to have a field that shows the patient’s opening balance. I created a query that calculates by patient the sum of the procedure charges and the sum of the patient payments, and an expression field that gives me the balance the patient owes. What I’m trying to do is pass the value in the OPEN_BAL field in the qryPAT_SUM query. I’d tried to use the control source property for the text box on the form, but I’m doing something wrong. If anyone has any ideas or if there’s a better way to handle this, and can help I would really appreciate the help. Thanks Tom

 

[roppetech]

Is your form based on the qryPAT_SUM query? If so, you should be able to just drag the OPEN_BAL field onto your form and have it work the way you want.
If you need to base your form on the patient table, try creating a new query joining the patient table and your qryPAT_SUM, and base your form on this new query.

hope it helps!

 

[ramrags]

Actually the form is a form with 3 sub forms attached to it; the form works like a POS transaction form and returns allot data from the database. I can’t make the form from a query because of the data entry needed on the form. I just wanted to add the results from the query sort of like an lookup and also use it in the calculations on the form. I wasn’t sure how to get it there or if it was best to write a function and call it in the code. Let me know if you have any thoughts. Tom

 

[roppetech]

in that case, I'd call a function from the code as soon as the user enters the cutomer number. In the function you can create a recordset equal to your lookup query. Just be careful if this is a multi-user database! I've had bad experiences with defining recordsets in such environments.
good luck!


- RoppeTech

 

[ramrags]

Thanks I thought a function would be the best way to handle this