Creating entry in subtable automatically?

[xhonzi]

Okay, this is a newbie question, but I can't seem to get this to work.

I have a master table and a sub-table, linked on a field called PRnumber. PRnumber is the autokey in both tables, I have created a one-one relationship with enforced integrity and cascading updates and deletes.

What I want is that when I create a new entry in the main table, that a corresponding entry is created in the sub-table. I can't seem to get that to work. The sub-table has default values set up for the important fields.

Any help?

xhonzi

 

[lespaul]

I don't think you've set up your tables correctly. You should have
[tt]
tblMaster
PRNumber (PK - Autonumber)


tblDetail
DetailID (PK - Autonumber)
PRNumber (FK - Long)
[/tt]

Additionally, in this kind of set up you would expect that there would be MANY details for each ONE master, but you say above you've enforced a ONE-to-ONE relationship. With that set up you can only have ONE detail record for each MASTER.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[xhonzi]

I didn't create these tables, I'm just maintaining them. There is a one-to-one relationship because it really should be one big table. However, like I said, I didn't design it and changing it at that level is out of the question.

I do believe there is a way to make it work with its current design. If I'm wrong about that, then I'll go from there.

xhonzi

 

[Crowley16]

splitting the tables out this way with a 1-1 relationship is a valid way of implementing a really large table however access tables allow 250 columns so it's hard to imagine a situation where a normalised structure would require more columns than that in 1 table...

anyway, msaccess doesn't have triggers (unless you're on adp), so what you are asking for cannot be done at the table level...

however you can setup a function or sub to do both the inserts and always use that function/sub to insert data...

--------------------
Procrastinate Now!

 

[xhonzi]

Roger. Will add a little VBA to insert a new row in the sub table.

Thanks.