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.
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 |
|
Guest
Posts: n/a
|
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 |
|
Guest
Posts: n/a
|
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
|
Guest
Posts: n/a
|
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
