Duplicating a record leaving one field blank for editing

[Uni]

Hi - I've seen many different posts regarding duplicating a record - I am
able to do this fine, however I do have a need to leave one of the fields
(Position.NewLineNo) blank so that the user can fill it in. This field is
not a primary key - I have a PositionID field which uses the autonumber
feature. Since my VBA skills are not very advanced I'm looking for a
solution to leave the field blank. I've included the code that I am using.
This code is using a cmd button with the OnClick() event:

Private Sub cmdDuplicateRecord_Click()
On Error GoTo Err_cmdDuplicateRecord_Click


DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_cmdDuplicateRecord_Click:
Exit Sub

Err_cmdDuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDuplicateRecord_Click

End Sub

I'm sure there is a simple way to do this but I am not sure what it might be

Thanks in advance for any suggestions.

Uni

 

[Marshall Barton]

Hi - I've seen many different posts regarding duplicating a record - I am
able to do this fine, however I do have a need to leave one of the fields
(Position.NewLineNo) blank so that the user can fill it in. This field is
not a primary key - I have a PositionID field which uses the autonumber
feature. Since my VBA skills are not very advanced I'm looking for a
solution to leave the field blank. I've included the code that I am using.
This code is using a cmd button with the OnClick() event:

Private Sub cmdDuplicateRecord_Click()
On Error GoTo Err_cmdDuplicateRecord_Click


DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_cmdDuplicateRecord_Click:
Exit Sub

Err_cmdDuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDuplicateRecord_Click

End Sub

Not simpler, but more accurate and far more flexible:

If Me.Dirty Then Me.Dirty = False 'save record
With Me.RecordsetClone
.AddNew
!fieldA = Me.fieldA
!fieldB = Me.fieldB
!fieldD = Me.fieldD 'skip fieldC
!fieldE = Me.fieldE
.Update
Me.Bookmark = .LastModified
End With

 

[Uni]

Marshall;

I saw that one too - I guess that would be a better way to go - I was being
lazy. Using your recommended code how would I ensure that the user populated
the blank field Position.NewLineNo? I was looking at using an IIF statement
with the event OnLostFoscus() to evaluate whether the field was populated and
if it not display a message. I had initially tried making the field
(property) "Required" but encountered problems whenever I duplicated the
record. I would get an error that stated something to the effect that you
couldn't have a null value for the Position.NewLineNo.

Many thanks for your help.

Uni

 

[Marshall Barton]

I saw that one too - I guess that would be a better way to go - I was being
lazy. Using your recommended code how would I ensure that the user populated
the blank field Position.NewLineNo? I was looking at using an IIF statement
with the event OnLostFoscus() to evaluate whether the field was populated and
if it not display a message. I had initially tried making the field
(property) "Required" but encountered problems whenever I duplicated the
record. I would get an error that stated something to the effect that you
couldn't have a null value for the Position.NewLineNo.

The LostFocus event would work if you force the focus to
that control right after setting the bookmark.

If there are other values users can fill in oe other reasons
to move around the form before entering that value, then the
standard approch of leaving the focus wherever you want and
using the form's BeforeUpdate event is more general.

Setting the field's Required property is the most reliable
and highly recommended. If you got that message, it means
something (paste?) was trying to either assign Null to the
field or save the record before any value was entered.

 

[Uni]

Marshall;

I updated the button code to the following:

Private Sub cmdDuplicateRecord_Click()
On Error GoTo Err_cmdDuplicateRecord_Click

If Me.Dirty Then Me.Dirty = False 'save record
With Me.RecordsetClone
.AddNew
!ParNum = Me.ParNum
!Billet = Me.Billet
!RpaNum = Me.RpaNum
!PayPlan = Me.PayPlan
!Series = Me.Series
!Grade = Me.Grade
!CurrentFY = Me.CurrentFY
!OrgCode = Me.OrgCode
!BusLineCo = Me.BusLineCo
!Authorized = Me.Authorized
!Ladder = Me.Ladder
!Vacant = Me.MilAllow
!MilRank = Me.MilRank
!ActionCode = Me.DtAssignMs
!Intern = Me.Intern
!Supervisor = Me.Supervisor
!Wait = Me.Wait
!DovDate = Me.DovDate
!DtTfbReview = Me.DtTfbReview
!DtTfbAppr = Me.DtTfbAppr
!DtfWdCcpo = Me.DtfWdCcpo
!DtCertRcvHrsc = Me.DtCertRcvHrsc
!TentSelectee = Me.TentSelectee
!DtProjOb = Me.DtProjOb
!Base = Me.Base
!Obligated = Me.Obligated
!ObRetDate = Me.ObRetDate
!Budgeted = Me.Budgeted
!Overhead = Me.Overhead
!VacRpt = Me.VacRpt
!Cremarks = Me.Cremarks
!BudgetRmks = Me.BudgetRmks
!CAstudy = Me.CAstudy
!BIN = Me.BIN
!CA_Fun = Me.CA_Fun
!CA_Rea = Me.CA_Rea

