Synchronizing forms and autolookup in the case of 1-to-1 relations

[Wim]

Hi,

In my Access 2003 database I have a main table with general information and
several other tables that store specific information about subsets of records
from the main table. The tables all have a numeric primary key, always called
ID, and they are in 1-to-1 relationships with the main table with referential
integrity enforced.
General information is entered in the main table using a form, and for every
subset table there is a specific form. The specific forms are opened from the
main form using a command button. To make sure the specific form refers to
the same case as the main form I use the following code:
stLinkCriteria = “[ID] = “ & Me!ID
DoCmd.OpenForm stDocName, , , stLinkCriteria
That works fine as long as I want to display information that is already
there.
But what if I want to input new cases? After completing the main form, I
want the specific form to create a new record with the same ID. I do that by
using the OpenArgs argument, changing the command to
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , CStr(ID)
In the On-Activate-Event of the specific forms I put:
If Not IsNull(OpenArgs) Then
If IsNull(Me.ID) Then Me.ID = CLng(OpenArgs)
End If

I wonder if this is the best way to go about. I understand all this is
accomplished automatically if you use a subform (Link Child and Master
fields), but I find subforms very unpractical in the case of a 1-to-1
relationship. Is there a better way?

To make things easier for the user I want the specific forms to display some
of the information about the case from the main table/form; just displaying
it, without the possibility to change it. Again, this is no problem as long
as the form is showing existing records. I can base the specific form on a
query that includes the fields from the main table that I want to repeat and
put the field names in the Control Source of the controls.

But what if we are creating new records? What do I have to do to make sure
controls on the specific form show information (from the main table) about
the record whose ID has just been set in the Activate Event?
In the case of a 1-to-many relationship, I understand this can be done by a
so-called autolookup query, but what in the case of a 1-to-1 relationship?

I hope you can give me some suggestions on these two questions.

 

[Allen Browne]

I think you are subclassing here. That is, the record in the main table
contains the fields applicable to all the various types, and the other
tables hold the fields specific to the subtype (one-to-one.) That's a
relatively standard approach when the subtypes need lots of disparate
fields.

One suggestion would be to force the save of the current record before you
open the related form. Also, check there is a record. This kind of thing:
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Enter/select a record first"
Else
DoCmd.OpenForm ...
End If

An alternative approach might be to use subforms for the related records.
You might do this as a tab control, where the first tab page shows the
primary record from the main table, and each other tab pages show the fields
for one of the subtypes. The subform's LinkMasterFields/LinkChildFields
ensures that the link is correct, and the subform then inherits the ID from
the main form automatically.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In my Access 2003 database I have a main table with general information
and
several other tables that store specific information about subsets of
records
from the main table. The tables all have a numeric primary key, always
called
ID, and they are in 1-to-1 relationships with the main table with
referential
integrity enforced.
General information is entered in the main table using a form, and for
every
subset table there is a specific form. The specific forms are opened from
the
main form using a command button. To make sure the specific form refers to
the same case as the main form I use the following code:
stLinkCriteria = “[ID] = “ & Me!ID
DoCmd.OpenForm stDocName, , , stLinkCriteria
That works fine as long as I want to display information that is already
there.
But what if I want to input new cases? After completing the main form, I
want the specific form to create a new record with the same ID. I do that
by
using the OpenArgs argument, changing the command to
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , CStr(ID)
In the On-Activate-Event of the specific forms I put:
If Not IsNull(OpenArgs) Then
If IsNull(Me.ID) Then Me.ID = CLng(OpenArgs)
End If

I wonder if this is the best way to go about. I understand all this is
accomplished automatically if you use a subform (Link Child and Master
fields), but I find subforms very unpractical in the case of a 1-to-1
relationship. Is there a better way?

To make things easier for the user I want the specific forms to display
some
of the information about the case from the main table/form; just
displaying
it, without the possibility to change it. Again, this is no problem as
long
as the form is showing existing records. I can base the specific form on a
query that includes the fields from the main table that I want to repeat
and
put the field names in the Control Source of the controls.

But what if we are creating new records? What do I have to do to make sure
controls on the specific form show information (from the main table) about
the record whose ID has just been set in the Activate Event?
In the case of a 1-to-many relationship, I understand this can be done by
a
so-called autolookup query, but what in the case of a 1-to-1 relationship?

I hope you can give me some suggestions on these two questions.

 

[Wim]

Thanks, Allen and Marshall, for your help.

Yes, Allen, that is correct, I am using subclasses.
And it is exactly because this looks like a standard approach that I am
amazed by how little documentation I can find on this approach...

In fact I am already forcing the current record to be saved. After pushing
the command button on the main form it checks if information has been entered
in certain fields; if not, it sends a warning in a msgbox. Only when these
fields have been completed it closes the main (saving the record) and opens
the second form.

I am not sure what
If Me.Dirty Then Me.Dirty = False
is exactly doing. Can you help me on this?

But, anyway, saving the record in the main table is not enough to populate
the controls on the auxillary form.

As for subforms, I would like to avoid them. I have too many controls
already on each form. I am using tab controls with several pages on each of
the forms for the subclasses. Besides, subforms are much less flexible than
main forms.

