autonumber linked tables

[krc777]

I have a MAIN table with autonumbering for the project number. In a table related to the MAIN table I have a project number that I want to be updated when the MAIN table has a new project entered. Can I keep these in sinc automatically?

 

[aikea]

Could you give me more info about your table defination. I never use autonumber in this way.

Normally, set "cascade" as reference integrity rule will make sure if a record is changed, its corresponding record in other table is updated.

In a more proper database product you can set a "Trigger" that respones to event, but I am not sure this feature is available in Access.

 

[Dennisk]

If you set up a Main and subform, with the main form for projects. If you have set up correct referntial integrity then the forign key will be automatically generated for on the sub form.

 

[krc777]

The relationship between the Main table and Deliv table is set to the primary key in Main (proj_No) and then to Proj_no in the linked (Deliv) table. The cascade is set on to both add and delete. In the form the Main form and sub link correctly but when I try to add a new record the subform stays at autonum. In reading about this at the ms site it looks as if this set-up should automatically insert a proj_no to match that entered into Main on the form into deliv.

 

[Dennisk]

Daft question, but it does happen. Are you trying to add a subform record without a mainform record?

 

[boblarson]

and then to Proj_no in the linked (Deliv) table.

So, is Proj_no in your linked (Deliv) table a Long Integer and NOT the primary key?

The cascade is set on to both add and delete.

Wrong - it is set to UPDATE and DELETE there is no "cascade ADD" (look again). It will cascade any updates or deletions but just setting the relationship will NOT add a record automatically to the table. You have to set up the Master / Subform links properly and have the linked field in both the main form recordsource and subform recordsource. If you do that, THEN the subform control will automatically add the data for the foreign key.

 

[krc777]

Yes. The deliv table's proj_no is a long integer and not a primary key. If I'm understanding this correctly "You have to set up the Master / Subform links properly and have the linked field in both the main form recordsource and subform recordsource."
In the source object for the subform the child field is deliv.proj_no and the master main.proj_no. The recordsource for both tables is set to Main linked to Deliv.