request box for record count paramerter value

[TLJ]

Totally in over my head here. I have client management type data base. I am attempting to track their accruing fees to me. I have created a sub form along with a financial table. Any input from any client record goes into this financial table as primay key, date, payment amount. My set fee is 40/mo. I created a query to base my report on attempting to use a text box in a report that says
=[RecordCount]*40-[textbox 7] the text box is a sum(PF's paid). When I run my report a box pops up requesting the parmeter value for record count. If I give it the correct number of records for that primary key value then the report adds correctly. If not the payment ledger is still created but the amount delinquent is wrong although my total paid is correct. What am I doing wrong? By the way I know nothing about codes etc...
I would like these to also auto populate on my client form to show in two text boxes amounts paid and amounts owed.

I have tried to post. Stripped and zipped is still about 245k. Would be glad to email to some one for help if needed.

 

[Pat Hartman]

I know nothing about codes

- The word code when used to refer to the instructions that form a computer program or any part there of, is never pluralized since it is a collective noun.

To make your count work properly, add another control to the group footer to count the records. Set its sum property to "over group".

= Count(*)

Then reference that control in your calculation.

= (txtRecordCount * 40) - [textbox 7]

 

[TLJ]

Pat thankyou very much. I added a text box in my group footer. (You know, I borrowed this report format from another and attempted to change it to meet my needs. I don't know how to add a group footer.)

Now for part two of my several continuing questions.

All of my clients pay 40/mo. Some also pay an additional 20/mo. I have a list box on my main form for yes or no to the additional type (CSD)

I want to add another couple of columns to my financial table and to this report also. I tried this before and it totaled the second group whether I wanted it to or not. I believe I need to link to that column in my clients table and do some form of like yes criteria. Is this right? and either way how?

As I mentioned on my post, I have two text boxes on my main form to post the paid and owed amounts. How do I post my report results automatically to my form (a caculated control?)
Or as a calculated control how can I show theses answers on my main form without running the report.
Why, I am required to print a payment ledger, the main form results are for quick knowledge form my benifit.

Again, thankyou. Knowledge is always a powerful thing. They used to say sometimes you pay the tv repair for knowing where to bang on your tv set. I have worked hours reading here and there trying nothing that worked.

 

[TLJ]

Pat,

I figured out adding my second payment set. If I don't enter any values, the report is blank. That will work for me. Counts both sets now.

Perhaps this is a FORMS question, but can you tell me how to do the calculated controls to post to my main forms on each client the total paid and owed based on the infor on my financial table as displayed in my sub form. Tried to do a similar type thing to my report syntax. These controls don't even show up on my sub form.

Thanks again. Its bed time. My 1 1/2, 3, and 4 yr old have been asleep along time. It would be tragic for me to wake them now.

 

[Pat Hartman]

My guess is that your application isn't using a "normal" set of tables for this process. When you have an application that needs to "bill" clients periodocially, you run a query that selects the clients that need to be billed and appends invoices to an invoice table. When a client pays, you need to update the invoice record. If you have special charges such as late fees, they need to be added to the invoice table when they are generated and updated when they are paid.

You would then use the invoice table to tell you what was billed and what was paid.

You can have a form based in client with a subform that shows invoices. The footer of the subform can contain controls that sum the billed and paid amounts. You don't ever need to post these totals to the client record.

 

[TLJ]

Pat,

I work for a government agency. We do not normally bill our clients. We are required to track their fees and simply send form letters if they get delinquent or direct them verbally to pay.

That's why I need to know how to do caculated controls or totals either on a main form or my financial sub form. Any help still appreciated.

 

[TLJ]

Attaching scrn shot top of my Client form to help explain what I am using it for

 

[TLJ]

Attaching screen shot of bottom part of client form to further help explain

 

[Pat Hartman]

You don't actually need to bill a client, you simply need to add a row to the invoice table at the time the charge is incurred.

I would rearrange the form. It is much too large. Use a tabbed form so that each of the subforms can go on a separate page. Separate the main form into two or three forms. Make sure that all the required fields appear on the first form.

Then to solve your problem, you can use DSum() to obtain the total amounts or you can add a footer to the subform and use Sum() to sum the values.

 

[TLJ]

Thanks again for your input. I have considered tabbing my form. However, I use this to go to court with. My clients appear before the court every other week. I need the information available at a quick glance and was afraid that tabbing forms my get me confused. The Judge only spends a few minutes with each client and I am required to document what occurs as well as providing verbal input of my own.

As to the dsum suggestion, I have looked at the dsum examples in "help" and don't have a clue of how to write the expression for my situation. I have attempted some and nothing works. Could you please be more specific?

I also do not know how commercial billing works or syntax used as mentioned in your last post. However, I really do not like the financial subform that I have borrowed for the following reasons not to mention it is primitive and tedious. First you must manually enter a new line each month whether you receive a payment or not. Second, it does not allow for multiple payments in the same month as each line is counted as a monthly billing cycle. Third, I cannot start with existing totals and then move on from there. I have numerous clients that have been paying for months or years to manually enter. Can you point me to an example or would you be willing to email me one? Thank you.

P.S. Please remember that I am from Rural Oklahoma. Seriously, like many you have helped in the past, I don't know much about access, but I am anxious to learn.

 

[Pat Hartman]

First you must manually enter a new line each month

this is the "billing" process. Create a query that selects each client that needs to be "billed" and turn it into an append query to create these records automatically every month.

Try using Sum()'s in the subform's footer. Add the following to a new control in the footer.

=Sum(YourFieldName)

 

[TLJ]

Pat thanks.

I had already tried =Sum(YourFieldName) in the form footer area and could not get it to work. I realized today quite by accident that my subform showing my billing was in datasheet view and doesn't show the form footer. I don't know if I am able to see it somehow. It doesn't matter as I want the data sheet view and I am viewing my results by referencing the controls from the form footer in my main form where I had placed controls for that purpose. I have one more question that I plan to post to the forms forum this evening.

Thank you again.

 

[TLJ]

Pat,

I believe I have a better way to count my fees owed. I now have a control that counts records. I have a control on my main form that has a begin date. If I change my record count control to the following I could enter as many payments as I wanted without them being counted as billing cycles.

my control needs to be an integer returned as how many months todays date is from the start date (text box47).


i.e. If start date is 1-1-03 and today is 8-22-03, 7 mos has passed.