Marshall, I am not sure if I understand the difference between placing the
code in the Load Event or in the On-Activate Event. Could you elaborate a bit
on that?

I put your code in the Load Event and it works OK.

But that still doesn't populate (autolookup) the other fields.
Any ideas on that?

 

[Allen Browne]

This line just forces the save of the current record:
If Me.Dirty Then Me.Dirty = False
If you using some other approach to ensure the record is saved, you don't
need this. (My preference for that approach is a) it works even if the form
does not have focus, and b) it generates a trappable error if the save
fails.)

But you are right: just creating a record in the main table does not
generate a related in any of the related table. Typically a subclassed
record belongs in only *one* of the related tables (not all 5), and there is
no way for Access to know which one this is. One way to do it would be to
include an unbound combo where the user chooses the appropriate type, and
you can deduce from that which related table the subclass record goes in.
You could then use the AfterInsert event procedure of the main form to
generate the record in the appropriate table.

 

[Wim]

Allen, thanks for your explanation.

The information about which related form/table to go to is actually in one
of the fields of the main table. This field is the control source of one of
the controls on the main form that have to be completed for the command
button to be able to save the record and jump to the correct form.

But once there, I want certain fields from the main table to be repeated
here - to help the user - even if we are in a new record. It seems this
cannot be simply done by means of bound controls (as would be the case in an
autolookup query); so does that mean that I have to use the DLookup function
or is there an easier way out?

Marshall, thanks for your information also. Very helpful.

 

[Paul Shapiro]

It is too bad that most databases have next-to-no support for subcategories
since it's such a useful design concept. I've always included the category
as an attribute in the main table (personTypeCode, or whatever) where it's a
FK from the category table (PersonType, e.g.).

If it works for this situation, you could use different main forms for each
of the 5 subcategories. Base each form on a query that links the parent
table with the appropriate subcategory table. Then when you add a new record
on the form Access will automatically add records to both the main and
subcategory tables. I think you could make this approach work with a form
and subforms too, but it would probably be more complex to get that working
reliably. With a single main form and separate subforms, I've usually done
this as others have suggested, with code adding (or removing) rows in the
appropriate subform. You probably need to respond to Undo events as well as
AfterUpdate. It can be simplified a bit if you don't let users change a
category on an existing row.

 

[Wim]

Hi Paul,

Thanks for your help.

I am not sure if your suggestion would work in my situation.
All my forms, both main and subclass forms, are single forms with quite a
number of fields on each. The main form and the subclass forms are completed
by different persons. In this situation it doesn't seem to be practical to
put everything on one form (on one of different main forms, that is). I am
afraid I will have to stick to one form with several subclass forms.

You say you have done this with "code adding/removing rows in the
appropriate subform". What code are you referring to? Or could you point me
to another source on this particular subject?

Thanks

 

[Allen Browne]

It is too bad that most databases have next-to-no support for
subcategories since it's such a useful design concept. I've always
included the category as an attribute in the main table (personTypeCode,
or whatever) where it's a FK from the category table (PersonType, e.g.).

It looks like the OP has done exactly that, Paul.

To be honest, subclassing is something I do only very rarely. It is a valid
approach, and probably the best solution for some cases. But in practice, I
find it messy to deal with, so I nearly always find another approach.

For example, I'm not keen on the idea of storing the subclass type as a
field in the main table. It seems to me that this is unnormalized, and could
lead to bad data (e.g. where the main table says it is type A, but the
related record is actually found in the table for type B.)

I'm not sure what to make of your observation of "next-to-no support for
subcategories." Does it mean that other developerss don't like to use them
either? Or does it mean they are messy to explain? Or ...?

 

[Paul Shapiro]

Every database modeling textbook devotes a great deal of attention to
subcategories, and that's because it's such a useful concept in logical db
design. All the db modeling tools support subcategories. But most DBMS's
don't support the concept. The developer is left to implement some kind of
messy solution. If Access natively supported subcategories, the developer
could specify the relationship type, and Access could automatically enforce
the appropriate contstraints. The Access form system could provide the
necessary infrastructure for working with subcategories, much as it does
today for 1:M relationships with forms and subforms. All we have to do as
developers for 1:M form-subform is specify the PK-FK master-child field
properties, and Access takes care of the rest. If it was that easy to work
with subcategories they'd be a lot more appealing.

 

[Wim]

Paul,

Please allow me to repeat my question:

You say you have done this with "code adding/removing rows in the
appropriate subform". What code are you referring to? Or could you point me
to another source on this particular subject?

Any help will be very much appreciated.

Every database modeling textbook devotes a great deal of attention to
subcategories, and that's because it's such a useful concept in logical db
design. All the db modeling tools support subcategories. But most DBMS's
don't support the concept. The developer is left to implement some kind of
messy solution. If Access natively supported subcategories, the developer
could specify the relationship type, and Access could automatically enforce
the appropriate contstraints. The Access form system could provide the
necessary infrastructure for working with subcategories, much as it does
today for 1:M relationships with forms and subforms. All we have to do as
developers for 1:M form-subform is specify the PK-FK master-child field
properties, and Access takes care of the rest. If it was that easy to work
with subcategories they'd be a lot more appealing.