Copy Record Problem

[Joe Williams]

I have an access 2003 database with an SQL backend.

I have a form based on a table, with a combo box in the form header for the
user to select the specific record they want to jump to. Works fine with no
problems. I then added a COPY record button using the access wizard so the
user could duplicate the record and save with only changing a couple fields.

Whenc licking COPY, I receive a nice long error message as follows:

Run Time Error '3146' ODBC -- Call Failed [Microsoft][ODBC SQL Server
Drive][SQL Server]Cannot insert the value NULL into column 'Last Update',
table 'ElmoreData.dbo.PROCESS1530'; column does not allow nulls. INSERT
fails. (#515) [Microsoft][ODBC SQL Server Drive][SQL Server]The statement
has been terminated. (#3621)

The options then are END or DEBUG.

Now, several issues. 1) I know that the data being copied contains no NULL
values prior to clicking COPY. 2) If I just click END the data seems to copy
correctly, so why the error? 3) If I remove the combo box from the form
header, the problem goes away and the record copies fine.

Can anyone tell me why adding the combo box into the form header would make
the COPY button code fail?

I have experimented with changing the rs.findfirst method to the
docmd.findrecord methods with the same results.

Can anyone offer any advice?

Thanks

Joe

 

[Marshall Barton]

I have an access 2003 database with an SQL backend.

I have a form based on a table, with a combo box in the form header for the
user to select the specific record they want to jump to. Works fine with no
problems. I then added a COPY record button using the access wizard so the
user could duplicate the record and save with only changing a couple fields.

Whenc licking COPY, I receive a nice long error message as follows:

Run Time Error '3146' ODBC -- Call Failed [Microsoft][ODBC SQL Server
Drive][SQL Server]Cannot insert the value NULL into column 'Last Update',
table 'ElmoreData.dbo.PROCESS1530'; column does not allow nulls. INSERT
fails. (#515) [Microsoft][ODBC SQL Server Drive][SQL Server]The statement
has been terminated. (#3621)

The options then are END or DEBUG.

Now, several issues. 1) I know that the data being copied contains no NULL
values prior to clicking COPY. 2) If I just click END the data seems to copy
correctly, so why the error? 3) If I remove the combo box from the form
header, the problem goes away and the record copies fine.

Can anyone tell me why adding the combo box into the form header would make
the COPY button code fail?

The wizard generated copy record code has all kinds of
issues and only works in the simplest situations. Instead,
you should use code to copy and set individual fields as
needed. Here's an example of the kind of DAO code you can
use:

With Me.RecordsetClone
.AddNew
'copy some/most of the fields on form
!thisfield = Me.thisfield
!thatfield = Me.thatfield
!otherfield = Me.otherfield
. . .
'set values of fields that should not be copied
!createdfield = Now
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Joe Williams]

Thanks Marshall,

Is there any other code I could use that would do the copy all at once
without specifying the individual fields? The particular form I am speaking
about is a process paramter database for our machies and each one has about
200 variables. Makes it very painstaking to enumerate each field in code...

Thanks

joe

I have an access 2003 database with an SQL backend.

I have a form based on a table, with a combo box in the form header for
the
user to select the specific record they want to jump to. Works fine with
no
problems. I then added a COPY record button using the access wizard so the
user could duplicate the record and save with only changing a couple
fields.

Whenc licking COPY, I receive a nice long error message as follows:

Run Time Error '3146' ODBC -- Call Failed [Microsoft][ODBC SQL
Server
Drive][SQL Server]Cannot insert the value NULL into column 'Last Update',
table 'ElmoreData.dbo.PROCESS1530'; column does not allow nulls. INSERT
fails. (#515) [Microsoft][ODBC SQL Server Drive][SQL Server]The statement
has been terminated. (#3621)

The options then are END or DEBUG.

Now, several issues. 1) I know that the data being copied contains no NULL
values prior to clicking COPY. 2) If I just click END the data seems to
copy
correctly, so why the error? 3) If I remove the combo box from the form
header, the problem goes away and the record copies fine.

Can anyone tell me why adding the combo box into the form header would
make
the COPY button code fail?

The wizard generated copy record code has all kinds of
issues and only works in the simplest situations. Instead,
you should use code to copy and set individual fields as
needed. Here's an example of the kind of DAO code you can
use:

With Me.RecordsetClone
.AddNew
'copy some/most of the fields on form
!thisfield = Me.thisfield
!thatfield = Me.thatfield
!otherfield = Me.otherfield
. . .
'set values of fields that should not be copied
!createdfield = Now
. . .
.Update
Me.Bookmark = .LastModified
End With

 

[Marshall Barton]

Since you probably do not want to copy every control/field
(e.g. autonumber field, datecreated fied, etc), you will
need some way to identify which fields to copy. Assuming
the fields to be copied are bound to a control, a convenient
way to do that is to use each control's Tag property. Let's
say you set the Tag for ones to copy to COPY, then you can
use a loop:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "COPY" Then
.Fields(ctl.ControlSource) = ctl.Value
End If
Next ctl
--
Marsh
MVP [MS Access]

Is there any other code I could use that would do the copy all at once
without specifying the individual fields? The particular form I am speaking
about is a process paramter database for our machies and each one has about
200 variables. Makes it very painstaking to enumerate each field in code...

I have an access 2003 database with an SQL backend.

I have a form based on a table, with a combo box in the form header for
the
user to select the specific record they want to jump to. Works fine with
no
problems. I then added a COPY record button using the access wizard so the
user could duplicate the record and save with only changing a couple
fields.

Whenc licking COPY, I receive a nice long error message as follows:

Run Time Error '3146' ODBC -- Call Failed [Microsoft][ODBC SQL
Server
Drive][SQL Server]Cannot insert the value NULL into column 'Last Update',
table 'ElmoreData.dbo.PROCESS1530'; column does not allow nulls. INSERT
fails. (#515) [Microsoft][ODBC SQL Server Drive][SQL Server]The statement
has been terminated. (#3621)

The options then are END or DEBUG.

Now, several issues. 1) I know that the data being copied contains no NULL
values prior to clicking COPY. 2) If I just click END the data seems to
copy
correctly, so why the error? 3) If I remove the combo box from the form
header, the problem goes away and the record copies fine.

Can anyone tell me why adding the combo box into the form header would
make
the COPY button code fail?

The wizard generated copy record code has all kinds of
issues and only works in the simplest situations. Instead,
you should use code to copy and set individual fields as
needed. Here's an example of the kind of DAO code you can
use:

With Me.RecordsetClone
.AddNew
'copy some/most of the fields on form
!thisfield = Me.thisfield
!thatfield = Me.thatfield
!otherfield = Me.otherfield
. . .
'set values of fields that should not be copied
!createdfield = Now
. . .
.Update
Me.Bookmark = .LastModified
End With