Working with subforms? 1 to 1 relationships

[Guest]

Hi,
I think I have a fairly common problem. In my database, there are a number
of tables all linked on a 1 to 1 basis with the main table. In the same
mannor, there are various forms for the different tables.
I am wanting to link all the forms and tables so that they work with '1
apparent record'. The best way seems to be with subforms? but I cannot find
any practical way to go about this.
Please help
Thanks

 

[Guest]

In addition.... When an ID is entered on the main form, and not in the sub
form, is it possible to automatically add an entry into the subform with the
specific ID? It's driving me nuts....
Thanks!

 

[Rick Brandt]

In addition.... When an ID is entered on the main form, and not in
the sub form, is it possible to automatically add an entry into the
subform with the specific ID? It's driving me nuts....
Thanks!

It would be poor design to do so. If you need a subrecord then enter something.
Creating blank "place holder" records is not a good way to do this. If you are
going to do that then you might as well use one big table.

The usual point of a 1-1 relationship is when you have fields that will be empty
often enough to make putting them in a separate table worthwhile, thus avoiding
lots of fields with null in them. If you create blank sub-records then you are
right back to having lots of null fields.

 

[Roger Carlson]

Assuming you have a good reason for having the data in many tables with a
1:1 relationship, I'd day that subforms would be the way to go. In fact, I
have done so on those few times when I've had 1:1 relationships. What
specific problems are you having?

General guidelines:
1) Designate one table as the Parent and give it an Autonumber primary key
2) Make the foreign key in each of the Child tables Long Integer (not
autonumber) and also make it the Primary key.
3) Set the relationships in the Relationship Window.
4) When you put your subforms on the main form, make sure to set the Link
Parent/Link Child properties correctly
5) Set the subforms to Single Form view by default

When you enter data in the main form, no record will appear in the subforms
until you enter data into them (which is proper). Once you do begin
entering data, the subform control will automatically insert the foreign key
value into the child record. If you set the subform default view as Single
Form, your users won't be able to attempt to enter a second record in the
subform.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L