multi-table forms....

[contay]

I am trying to create a form containing information from 3 tables which
are linked.
Invoice, Member, Vendor.

Invoice contains the memberId & vendorId (primary keys in the other
tables).

what is the best approach? subforms?

I tried dumping all the information into one form and have my own
custom add/find/next, etc buttons, but I run into certain errors
(trying to add) pertaining to the table relationships. For example
"The Microsoft Jet database engine cannot find a record in the table
'Members' with key matching 'Invoice_memberId'".

questions...when I have duplicate fields (the member id in the invoice
table, and the member id in the members table), which one do I display
in the form? I understand the resons behind the above error, but it
seems like i am not given a chance to fill everything out before it
decides to complain. If i continuously tab through the fields without
any type of field validation, it works. However, if someone tries to
tab past a field without entering information, and I present a warning
and set focus back to that field (any field..let's say invoice date),
it then decides to complain about the relationship.

basically, I am just trying to create a simple form connecting three
tables, and allow for records adds, etc and have cutomized buttons,
etc...i would apprecaite it if someone could point me in the right
direction. Having read a few posts, people seem to advice using
subforms. Let's say I take that approach and create a main form with
all the fields from the invoice table. When I create the subforms for
members and vendors, do I include the primary keys in that form? In
which case wouldn't I end up with duplicate fields on the form as a
whole?

Appreciate any help out there! Thanks!

 

[John Vinson]

what is the best approach? subforms?

Yep. Form for the "one" side, subform for the "many" - with perhaps a
sub-subform if needed.

John W. Vinson[MVP]

 

[John Vinson]

On 10 May 2006 20:33:36 -0700, (e-mail address removed) wrote:

A more detailed answer than yesterday's overly terse reply... comments
inline.

I am trying to create a form containing information from 3 tables which
are linked.
Invoice, Member, Vendor.

Related how: I presume Member --1:n--> Invoice, Vendor --1:n-->
Invoice?

Invoice contains the memberId & vendorId (primary keys in the other
tables).

what is the best approach? subforms?

A couple of possibilities. If the Member and Vendor information is
already stored, you could simply have a Form based on the Invoice
table with combo boxes for the MemberID and VendorID, storing the ID
but displaying a human-meaningful name.

If you want to enter multiple invoices for a given Member (or a given
Vendor), you can use a Form based on that table, with a Subform based
on the Invoice table. You'ld use the MemberID (or VendorID) as the
master/child link field.

I tried dumping all the information into one form and have my own
custom add/find/next, etc buttons, but I run into certain errors
(trying to add) pertaining to the table relationships. For example
"The Microsoft Jet database engine cannot find a record in the table
'Members' with key matching 'Invoice_memberId'".

Well... that's the HARD way to do it, and unnecessary as well.

questions...when I have duplicate fields (the member id in the invoice
table, and the member id in the members table), which one do I display
in the form?

Generally you *wouldn't* display the numeric ID fields, at all.
Neither instance of them is needed for display. If you use the
MemberID as the Master/Child Link Field, Access will automatically
fill it in; if you use it as the Bound Column of a combo box, with the
combo's ColumnWidths property set to have that field width 0", then it
will store the ID while displaying the name.

The ID is meaningless to the user. The computer needs to know it, but
the user will most likely be much better off seeing "Doakes, Jane" on
a combo box; he knows who she is, but typically would not know
anything about MemberID 3318.

basically, I am just trying to create a simple form connecting three
tables, and allow for records adds, etc and have cutomized buttons,
etc...i would apprecaite it if someone could point me in the right
direction. Having read a few posts, people seem to advice using
subforms. Let's say I take that approach and create a main form with
all the fields from the invoice table. When I create the subforms for
members and vendors, do I include the primary keys in that form? In
which case wouldn't I end up with duplicate fields on the form as a
whole?

Take a look at the Orders form in the Northwind sample database. It
will give you an idea how this kind of situation can be handled.

Ordinarily you have at least SOME information about vendors and
members before you start creating invoices... don't you?

John W. Vinson[MVP]

 

[contay]

thank you very much for a more detailed response...haha...
ok..to answer a few of your questions...Invoice is my 'main' table.
This is the main drive behind my form. I want people to be able to
look up invoices, or enter new ones. The purpose behind the member and
vendor table is to bring up all their information if they already
exist. So I do need the member id & vendor id (my system specific
id's, not auto generated id's by access)....so, I do need to display
these fields. So the intent behind my form is not to enter multiple
invoices for a member...but more invoice driven...to view them...or add
a new one...and potentially enter an existing member id or vendor id to
have the rest of their information automatically filled out.

so given my situation...create member and vendor subforms with all
their info in...then create a invoice form and add the other two as
subforms? ok...so I understand that I will have to have the member id
and vendor id fields in the invoice form displayed...because it's in
those fields that if one enters a value that exists in the other tables
that causes that information to autamtically be filled out in the
subforms. however, it would be nice (design wise) to group the member
id and vendor id with the member & vendor specific information...but
you are not allowed to move a field from a form into a subform for
display purposes. In other words, I cannot position the member id from
the invoice form with the member information from the member subform.

anyways...I think I'm rambling...but if you (or anyone else) has any
suggestions or has run into anything similar, I would greatly
appreciate any tips/advice...thanks again