Assign values to create unique index

[ShaunWillmott]

There are two resident types OR and TS, therefore I have created a Table with base Resident data and another tblResDetail which holds the Resident Type details. The tables have a one to one relationship based on ID and Type and the Resident Table ID is set to Autonumber, Resdetail Primary key is ID & Type.

The issue is when I add a new Resident. At 1st the ResDetail table record created with ResDetailID = 0 and Type correctly, so I created a Before Update event to assign ResDetailID = ResID and this is when I get the error Can't assign value to object.

What am I doing wrong?

I have read many other posts (which has increased my 4 week old knowledge), and tried one which assigned the Resdetail fields to to keys on the form as a default value, but this didn't work.

I can open the table and enter the correct ID without any error???

 

[AlanS]

I see at least two approaches to this:

1. Having two tables in a one-to-one relationship is only necessary in special cases: you need more fields than Access will allow in a single table, security considerations for splitting the table, substantial storage efficiency to be realized with two tables, etc. If you can't articulate a reason for having two tables in a one-to-one relationship, you probably don't need two tables, and will simplify your work (and the application) by combining them into one.

2. If you must have two tables, try creating a matching record in the second table in the AfterUpdate event for the form bound to the first table.

 

[ShaunWillmott]

AlanS,

Thanks, you were right, once I'd looked at why I was using the two tables, I realised that I didn't need them and combined all the fields into one table. This has made my life (and the app) much easier.

Cheers