Using a single form to enter data into two related tables

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I am using A97.

I need to create two tables, [payments] and [equipment].

The idea is that [payments] contains various details about all the payments
that are made, some of which are for items of equipment, and for those
payments that are for equipment I need to record further information about
the equipment (serial number, make, model number, warranty period, etc.).
The equipment information should be stored in table [equipment].

I know how to set up the tables (I think!: primary key in table [payments]
joined one-to-many to foreign key field in table [eqiopment]), but my
difficulty is that I want the user to have a single form to enter data into
both tables, such that if (and only if) they enter any data in (say) the
'equipment_serial' or equipment_make' fields - or any of the fields from
table [equipment] - then a new record is created in table [equipment], with
the corresponding value in the foreign key field that is related to the
primary key field in table [payments].

I hope I have explained this adequately, and that someone can help.
Thanks
Les
 
Hey, I TOO would like to know how one form can put the data into two seperate
tables...I mean, Microsoft is an enormous and enormously rich company and I
figured Access could do it, but no one has yet to explain that to me.

So I figured out a trick of my own. Okay, let's say I have an "Equipment"
and "Payment" table like you do. What I'd do is make one form through the
wizard, and then choose what I want from the Equipment table first, then
click on the drop down box in the wizard and choose some fields from the
Payment table. Then just finish up the wizard and take a look.

It makes for a somewhat ugly form but it's the inner form beauty that makes
it work--it may be ugly and long but it puts the right info into the right
field spots on the right tables. Like ok lets say these are the field spots
here:

Equipment - Amount Due
Eq - Amt Paid
Eq - Intrest
Payment - Source
Pay - Funds From
etc etc...ok the form will throw all the Equipment stuff into that table
because those fields came from that table.

I hope this helped some...hope it didn't confuse you worse! I'm new at this
(I'm not even a computer guy but I still get this *fun* assignment) so if I
am wrong, I apologize. It works on my acc97 but I might not have explained
proper.
 
hey what IS a subform? how do I make one? what can it do for me? yes, i'm
new to this and yes i'm a bit dense

SusanV said:
Use a main form for the payments, and a subform for the equipment.

Leslie Isaacs said:
Hello All

I am using A97.

I need to create two tables, [payments] and [equipment].

The idea is that [payments] contains various details about all the
payments
that are made, some of which are for items of equipment, and for those
payments that are for equipment I need to record further information about
the equipment (serial number, make, model number, warranty period, etc.).
The equipment information should be stored in table [equipment].

I know how to set up the tables (I think!: primary key in table [payments]
joined one-to-many to foreign key field in table [eqiopment]), but my
difficulty is that I want the user to have a single form to enter data
into
both tables, such that if (and only if) they enter any data in (say) the
'equipment_serial' or equipment_make' fields - or any of the fields from
table [equipment] - then a new record is created in table [equipment],
with
the corresponding value in the foreign key field that is related to the
primary key field in table [payments].

I hope I have explained this adequately, and that someone can help.
Thanks
Les
Click to expand...
Click to expand...
 
No problem, I'm not an expert but I've been "dabbling" a while and am glad
to help.

Create your main form from the main table, say, Payments. Open the Main form
in design view, and on the toolbar click on the subform control then on the
main form to place it. The wizard will walk you through it, select the table
for your subform (Equipment in this example) or a previously designed form.
Just make sure that before you do this you have the relationship setup
properly and choose the option that matches the records by the related
fields.

That's probably not very clear, so here's a link for a tutorial with
definition and how to etc:
http://www.fgcu.edu/support/office2000/access/subforms.html

Good luck!

SusanV

Mico said:
hey what IS a subform? how do I make one? what can it do for me? yes,
i'm
new to this and yes i'm a bit dense

SusanV said:
Use a main form for the payments, and a subform for the equipment.

Leslie Isaacs said:
Hello All

I am using A97.

I need to create two tables, [payments] and [equipment].

The idea is that [payments] contains various details about all the
payments
that are made, some of which are for items of equipment, and for those
payments that are for equipment I need to record further information
about
the equipment (serial number, make, model number, warranty period,
etc.).
The equipment information should be stored in table [equipment].

I know how to set up the tables (I think!: primary key in table
[payments]
joined one-to-many to foreign key field in table [eqiopment]), but my
difficulty is that I want the user to have a single form to enter data
into
both tables, such that if (and only if) they enter any data in (say)
the
'equipment_serial' or equipment_make' fields - or any of the fields
from
table [equipment] - then a new record is created in table [equipment],
with
the corresponding value in the foreign key field that is related to the
primary key field in table [payments].

I hope I have explained this adequately, and that someone can help.
Thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Hey, I TOO would like to know how one form can put the data into two
seperate tables...I mean, Microsoft is an enormous and enormously rich
company and I figured Access could do it, but no one has yet to
explain that to me.
Click to expand...

Well, that's because there isn't just one way to do it. The best method
really depends on what your users need to do with the data; on what the
information is and where it comes from; and on the particulars of the
database schema. You know when you have done a good job of the system
analysis, because the user interface becomes glaringly obvious and easy.
You know when you haven't done it right because you get stuck wondering
how to set up your forms.

One possibility is to create a query based on a join between the relevant
tables, and to base the form on that query. It takes some care to ensure
that the query is updateable in the way that you expect.

Another possibility is to use a form/ subform design: you design a form
on each table, and embed the "child" table form inside the "parent",
usually as a continuous-forms. Access itself witill handle the linking
and foreign keys etc.

Yet another approach is to use a succession of custom dialogs, for
example to "add an address" or whatever it may be. With more ambitious
programming skills, you can use listboxes rather than (highly non-
standarad) subforms; and even aim for a full drag-and-drop interface.
Some things like this are actually easier in VB than Access, but that is
pretty much horses for courses.

So the answer is "yes, Access can -- now what is the question?"

Hope that helps



Tim F
 
Back
Top