Adding a Record to Tables in a One to One Relationship

Problem: If a table has too many fields, or has a record width greater than 2048 bytes, you can connect them in a One to One relationship.  You can Enforce Referential Integrity between the tables based on a Primary Key.  This will take care of Editing or Deleting the records, and automatically change the Field value in the Primary Key to match both tables.  But how can we Add records to this setup? 

If we add a record to Table1, it does not automatically add a record to Table 2, even though they are linked on a Primary Key and Referential Integrity is enforced.  Access does not assume you want to add to the other tables.

Here is an example below from the Hopewell Township project.  They track information from properties in their town in New Jersey.   It has 5 main tables:
BLOCKLOT
ENVIRONMENTAL
INFO
PERMIT
WATERINFO

Thing are so much easier when all the fields are in one table.  But this was not possible after repeated attempts.  There were simply too many fields to put them in one table, as Access can only accommodate 255. All the fields were important, and different.   In addition, the row size was much more than 2048, even after trimming a lot of fields, and converting as many as possible to Memo fields.

For data entry and editing, the tables are connected by a form, designed to look similar to the Q&A form the staff was used to.


Click to enlarge

Trying AutoNumber

I next converted the field Recno in Table1 from Integer to AutoNumber.  I could not simply change the data type.  I had to add a new field called Recno2 as an AutoNumber, and let Access populate it.  Then I renamed Recno to Recno1, then renamed Recno2 to Recno.

This resulted in some problems:

1. Even though I had the records sorted, the Autonumbering did not make them the same as the original.  This would result in some data problems because I would need to make sure the numbers were consistent in the other tables in the One to One relationship.

2. Access would allow me to Enforce Referential Integrity, but would not allow Cascade Delete or Cascade Update.  This is because an AutoNumber field cannot be changed.  It should not need to, once created. But this would make it difficult to delete an unwanted record.  It would have to be done manually on each of the 5 tables.

I looked in Google for "Microsoft access add record one to one relationship tables" and found some good ideas.

http://www.pcreview.co.uk/forums/thread-1165430.php

08-09-2003, 04:15 PM   #2
Rick Brandt
Guest
 
 
Posts: n/a
 
Default Re: Doesn't 1:1 automatically generate record in foreign key table?

Relationships do not automatically create records. The closest behavior to this is
if you use a mainform/subform combination to make entries in two tables with a
one-to-many relationship. In that scenario the MasterLink/ChildLink properties of
the subform control will automatically cause the foreign key of the child records to
pick up the value from the main form without the developer or the user having to do
anything to make it happen. While the foreign key value is automatically assigned,
the record still has to be created explicitly.

Relationships can cause related records to be automatically *deleted* by using
cascade delete in the relationship properties, but they are never automatically
created.
 

After Insert command to create records in other tables

08-09-2003, 04:43 PM   #4
Van T. Dinh
Guest
 
 
Posts: n/a
 
Default Re: Doesn't 1:1 automatically generate record in foreign key table?



--
HTH
Van T. Dinh
MVP (Access)

No. They are related but still separate Tables.

With regards to tblVisits + tblBillingInfo, your Form is probably based on a
Query combining the 2 Tables and when you added a "row" on the Form, you
actually add 1 Record to each Table.

You can use the Form_AfterInsert Event of the Form you use to add Record
into tblVisits to add a related (dummy/nearly blank) Record into the
tblPayments by code if you wish to.

However, I would be wary of the One-to-One relationship. There are not many
One-to-One relationships in most database applications.

HTH
Van T. Dinh
MVP (Access)

Insert Into command

Van T. Dinh
Guest
 
 
Posts: n/a
 
Default Re: Doesn't 1:1 automatically generate record in foreign key table?

In the Form_AfterInsert Event, use something like (not tested):

DBEngine(0)(0).Execute "INSERT INTO tblPayments (VisitID) " & _
" VALUES (" & Me.txtVisitID & ")
", dbFailOnError

Check Access Help on the Append Query / Insert Into... SQL statement. Check
Access VB Help on the Execute Method of the Database Object.

You know relationships for your Tables best but I visited a doctor once and
I got 4 bills (1 for the consultation where the doctor took some sample
tissues, one for the pathology to do some tests, one for the specialist to
look at the pathology results and the last one for the original doctor to
interpret the specialist's report).

OTOH, I might decide I don't have enough money (you know Access developers
don't usually have enough money!) and there for make a number of
part-payments to each bill. I didn't but it is a possible scenario and you
may need to cater for this!
--
HTH
Van T. Dinh
MVP (Access)

 

Solution: Add Record Button

It was decided to put an Add Button on the form, which would simultaneously add a record to each of the 5 tables, and assign the same RecNo.

Private Sub bAddRecords_Click()
'On Error GoTo Err_bAddRecords_Click

'Add a record to all tables at once
nRecordsNow = Me.RecordsetClone.RecordCount
nNextRecord = nRecordsNow + 1

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Next Record Number will be: " & nNextRecord ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Add Record to Block & Lot" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
     DoCmd.GoToRecord , , acNewRec
    Me.RECNO = nNextRecord
    Me.ENVIRONMENTAL_recno = nNextRecord
    Me.INFO_recno = nNextRecord
    Me.PERMIT_recno = nNextRecord
    Me.WATERINFO_recno = nNextRecord
    Me.Refresh
Else ' User chose No.
    MyString = "No" ' Perform some action.
End If

Exit_bAddRecords_Click:
Exit Sub

Err_bAddRecords_Click:
MsgBox Err.Description
Resume Exit_bAddRecords_Click

End Sub
 


AccessHelp.com