I posted this problem a while back but I didn't get to the root of the problem in my explaination so hopefully this will get the problem on the right track to being solved. I have colored new information in blue.

We recently switched over to A2003. As far as my db everything went ok except for one thing.

I have a continuous form that we enter data into. When you open a new record the form only displays one empty row. Because we enter tons of rows that come from an AS400 system I made a button on the top of the form that switches from continuous form view to datasheet view.


Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub


Within datasheet view I am able to paste many rows at once. However, after the change to A2003 it still "appears" to work. Many rows are pasted in but if you exit the db and come back to the record all of the data is lost and you are back to one blank row in continuous form view.

When a record is added to the continuous form each record is assigned an autonumber primary key AND a Sequence PK (using PK for lack of a better term).

The db is used to track engineering changes (ECN's). There are 3 tables.

tblECNMain
tblECNDetail
tblECNParts

When you create a record for an ECN an autonumber ECN ID PK is created in tblECNMain. When you add dates to the ECN they are stored in tblECNDetail and the same ECN ID PK is assigned to that record. Where it got difficult was within the tblECNParts. There you have many records for 1 ECN record so I have an autonumber Part ID PK and the same ECN ID PK is also assigned to each record AND they must be displayed in a certain order (to maintain the BOM structure) so there is code to create a Part # sequence PK.

Here is the code to increase the Part PK.


Private Sub Form_BeforeInsert(Cancel As Integer)

Forms!frmECN.Seq = Forms!frmECN.Seq + 1
Me.Seq = Forms!frmECN.Seq

End Sub


Here is an example
ECN 1001 has ECN PK 1
ECN 1001 detail has ECN PK 1
ECN 1001 has part 2001 with Part PK 1 and ECN PK 1 and sequence PK 1
ECN 1001 has part 2002 with Part PK 2 and ECN PK 1 and sequence PK 2

ECN 1002 has PK 2
ECN 1002 detail has ECN PK 2
ECN 1002 has part 2001 with Part PK 3 and ECN PK 2 (not a mistake on part, a part can be on many ECN's) and sequence PK 1
ECN 1001 has part 2004 with Part PK 4 and ECN PK 2 and sequence PK 2

So as parts are added to the tblECNParts they have to not only have the appropriate ECN PK applied to them but the Part PK must keep increasing and the part sequencing PK must increase.

With the paste in Access 2003 the application of the increasing Part sequencing PK is failing.

frmECN is the form used to create all ECN records. It is a tabbed form and frmParts is the second tab where we enter the part records. It is normally displayed in continuous form view but I use the code above post to change to datasheet view for mass pasting of records.

My thought was that the part records were not getting written to the table and by using a Paste Append I could solve the problem but it didn't help. I tried it in and On Close and then in an AfterInsert event. Both locations gave me errors.

I have attached screen shots of the table design and some data in the table to hopefully give as good a picture as I can. The last field is the Part # sequence PK.