Form with Subform and autonumber field link

  • Thread starter Thread starter Bob Ryan
  • Start date Start date
B

Bob Ryan

For years I have used autonumber fields as primary keys in
access database tables and I have used those tables as the
record source for the forms I create.

Many times I create subforms that link to the primary key
field or the control that is bound to the primary key.

This has always worked very well and easy.

An example would be an orders form that has a subform for
order details where the datasourse for the main form is an
orders table that has an autonumber field for a primary
key which links to the subform from that primary key.


I am doing my first access "project" as opposed to mdb
using a sqlserver back end and I find this method does not
work.


With the sqlserver back end the identity field does not
get updated till after you save the record which seems to
be too late to use the subform to add more detail

How do other get around this situation?

Thanks
 
If I need to access the identity value of the new record
in code, I usually just forced it to write when I need it
i.e. Docmd.RunCommand accmdSaveRecord
I wish I knew of a better way. Perhaps someone else can
offer a better solution.
 
I also save the record. However, I found I had to add an
additional line of code to link the primary key to the
subform.

on the foreign key field on the subform I set the foreign
key field = to the primary key field on the main form.

Ex:

txtForeignKey = Forms!frmMain!txtPrimaryKey

I put the 2 lines of codes on various events and found for
me that if I put it on the After Update event of a field
that is always required on the subform it worked best.
 
Back
Top