Duplicating Record Problem

[jpatchak]

Hello All,

I am trying to code a command button on a form to create a duplicate record and renumber it. The code I have in the command button currently is;

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
NumberNewRecord
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
The NumberNewRecord sub is as follows:

[Prospect Number] = DMax("[prospect number]", "PR Only Info", "[list name] = [Forms]![PR Only Info]![cbListFilter]") + 1
If IsNull([Prospect Number]) Then [Prospect Number] = 1[List Name] = cbListFilter.Value

What currently happens with this code is that it adds a new record on form and appears to show the duplicate data in the controls. However, the numbering does not appear to be working. Also, when I move off the new record and then move back, the record still exists, but is empty. Any ideas?

Thanks,
Josh

[NeoPa]

Not many experts use macros :(.
We all use VBA code as it's better in almost every way.
It's particularly more readable (although code tags help too).
If you were to redo using VBA I'm sure you'd get more help.
If not, just leave it here and hope that someone can help. Good luck whichever.

[jpatchak]

This code is in VBA. The first part of the code was generated by the command button wizard. If there is a better way to code it without using the docmd method, please advise.

[NeoPa]

Yes, you're right.
I was confusing calling menu items with macros. :embarrassed:
This is, nevertheless very difficult to read and understand without trawling through the documentation (typical wizard created code :().
Unfortunately I can't advise on alternative code as I have no idea what it's trying to do.
For that same reason I'm sorry I can't help you with this question.

[missinglinq]

I tried using the Wizard generated code to copy a record, like yourself, found it to be too full of problems, and finally "rolled" my own. Basically, I copy the record's values I want carried forward to the new record into variables, goto a new record, then assign these variables to the appropriate controls on the new record. For controls that you want to change, you can leave blank, fill with a message telling the user to fill them in, or, as in your case, fill in a new value. Not sure exactly what you mean by "re-numbering" the record, but if it involves incrementing a custom ID number number, yo could use something like DMAX + 1.

'Copy fields from original record to variables
NewField1 = Me.YourField1
NewField2 = Me.YourField2
NewField3 = Me.YourField3
 
'Go to a new record
DoCmd.GoToRecord , , acNewRec
 
'Plug in old values into new record
Me.YourField1.Value = NewField1
Me.YourField2.Value = NewField2
Me.YourField3.Value = NewField3
 
'Set the fields your user wants to change to blanks (not really necessary) 
Me.YourField4.Value = ""
Me.YourField5.Value = ""
Me.YourField6.Value = ""
 
If any of these fields to be changed is marked as Required in their table's Design Grid, leaving them blank will throw an error, so you'll need to change these to something like this: 
 
Me.YourField4.Value = "Enter New Value for YourField4 "
Me.YourField5.Value = "Enter New Value for YourField5 "
Me.YourField6.Value = "Enter New Value for YourField6 "
 

As my signature insinuates, this is only one of several ways to do the job! Good luck!

[jpatchak]

This solution had occured to me, however this form is huge (over 100 controls), so writing the code to copy each control's value would be tedius and inefficient. I was hoping that there was a way to copy the whole record and then just change the two fields that I needed to. Is there a way maybe with DAO? Sorry, I am new to database programming.

[jpatchak]

This is the solution I came up with - seems to be working alright. Basically, it gets the primary key of the active record and opens a DAO recordset with only this record and the values of all of the fields I want to keep are copied into an array. A new record is then added to this record set. The fields are then populated with the data in the array. The fields I want to change[List Name] is then entered. The field [Prospect Number] is generated by my ReNumber sub and the form is requeried. Does anyone see any potential issues here?

strList = InputBox("Enter List Name")
Dim db As DAO.Database
Dim IQrst As DAO.Recordset
Dim IQstrSQL As String
Dim i As Integer
Dim lngMaster
lngMaster = [ID]
Dim NumFields As Long
IQstrSQL = "Select * FROM [PR Only Info] WHERE [ID] = " & CStr(lngMaster)
Set db = CurrentDb
Set IQrst = db.OpenRecordset(IQstrSQL, dbOpenDynaset)
NumFields = IQrst.Fields.Count - 1
Dim TempArray()
ReDim TempArray(3 To NumFields)
For i = 3 To NumFields
TempArray(i) = IQrst.Fields(i).Value
Next i
With IQrst
.AddNew
.Update
.MoveFirst
Do
If IQrst![ID] <> lngMaster Then
.Edit
For i = 3 To NumFields
.Fields(i).Value = TempArray(i)
Next i
IQrst![List Name] = strList
.Update
End If
.MoveNext

Loop Until .EOF
.Close
End With
Set IQrst = Nothing
Set db = Nothing
ReNumber strList
Requery