Fill textbox with result from query

[brianinmaine]

Such a simple question, why so hard to find the answer?

I have a form with family info and a subform with their members. Many members of each family pay fees for classes they take. How do I display the result of the query in the textbox? DLookup? I tried that many times and cound't get it to work. Do I have to start over on my form and base it on a query or something? I hope not! The query works fine and it comes out with the right answer in a subform, but I would really like a simple textbox.

Is there a really great book that teaches this stuff well? I've tried a few, but most are either to easy (I can figure out wizards, thank you

) or too hard (all SQL?). Any suggestions?

I'm still really new at this. Thank you for your efforts?! - Brian

 

[Rich]

What results are you talking about do you mean the sum of fees per family?

 

[Chris RR]

Let me just clear up one thing. If you did decide to base your form on a query, you don't need to start over, at all.

If the query contains at least the same fields your using in the form (and maybe more), then all you do is change the "record source" property for your form from the table name to the name of your query. It's not unusual at all to base a form on a query (I do this more often than not.)

Then, try getting a copy of "Using Microsoft Access 2000" by Susan Sales Harkins, Ken Hansen, etc (it's a "Que" book) It's pretty good, and keep reading this forum. Good luck.

 

[brianinmaine]

Yes I want to place a sum of fees textbox on my form after I already made the form. I see that I can change the form recordsource from a table to a query, but is there another way to do this for only 1 or 2 boxes? In general, I mean, not just this example. Can you have a textbox based on a query result if the form is set for a table?
-Thanks for trying! - Brian

 

[Rich]

Add a textbox to the subform footer, set its control source to =Sum([Fees]), add a text box to the main form, set its control source to =Forms!MainformName!SubFormName subform.Form!TextboxName.
HTH

 

[brianinmaine]

Awesome! Thanks!-Brian

 

[brianinmaine]

Why do I now get an #error when I try to view this field sometimes. I realize its looking at a null, but Nz() doesn't seem to work for this. Any other way? I've tried to change the underlying query also but same result. I should be able to have it display $zero when it is $zero, instead of #error.
Thank you for your suggestions?!! - Brian

 

[Pat Hartman]

Try -
=IIf(Sum(Nz([Fees],)) = 0, "$Zero", Sum(Nz([Fees],)))