Duplicate all fields except one

[Paul3rd]

Hello,
The records in the JOBS table have 20 fields.
I'd like to duplicate the record but change the YEARID field.
My wizard built a button to copy the record, how can I modify
the code to change one field?
Thanks in advance,
Paul

Private Sub DuplicateRecord_Click()
On Error GoTo Err_DuplicateRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_DuplicateRecord_Click:
Exit Sub

Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click

End Sub

 

[Jeff Boyce]

You've described "how" you want to do something, but not "why". What
business need are you planning to solve by having essentially identical
(except for year) records?

That sounds a bit like what you might have to do if you were working with a
spreadsheet instead of a relational database...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Paul3rd]

Hello Jeff,
Each record contains info on a particular model of motorcycle.
Sometimes the technical information doesn't change from one year
to the next, (or just one or two things change). Forms and queries in the
application rely on the YEAR information.
I have a data entry form to allow information input when a new model is
introduced.
It would be easier to code a Click event of a button to duplicate the record
except for the YEARID (or the SparkPlugID etc.)
Paul

 

[John Spencer]

Private Sub DuplicateRecord_Click()
On Error GoTo Err_DuplicateRecord_Click
Dim x as NewValue

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
x = InputBox ("Enter new value?","New Year ID","2009")
'Assumption that YearID is a text field. If YearID is a number field
'you may have to use Me.YearID = Val(x)
IF Len(x) > 0 then
Me.YearId = x
End If

Exit_DuplicateRecord_Click:
Exit Sub

Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Paul3rd]

John,
Thanks, that's exactly what I was looking for.
Paul