Updating two tables with one form

[Plastic1]

I have created a very simple database that I want to use for bookkeeping.

At the moment it has three tables as follows:

Table 1 - General Journal Master
Columns - ID (auto, PK), Entry No (INT), Date, Description (TEXT)

Table 2 - General Journal Detail
Columns - ID (auto, PK), ID.GJMaster, ID.AccountsList, Amount

Table 3 - Accounts List
Columns - AccNum (PK), AccName

The GJ Detail table has a relationship with the GJ Master table (ID.GJMaster), and the Accounts List table (ID.AccountsList).

I want to have a form called the "General Ledger" form that does the following:

1. Adds one entry line to the General Journal Master table with the Entry Number, Date & Description of the Journal, with the PK-ID to be hidden.

2. For the same entry in General Journal Master, it adds a line in General Journal Detail for each entry required, e.g. the one form entry might need to add three lines to General Journal Detail if three accounts are affected by the one transaction.

3. I also want the form to have a look up function where it selects the Accounts List ID field by display a list of the Account Numbers and Names when you click on it.

How would this be possible? Essentially I'm asking how is it possible to update two tables with one form...

[Plastic1]

Can anyone point me in the right direction with this? Any ideas ofn updating two tables with one form?

[QuazzieEvil]

Some thoughts: First, You may consider having one ledger, and create a view to single out a given account (by AccountID column in ledger). This would eliminate the need to write to two ledgers for every transaction. Otherwise, you can write a quick BASIC macro that copies the data to the other table (use the AFTER UPDATE event of form). I have some tutorials at http://www.baseprogramming.com/resources.html that may help you.

ALSO, You may consider GNU Cash. It is complex (it is a full, double-entry accounting package), and may not suite your needs if you just want something simple. one thing I do like about GNU Cash is that it can store accounts and ledgers in various formats, including MySQL and PostreSQL. writing to a database may be useful if you want to access the data programmatically.
_________________
Free Docs @ http://www.baseprogramming.com/resources.html
Book @ lulu.com http://www.lulu.com/content/2455551

[Plastic1]

I have figured out in Base how to have a main form that links to one table and a sub-form that links to another table, but...

When I choose the format for my sub-form I can choose to have an area to enter one record at a time in the sub-table using text boxes or list boxes OR I can choose to enter multiple records in the sub-table by using a sub-form that looks kind of like a spreadhseet.

The problem is that I want to be able to enter multiple records at a time (like the spreadsheet), but I also want to be able to use a look up function to fill in some of the columns, which I believe is only available using the other method (i.e. list boxes).

I tried to copy and paste the text boxes that allow input one record at a time but entered them multiple times, but this just creates text boxes that repeat the information in the first line.

So is there a way to be able to enter as many records as you wish in one sub-from, but also allow some of the table's columns to be list boxes or combo boxes, etc?