Working with Forms

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

srikanth - 25 Jan 2006 06:31 GMT

I linked up two tables - one is students fees table and another one is
receipts table. I want to update the student ID fees by entering fees
collected. I have taken the fields - receipt id, date, stu.id, stu.name,
amt.paid, bal.amount, tot.amount (stu.id being the foreign key in receipt
table). When I select the stu.id and enter the fees paid in amt.paid field
the bal.amout has to be updated each time when I select the same stu.id. till
it is zero.
Eg. if the tot.amount to be paid is 5000 and the student pays in
installments each time when I select the same stu.id. the amount should be
updated.

Reply to this Message

Marshall Barton - 25 Jan 2006 16:23 GMT

>I linked up two tables - one is students fees table and another one is
>receipts table. I want to update the student ID fees by entering fees
[quoted text clipped - 6 lines]
>installments each time when I select the same stu.id. the amount should be
>updated.

I disagree. The general theory of databases is that you
should not save derived values in tables (if for no other
reason than your problem). The bal.amount field should be
removed from your table. Instead, this value should be
calculated whenever you need to display it for someone to
look at on a form or report.

Calculating the value can be done in several ways depending
on how you've designed your user interface. Most common is
to use a main form for the student data and a (continuous?)
subform to display all the payments for a student. In this
kind of situation the total can be displayed in the
subform's footer section by simply using a text box with the
expression =Sum([amt.paid])

BTW, you are making things a little messier for yourself by
using a dot in your field names. A best practices
recommendation is to aviod using any non-alphanumerix
characters, especially dot. The dor character is the
character Access/SQL uses for several of its own purposes
(e.g. Me.textbox to refer to a control or table.field).

Signature

Marsh
MVP [MS Access]

Reply to this Message

srikanth - 27 Jan 2006 05:10 GMT

Thanks Mr.Marshall. Exactly, what you have mentioned is right. I want to know
the balance amount payable on a report and also on the form as when the
student comes and pay for the next time, I should know the balance amount
payable. I tried what you have mentioned but still I am facing some problems.
Like we collect fees in 2 terms - In one term itself the student pays in one
or two installments. After doing the 1st term when I updated the 2nd term the
amount paid in the first term is also calcuated in the sum function. whereas
I want to cut off the amount paid in 1st term and 2nd term separately and
ofcourse if any balance available in the 1st term has to be carried forward
in the 2nd term.

I would be very very thankful to you if you could send me this program to me
by email at ivyacd@hotmail.com or if not possible answers indepth for me to
understand little more

> >I linked up two tables - one is students fees table and another one is
> >receipts table. I want to update the student ID fees by entering fees
[quoted text clipped - 28 lines]
> character Access/SQL uses for several of its own purposes
> (e.g. Me.textbox to refer to a control or table.field).

Reply to this Message

Marshall Barton - 27 Jan 2006 15:42 GMT

To isolate the calculation to a single term, you will need a
term identifier field in the receipts table. Then your
form's header section will need an additional unbound text
box for you to indicate which term you are working on at the
moment. This text box's name would then be added to the
subform control's LinkMaster property and the term field in
the receipts table would be add to the LinkChild property.
After doing that, the subform will only list the payments
for the single term specified in the main form's header's
text box so the total will only sum the that one term.

Signature

Marsh
MVP [MS Access]

>Thanks Mr.Marshall. Exactly, what you have mentioned is right. I want to know
>the balance amount payable on a report and also on the form as when the
[quoted text clipped - 43 lines]
>> character Access/SQL uses for several of its own purposes
>> (e.g. Me.textbox to refer to a control or table.field).

Reply to this Message

srikanth - 28 Jan 2006 07:14 GMT

Thanks once again. But I could not get what you mentioned. Would you please
eloborate on in. Any code is required for it. And also how do I get the
balance report in a single term and the balances if any has to be carried
foward to the next term.

> To isolate the calculation to a single term, you will need a
> term identifier field in the receipts table. Then your
[quoted text clipped - 53 lines]
> >> character Access/SQL uses for several of its own purposes
> >> (e.g. Me.textbox to refer to a control or table.field).

Reply to this Message

Marshall Barton - 28 Jan 2006 14:28 GMT

There is no code needed for what I was describing. It's
just a matter of having the needed data available in your
table and form. Think of the unbound term text box in the
form's header section as a search criteria to find the
receipts for a specific term (using the Term field in the
table). The actual mechanism that performs the search is
the subform control's Link Master/Child Fields properties.
What I've tried to describe is about as detailed as I know
how to make it.

For a carry forward amount from previous terms, that is a
different question that involves accounting procedures to
determine what carry forward really means. This is best
left for another time when you have gotten past the current
issue. About all I can say at this point is that it will
probably involve a separate calculation from what we've been
discussing so far.

Signature

Marsh
MVP [MS Access]

>Thanks once again. But I could not get what you mentioned. Would you please
>eloborate on in. Any code is required for it. And also how do I get the
[quoted text clipped - 59 lines]
>> >> character Access/SQL uses for several of its own purposes
>> >> (e.g. Me.textbox to refer to a control or table.field).

Reply to this Message