OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Updating two tables with one form

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Plastic1
Newbie
Newbie


Joined: 17 Feb 2010
Posts: 4

PostPosted: Wed Feb 17, 2010 7:41 pm    Post subject: Updating two tables with one form Reply with quote

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...
Back to top
View user's profile Send private message
Plastic1
Newbie
Newbie


Joined: 17 Feb 2010
Posts: 4

PostPosted: Thu Feb 18, 2010 3:54 am    Post subject: Reply with quote

Can anyone point me in the right direction with this? Any ideas ofn updating two tables with one form?
Back to top
View user's profile Send private message
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Thu Feb 18, 2010 5:49 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
Plastic1
Newbie
Newbie


Joined: 17 Feb 2010
Posts: 4

PostPosted: Sun Feb 21, 2010 3:08 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group