Vurtual Subform

[Jacob JKW]

I have two tables: Institutions and Banks. Banks are a type of
Institution and the primary key of Institutions shows up a foreign key
in Banks.

I want to create an AddBank form which allows the user to enter data
specific to Institution record. When the record is saved the form
should first create a new entry in the Institutions table, save the
Institution specific data, and then use the primary key to create a new
entry in the Banks table.

The way I have done this would be by including a Banks subform on the
main AddBank form. This certainly works, but it's pretty poor design in
that the Banks subform is superfluous. The user doesn't need to enter
any data on that subform (except for an AutoNumber), all the relevant
data is just created from info entered in to the main form.

So any ideas on how to do that without creating the physical subform?


Thanks,
Jacob

 

[John Vinson]

I have two tables: Institutions and Banks. Banks are a type of
Institution and the primary key of Institutions shows up a foreign key
in Banks.

I want to create an AddBank form which allows the user to enter data
specific to Institution record. When the record is saved the form
should first create a new entry in the Institutions table, save the
Institution specific data, and then use the primary key to create a new
entry in the Banks table.

The way I have done this would be by including a Banks subform on the
main AddBank form. This certainly works, but it's pretty poor design in
that the Banks subform is superfluous. The user doesn't need to enter
any data on that subform (except for an AutoNumber), all the relevant
data is just created from info entered in to the main form.

So any ideas on how to do that without creating the physical subform?

I'm not sure I understand. There is NOTHING in the Banks table other
than an Autonumber? Or does the Banks table contain the same fields as
the Institutions table, redundantly?

One way you *can* do this is to base the Form on a query joining the
Institutions table to the Banks table, joining the Autonumber (?)
Institutions Primary Key to the Primary key (Long Integer) of the
Banks table. If you include both linking fields in the Query and enter
data into at least one field in the Banks table, it will automatically
fill in the linking field - you don't need a subform.

John W. Vinson[MVP]

 

[Jacob JKW]

I'm not sure I understand. There is NOTHING in the Banks table other
than an Autonumber? Or does the Banks table contain the same fields as
the Institutions table, redundantly?

Thanks for the reply, John.

The Banks table has an AutoNumber as its primary key and the primary
key from Institutions as a foreign key. There are additional columns
that accept data buy they wil never be updated when the recordf is
created.

One way you *can* do this is to base the Form on a query joining the
Institutions table to the Banks table, joining the Autonumber (?)
Institutions Primary Key to the Primary key (Long Integer) of the
Banks table. If you include both linking fields in the Query and enter
data into at least one field in the Banks table, it will automatically
fill in the linking field - you don't need a subform.

Only problem is that the record in the Banks table doesn't actually
exist when the Insitutions record is first created so Access won't as
it says, "enter value into blank on 'one' side of outer join."
Obviously it's possible I'm just doing something wrong, but it doesn't
seem that the Banks record just by mentioning it in a join.

Thanks,
Jacob,

 

[John Vinson]

Only problem is that the record in the Banks table doesn't actually
exist when the Insitutions record is first created so Access won't as
it says, "enter value into blank on 'one' side of outer join."
Obviously it's possible I'm just doing something wrong, but it doesn't
seem that the Banks record just by mentioning it in a join.

I guess what I don't understand is WHY you need to create a Banks
record *at all*.

If it has no data in it, what is the purpose of the existance of the
record? There is generally no reason to create empty 'placeholder'
records. Certainly just having a relationship defined will not
automagically create a "child" record.

Can you not simply use a Subform, and add data to the Banks table when
there is data that needs to be put into it?

John W. Vinson[MVP]

 

[Jacob JKW]

I guess what I don't understand is WHY you need to create a Banks
record *at all*.

If it has no data in it, what is the purpose of the existance of the
record? There is generally no reason to create empty 'placeholder'
records. Certainly just having a relationship defined will not
automagically create a "child" record.

You bring up a point with which I struggled for quite some time when I
first designed the DB. In reality there are several different types of
Institutions. Let's say there are Banks and Schools. Both Banks and
Schools have attributes in common ... let's just say they both have
addresses and they both have phone numbers. But they also have
attributes not in common ... Schools for example have have average SAT
scores, and Banks have interest rates. Now obviously the common
attributes would be referenced in the Institutions table, but the
dissimilar attributes would be referenced in the Subtypes (Banks or
Schools) tables.

Now this is the point. If you look at a record in the Institutions
table, how do you know what type of Institution it is? Now there are
two ways to handle this ... first way would be to place an identifier
in the Institutions table indicating the Subtype. If and when the user
needed to reference Subtype atributes a record would then be created in
the Subtype table. The other method, which is the method I chose, is to
leave off the identifier from Institutions and instantiate an
essentially blank record in the appropriate Subtype table as soon as
the Institution record is created. This way the identity of the Subtype
is not duplicated across the table. The identifier from the first
method is superfluous in that the Subtype can always be gleaned by
looking at the Institution's presence or absence in the appropriate
Subtype tables.

Anyway, the way I think I'll have to handle this (unless someone can
suggest a better solution) is in the Form's OnUpdate first do a DLookup
and check for the Institition in the Banks table, and if not present
create the record with a RunSQL. Messy, but it could be the only way?