1 to 1 relation problem

[Newman]

Hello,
I had this huge table with over 200 fields.
Since the form related to this table has 3 tabs, I thaught it could be usefull to divide my table into 4. One for the main form and one for each tab.
I made 1 to 1 relations with the primary key that I brought to every tables. I use the key of the main table (an autonumeric field) for my form.
Now, when I create a new record in my main form, it says that I cannot add this record 'cause the corresponding record is needed in the main table.
???I can't add a record unless it has already been added???

I tried a lot of things and nothing works.
I can't find out where the problem lies.
Should I come back to a single table?
Thank you for your help!
Newman

 

[sambo]

If you are adding the record to the SubForm, then you will need to tell the Form which element of the MainForm it should link the new SubForm Record to.
Confused Yet?!

In the On Insert Event of the SubForm, add this bit of code

Me.MainID = Forms!MainForm!MainID   'This sets the foreign key value in your subfrm table to be the same as the current record on the MainForm

MainID is the Primary Key of MainTable and the Foreign Key of SubTable.
Using this method will properly relate the two.
Hope this helps..

 

[Spawn]

The problem itslef lies in the one-to-one relationship. Both sides are classed as mandatory. For example you have 2 tables Stock and Supplier. There is a 1-1 relationship between them. A Supplier can not exist without a Stock and a Stock can not exist without a Supplier, so if you try to enter a new record for either side, you will violate validation. Unfortunately i can not help, as i have the same problem myself, any help will be greatly appreciated.

 

[Newman]

Sambo > Subform? I have tabs! Thanks, but no need for your code.

Spawn > Lunch time. I'll take a look at it right after.

Newman

 

[sambo]

You can call it a tab, or a subform, or whatever you want. The moral of the story is... If you don't properly relate a new record in the SubDataSheet (or SubTable) then you will not be able to enter new records at all.
That is how 1 to 1 works. The rules of referential integrity clearly state that you cannot enter a new record in the second-level hierarchy if you have not properly related the second-level (SubDataSheet Record) to the Top-Level Record.
You need to make your primary key in the Second-Level table of type Number, not AutoNumber. So, whenever you create a new entry in the Top-Level Table, you need to create a record in the SubData Table with the exact same Primary Key Value.

Like this...

Public Function addSubData()
'Function to Append SubData to a Given Top-Level Table
'This is a One to One Relationship, based on Top-LevelID and SubDataID
'Two tables were used because of the great number of fields required for the Top-Level table

 Dim db As DAO.Database, rst As DAO.Recordset
 
'Procedure to Add SubDataID("tblSubData")
 Set db = CurrentDb
 Set rst = db.OpenRecordset("tblSubData")
 
 With rst
    'Add the SubDataID data for the corresponding Top-Level (1:1) 
    .AddNew
    !SubDataID = Me.TopLevelID 'TopLevelID is the newly created ID on your MainForm
    SubDataID is NOT an auto increment field, instead it gets its value from TopLevelID
    'This is to ensure One to One
    .Update
 End With
 
 rst.Close
 db.Close
End Function

You can call this function in order to add the corresponding 1:1 data wherever you see fit. If you want to call whenever a new record is added (suggested) then Call it in the After Insert Event of your MainForm. Doing this will ensure that you get the Current TopLevelID and match it to your newly created SubDataID

Good Luck..