Linking Forms

[Mike]

Hello,

I'm working on creating a checkbook database for my church. I have the
basics set up on it. I created a table of catagories of where money comes in
from, or goes out to. I created the main "Ledger" table which will show an
overview of the income/expenses. This table is basically the same as what
you would see when you look at your checkbook. Then I created a "Sub-Ledger"
table. This table is based on a book they are currently using to track
income and expenses. It has tabs for each catagory, and then pages that show
things like date, description, payment, deposit, and total. Currently I have
the "Sub-Ledger" form set up so I can pick a catagory, and enter the other
information. What I need to do though is link the "Ledger" and "Sub-Ledger"
tables together. That way when a person is entering the checkbook
transaction, then can tab down to the "Sub-Ledger" section and enter the
information as to where the money came from or is going. There are times
when the money is coming from multiple areas, or going to multiple areas, so
I need to be able to enter several lines in the "Sub-Ledger" for one line in
the "Ledger". If you can help, please reply.

 

[Mike]

Thanks for the help... That seemed to simple... I think I have it working
now...

 

[Mike]

Ok, I must have something set wrong.. I have the form and subform now, but
when I enter a second line in the Sub-Ledger section, and use a different
catagory, it doesn't keep it. When I go back to the record it will only show
the first line entered. If I enter several lines, but give them the same
catagory it keeps them. Also I tried setting the value of the "Check/Debit"
field to the sum of the "Check/Debit" field of the sub-ledger, and the same
for the "Deposit" fields, but I get an error. Can anyone suggest any help
here?

 

[John W. Vinson]

Ok, I must have something set wrong.. I have the form and subform now, but
when I enter a second line in the Sub-Ledger section, and use a different
catagory, it doesn't keep it. When I go back to the record it will only show
the first line entered. If I enter several lines, but give them the same
catagory it keeps them. Also I tried setting the value of the "Check/Debit"
field to the sum of the "Check/Debit" field of the sub-ledger, and the same
for the "Deposit" fields, but I get an error. Can anyone suggest any help
here?

If you're trying to store a sum in your table... DON'T.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[Mike]

The fields for my Ledger table are: ID, Item Number, Date, Transaction
Description, Check/Debit, Deposit/Credit.
The fields for my Sub-Ledger table are: ID, Catagory, Date, Description,
Payment Deposit.
These fields match the current "fields" in the current checkbook or other
ledger book that is being used.

 

[Mike]

Thanks for the help. The form seems to be working with the test data. I
just have to make a few navagation changes to it, but that won't be that hard
to do. Is it possible to have the for sum up the items in the subform, and
insert them into the proper field in the main form? I tried using a simple
sum funtion, but i get an error message.

 

[Mike]

Ok, after posting my last message I figured out how to get it to kind of do
what I wanted. I just put text fields in the subform that did a sub funtion
on the payment and deposit fields. Then I set the fields in the main form
equal to the text boxes. This shows the right numbers in the main form, but
it doesn't store the totals in the table.

 

[Mike]

Ok, so I have the form working, and i'm using just a text box to calculate
the totals. Now how would I get that to work on a report. I need to do
several reports, on is a Ledger report, which just shows what is listed in
the checkbook. So this report needs to show just the totals of what is in
the sub-ledger. I tried adding the fields to the report and doing a sum, but
I get the total sum for everything in each field. Any suggestions?