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.

 

[Steve]

Table Ledger should have a PK of LedgerID. Table Sub-Ledger should then have
a foreign key of LedgerID. With this you can create a form/subform to link
both table. The man form should be based on the Ledger table and the subform
should ve vased ob the Sub-Ledger table. The LinkMaster and LinkChild
properties should be set to LedgerID.

Steve

 

[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.

 

[Steve]

Post the fields in your Ledger table and Sub-Ledger table and I will try and
help you.

Steve

 

[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.

 

[Steve]

To start, I suggest you rename the ID field in the Ledger table to LedgerID
and rename the ID field in the Sub-Ledger table to SubledgerID. Then add a
field named LedgerID to the Sub-Ledger table. Finally open your main form in
design view and go to the Data tab. Set the LinkMaster property to LedgerID
in Ledger table and set the LinkChild property to LedgerID in Sub-Ledger
table. When you open your form, you will only see the Sub-Ledger records
associated with the Ledger record in the main form. Further, any new
Sub-Ledger records you add will automatically be associated with the Ledger
record in the main form. You don't need a LedgerID field in your main form,
a LedgerID field in the subform or a SubLedgerID field in the subform.
LedgerID for the main form will automatically be recorded when you enter a
new record in the main form. SubLedgerID and LedgerID for the subform will
automatically be entered when you enter a new record in the subform.

Steve

 

[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.

 

[Steve]

Hi Mike,

Way to go to get your form working!

You should NEVER store totals in a table. If a number that goes into a total
changes, the stored total will be wrong. You should always calculate totals
on the fly. In your case, you should calculate the total in the subform via
an unbound textbox and then if you want to show the total in the mainform,
again use an unbound textbox.

Steve

 

[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?