.Update
Me.Bookmark = .LastModified
End With

Exit_cmdDuplicateRecord_Click:
Exit Sub

Err_cmdDuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDuplicateRecord_Click

End Sub

Then I changed the property on the field NewLineNo to be required. When I
click on the Duplicate button I get the error:

"The field 'Position.NewLineNo' cannot contain a Null value because the
Required property for this fied is set to True. Enter a value in this field."

However, unlike other code that I used, this happens immediately prior to
copying the record. I am only using the OnClick() event to fire the code on
the button so I must had done something wrong.

Can you assist?

Thanks, again.

Uni

 

[Marshall Barton]

I updated the button code to the following:

Private Sub cmdDuplicateRecord_Click()
On Error GoTo Err_cmdDuplicateRecord_Click

If Me.Dirty Then Me.Dirty = False 'save record
With Me.RecordsetClone
.AddNew
!ParNum = Me.ParNum
!Billet = Me.Billet
!RpaNum = Me.RpaNum
!PayPlan = Me.PayPlan
!Series = Me.Series
!Grade = Me.Grade
!CurrentFY = Me.CurrentFY
!OrgCode = Me.OrgCode
!BusLineCo = Me.BusLineCo
!Authorized = Me.Authorized
!Ladder = Me.Ladder
!Vacant = Me.MilAllow
!MilRank = Me.MilRank
!ActionCode = Me.DtAssignMs
!Intern = Me.Intern
!Supervisor = Me.Supervisor
!Wait = Me.Wait
!DovDate = Me.DovDate
!DtTfbReview = Me.DtTfbReview
!DtTfbAppr = Me.DtTfbAppr
!DtfWdCcpo = Me.DtfWdCcpo
!DtCertRcvHrsc = Me.DtCertRcvHrsc
!TentSelectee = Me.TentSelectee
!DtProjOb = Me.DtProjOb
!Base = Me.Base
!Obligated = Me.Obligated
!ObRetDate = Me.ObRetDate
!Budgeted = Me.Budgeted
!Overhead = Me.Overhead
!VacRpt = Me.VacRpt
!Cremarks = Me.Cremarks
!BudgetRmks = Me.BudgetRmks
!CAstudy = Me.CAstudy
!BIN = Me.BIN
!CA_Fun = Me.CA_Fun
!CA_Rea = Me.CA_Rea

.Update
Me.Bookmark = .LastModified
End With
...

Then I changed the property on the field NewLineNo to be required. When I
click on the Duplicate button I get the error:

"The field 'Position.NewLineNo' cannot contain a Null value because the
Required property for this fied is set to True. Enter a value in this field."

However, unlike other code that I used, this happens immediately prior to
copying the record. I am only using the OnClick() event to fire the code on
the button so I must had done something wrong.

No, it looks like you did what I said. I failed to tie the
Required property setting and the dup problem together and I
apologize for wasting your time. The .Update method saves
the new copy and will fail the Required constraint. That
code will only work if Required is set to No.

An alternative that should work with the Required property
is to set each control's DafaultValue property and then go
to the new record. This way, nothing is set until the user
starts to enter the required value. Even then, the user can
back out of creating the record by hitting the Esc key
twice.

The code for this approach would be along these lines:

If Me.Dirty Then Me.Dirty = False 'save record

'for number fields
Me.controlname.DefaultValue = Me.controlname

'for Text fields
Me.controlname.DefaultValue = """" & Me.controlname& """"

'for date fields
Me.controlname.DefaultValue = Format(Me.controlname,
"\#yyyy-m-d\#")

DoCmd.GoToRecord , ,acNewRec

 

[Uni]

Thanks Marsh - that explains everything.

Thanks for your help.

Uni