A
Autoeng
Sorry for such a long post.
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.
Code:
--------------------------------------------------------------------------------
Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub
--------------------------------------------------------------------------------
Within datasheet view I am able to paste many rows at once (by left clicking
on the top left corner of the datasheet and then right clicking - Paste) .
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. I think that this is where it is failing. I think I
figured out the problem (but not the solution) but I will have to provide a
little background first.
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.
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
ECN 1001 has part 2002 with Part PK 2 and ECN PK 1
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)
ECN 1002 has part 2004 with Part PK 4 and ECN 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.
With the paste in Access 2003 the application of the increasing Part PK is
failing.
Here is the code to increase the PK.
Code:
--------------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Forms!frmECN.Seq = Forms!frmECN.Seq + 1
Me.Seq = Forms!frmECN.Seq
End Sub
--------------------------------------------------------------------------------
I didn't write this code but when I first created the db (my first one)
hired a programmer to make it work properly. 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 we use the code in first post to change to datasheet view for mass
pasting of records.
frmECN.Seq is a hidden text field on the first tab of the data entry form.
On the first tab of the data entry form is where you enter the ECN #
(parent). At that point an autonumber ECN ID PK is created so the record can
be saved. The children are entered on the second tab so that the parent is
saved (or should be) by that point.
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 an On Close and then in an AfterInsert event. Both locations gave
me errors.
The reason I don't just change over to a transferspreadsheet method is that
the pasted data is coming from a copy of an AS400 screen not a true
spreadsheet.
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.
Code:
--------------------------------------------------------------------------------
Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub
--------------------------------------------------------------------------------
Within datasheet view I am able to paste many rows at once (by left clicking
on the top left corner of the datasheet and then right clicking - Paste) .
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. I think that this is where it is failing. I think I
figured out the problem (but not the solution) but I will have to provide a
little background first.
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.
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
ECN 1001 has part 2002 with Part PK 2 and ECN PK 1
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)
ECN 1002 has part 2004 with Part PK 4 and ECN 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.
With the paste in Access 2003 the application of the increasing Part PK is
failing.
Here is the code to increase the PK.
Code:
--------------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Forms!frmECN.Seq = Forms!frmECN.Seq + 1
Me.Seq = Forms!frmECN.Seq
End Sub
--------------------------------------------------------------------------------
I didn't write this code but when I first created the db (my first one)
hired a programmer to make it work properly. 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 we use the code in first post to change to datasheet view for mass
pasting of records.
frmECN.Seq is a hidden text field on the first tab of the data entry form.
On the first tab of the data entry form is where you enter the ECN #
(parent). At that point an autonumber ECN ID PK is created so the record can
be saved. The children are entered on the second tab so that the parent is
saved (or should be) by that point.
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 an On Close and then in an AfterInsert event. Both locations gave
me errors.
The reason I don't just change over to a transferspreadsheet method is that
the pasted data is coming from a copy of an AS400 screen not a true
spreadsheet.