Duplicating records and autonumber

[Guest]

I'm trying to add a "duplicate record" button to a form. The Access version of the process:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Throws errors every time I run it. I suspect this has soemthing to do with it trying to paste into the autonumber field. So far, I've been unable to capture the error and prevent my user from seeing it.

I searched the archives of this board duplication questions and tried the solution recommended at: http://support.microsoft.com/default.aspx?scid=kb;en-us;210236

The KB's recommendation has given me many hours of headache. The method they use walks through every control on the form and tries to set the control's value to the corresponding value from the recordset. Unfortunately, I have a lot of controls on my form that don't exist in the record set (labels, command buttons, etc). I had to fight the sample code to get it to work even a little bit. One example, it took me a while to get it to use a record of my choosing to copy from. For a long while it thought my recordset was empty (Both BOF and EOF = true) In short, I really don't want to continue down that road unless it really is "the only way."

Any suggestions for this seemingly simple task?

Also - I'd like to thank all of you guys (and gals) out there that know Access so well and are willing to give your time to help those of us that are lost.

-Kyle

 

[Douglas J. Steele]

I don't see why having other controls should really matter: the code in the
KB article assumes you have a list of fields that you want to copy into (the
variable FillFields is supposed to hold this information)

While there may well be other ways, I doubt they'll be any easier.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to add a "duplicate record" button to a form. The Access version of the process:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

Throws errors every time I run it. I suspect this has soemthing to do

with it trying to paste into the autonumber field. So far, I've been unable
to capture the error and prevent my user from seeing it.

I searched the archives of this board duplication questions and tried the

solution recommended at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210236

The KB's recommendation has given me many hours of headache. The method

they use walks through every control on the form and tries to set the
control's value to the corresponding value from the recordset.
Unfortunately, I have a lot of controls on my form that don't exist in the
record set (labels, command buttons, etc). I had to fight the sample code
to get it to work even a little bit. One example, it took me a while to get
it to use a record of my choosing to copy from. For a long while it thought
my recordset was empty (Both BOF and EOF = true) In short, I really don't
want to continue down that road unless it really is "the only way."

Any suggestions for this seemingly simple task?

Also - I'd like to thank all of you guys (and gals) out there that know

Access so well and are willing to give your time to help those of us that
are lost.

 

[Douglas J. Steele]

If you check the code in the KB article, you'll see there's no AddNew in it.
That's because you're assigning values to the bound controls, which
automatically adds a new record. I think your AddNew is creating a record
that isn't saved, but the AutoNumber value is lost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Originally I had deleted that feature because i didn't want to enter all

the field names. *slap on the wrist* Shortcuts are bad. It now copies all
of the fields I want, but it's doing someing else I don't like: autonumber
is stepping forward by two (instead of the normal one). My question: how
do I fix this autonumber problem?

I've modified the KB code a little because I want the user to be looking

at a record and be able to choose that record as the one they want copied.
I'll star (***) those parts I changed.

Function AutoFillNewRecord(F As Form, CopyFrom As Long)
'*** I added the CopyFrom variable

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
Dim strFindWhat As String '*** Added strFindWhat

On Error Resume Next

' ***I don't use this, so it's commented out
' Exit if not on the new record.
' If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
' ***Actually, I want the record that matches the value of "CopyFrom"
Set RS = F.RecordsetClone

' RS.MoveLast *** not used

strFindWhat = "HistoryID = " & CopyFrom
RS.FindFirst strFindWhat


'*** Step the Form into a new record.
'*** I suspect this is my problem-child. I want the code to now add a new record
'*** to the form which will be used as the paste target.
'*** This is the last change I made to the KB code.
F.Recordset.AddNew

' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function

' Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

' Visit each field on the form.
For Each C In F
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function




<snip>

 

[Guest]

Doug, thanks for your responses.

Unfortunately, if I take the AddNew line out, the code assigns the values of the current record to itself. I was using the AddNew method to step the form into a new record. The KB code is set up to have the *user* create a new record and then run the code to pull data from the last record in the table. I'm trying to modify it to grab all of the current values and stuff them into a new record that the code creates.

Should I try to move the form to a new record before running this code (as the KB intended)? How would I do that? I'm sure there is a simple Docmd.___ or FormName.____ method that performs this action, but for some reason I have not found it. (I will grab the CopyFrom value before advancing the form)

Thanks again,
Kyle

If you check the code in the KB article, you'll see there's no AddNew in it.
That's because you're assigning values to the bound controls, which
automatically adds a new record. I think your AddNew is creating a record
that isn't saved, but the AutoNumber value is lost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Originally I had deleted that feature because i didn't want to enter all

the field names. *slap on the wrist* Shortcuts are bad. It now copies all
of the fields I want, but it's doing someing else I don't like: autonumber
is stepping forward by two (instead of the normal one). My question: how
do I fix this autonumber problem?

I've modified the KB code a little because I want the user to be looking

at a record and be able to choose that record as the one they want copied.
I'll star (***) those parts I changed.

Function AutoFillNewRecord(F As Form, CopyFrom As Long)
'*** I added the CopyFrom variable

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
Dim strFindWhat As String '*** Added strFindWhat

On Error Resume Next

' ***I don't use this, so it's commented out
' Exit if not on the new record.
' If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
' ***Actually, I want the record that matches the value of "CopyFrom"
Set RS = F.RecordsetClone

' RS.MoveLast *** not used

strFindWhat = "HistoryID = " & CopyFrom
RS.FindFirst strFindWhat


'*** Step the Form into a new record.
'*** I suspect this is my problem-child. I want the code to now add a new record
'*** to the form which will be used as the paste target.
'*** This is the last change I made to the KB code.
F.Recordset.AddNew

' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function

' Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

' Visit each field on the form.
For Each C In F
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function




<snip>

 

[Douglas J. Steele]

That's doubtlessly why their code has the MoveLast statement in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, thanks for your responses.

Unfortunately, if I take the AddNew line out, the code assigns the values

of the current record to itself. I was using the AddNew method to step the
form into a new record. The KB code is set up to have the *user* create a
new record and then run the code to pull data from the last record in the
table. I'm trying to modify it to grab all of the current values and stuff
them into a new record that the code creates.

Should I try to move the form to a new record before running this code (as

the KB intended)? How would I do that? I'm sure there is a simple
Docmd.___ or FormName.____ method that performs this action, but for some
reason I have not found it. (I will grab the CopyFrom value before
advancing the form)

Thanks again,
Kyle

If you check the code in the KB article, you'll see there's no AddNew in it.
That's because you're assigning values to the bound controls, which
automatically adds a new record. I think your AddNew is creating a record
that isn't saved, but the AutoNumber value is lost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Originally I had deleted that feature because i didn't want to enter

all
the field names. *slap on the wrist* Shortcuts are bad. It now copies all
of the fields I want, but it's doing someing else I don't like: autonumber
is stepping forward by two (instead of the normal one). My question: how
do I fix this autonumber problem?

I've modified the KB code a little because I want the user to be

looking
at a record and be able to choose that record as the one they want copied.
I'll star (***) those parts I changed.

Function AutoFillNewRecord(F As Form, CopyFrom As Long)
'*** I added the CopyFrom variable

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer
Dim strFindWhat As String '*** Added strFindWhat

On Error Resume Next

' ***I don't use this, so it's commented out
' Exit if not on the new record.
' If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
' ***Actually, I want the record that matches the value of "CopyFrom"
Set RS = F.RecordsetClone

' RS.MoveLast *** not used

strFindWhat = "HistoryID = " & CopyFrom
RS.FindFirst strFindWhat


'*** Step the Form into a new record.
'*** I suspect this is my problem-child. I want the code to now

add a
new record

'*** to the form which will be used as the paste target.
'*** This is the last change I made to the KB code.
F.Recordset.AddNew

' Exit if you cannot move to the last record (no records).
If Err <> 0 Then Exit Function

' Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

' If there is no criteria field, then set flag indicating ALL
' fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

' Visit each field on the form.
For Each C In F
' Fill the field if ALL fields are to be filled OR if the
' ...ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function




<snip>