Clone a record on a form

[bulrush]

A2003 on Win XP

I'm made a button using the Button Wizard to duplicate (clone) a record on a form. I have 2 combo boxes used to search for records on different fields. Whenever I clone a record I get errors from the AfterUpdate of these combo boxes. The error is:
"3020 Update or Cancel Update without Addnew or Edit".

Here is my Clone button code:
The error actually occurs in cmdClone, and the error is "Select Record isn't available right now". I also have a subform on my form, is my subform the problem? I don't use any permissions on my database, anyone can edit anything.

Private Sub cmdClone_Click()
On Error GoTo Err_cmdClone_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ' Select record. Error occurs here.
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_cmdClone_Click:
    Exit Sub

Err_cmdClone_Click:
    MsgBox Err.Description
    Resume Exit_cmdClone_Click
    
End Sub

I'll look into exactly which lines causes the error.

Any idea how to fix this?

 

[vbaInet]

What is your main purpose of cloning the record? What will it be used for in the end?

Maybe you could just employ variables to handle that job instead?

 

[bulrush]

The cloned record will have all the same values as the source record, except for one field. Thus, the record really does need to be cloned. It is a piece of produce that has all the same info except for a PLU.

 

[vbaInet]

Which is I was suggesting you use variables to store the information, arrays would work fine or if you want to get your hands dirty then look at the Dictionary object. You are only storing one record at a time right?

 

[bulrush]

I have one "master" table, with 600+ records right now, and I need an edit screen to find and edit each record. The number of "master" records will slowly and surely be increasing.

Besides, my whole application is based on records in these tables. I cannot use variables in this case.

 

[vbaInet]

I'm still not entirely convinced. Maybe I need clarification on what you want to achieve. Question.

What is the method of the Docmd below supposed to do? Select what exactly?

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70' Select record. Error occurs here.

 

[bulrush]

That line selects the current record to clone. I don't know what the second line does, Access Button Wizard does not add comments. But the third Docmd does a PasteAppend.

The error is in the first Docmd, which is trying to select the record.

My hunch is, because my form to edit these records contains a subform, I simply cannot select the record because of the linked subform records.

 

[vbaInet]

I can't remember what the numbers in the menu items stand for. Looking at your code I don't see any updating going on there? All you're doing is searching through your recordset whether the field, which has just been updated, exists? I don't get that.

Could you please explain the flow of things.

 

[bulrush]

Ok. My initial post was too complicated, because my error trapping wasn't working right. So I edited my first post in this thread. So I took out stuff about my combo boxes. Now look at the cmdClone code.

This line:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ' Select record

gives me an error that the SelectRecord command is not available now.

First, this code was created by the Access Button Wizard but it doesn't work.

Here's how you can duplicate my code:
- Make a master and detail table with a one to many relationship.
- Make a form which edits the master records and contains a subform which links to the linked detail records.
- Make a combo box to search and pull up a single record.
- Make a clone button using the Button Wizard to duplicate a record.

See if you get any errors.

In the cbxSearch_AfterUpdate event use this code:

Dim Criteria As String
Dim MyRS As dao.Recordset
dim procname as string

on error goto MyError
procname="cbxSearch_Afterupdate"
me.requery ' Requery form to account for Access 97 bug.
cbxSearch.requery  
Set MyRS = Me.RecordsetClone
  
Criteria = "[SSNum] = '" & cbxSearch & "'"
MyRS.MoveFirst

MyRS.FindFirst Criteria
If MyRS.NoMatch=true  Then
    MsgBox "Could not find a match for " & cbxSearch
Else
    Me.Bookmark = MyRS.Bookmark
End If
MyRS.Close
set MyRS=nothing
exit sub

MyError:
Call DispError(procname)
exit sub

In cbx_Enter event put:

cbxSearch.requery

 

[vbaInet]

Here's how you can duplicate my code:
- Make a master and detail table with a one to many relationship.
- Make a form which edits the master records and contains a subform which links to the linked detail records.
- Make a combo box to search and pull up a single record.
- Make a clone button using the Button Wizard to duplicate a record.

I wouldn't have the time to perform the above operations but I'm sure we could get to the bottom of this without needing to do that.

Under the Data tab of your form, check the properties: Allow Additions, Allow Edits. Both should be set to Yes. Also try using this in place of that line of code.

DoCmd.RunCommand acCmdSelectRecord

You haven't yet explained why you are performing the search in that field?

 

[bulrush]

I used your code above instead of the Button Wizard code, and the error in cmdClone no longer appears. Now I am getting new errors from cbxPLU_AfterUpdate. I don't know why cbxPLU_AfterUpdate is running during my clone process but it is.

The code is this:

 Private Sub cbxPLU_AfterUpdate()
Dim Criteria As String
Dim MyRS As dao.Recordset
Dim procname As String

On Error GoTo MyError
procname = "cbxPLU_Afterupdate"
Me.Requery ' to account for Access 97 bug.
cbxPLU.Requery ' 3020 error here
If (Len(cbxPLU) > 0) Then
    Set MyRS = Me.RecordsetClone
      
    Criteria = "[GID] = " & cbxPLU
    MyRS.MoveFirst
    MyRS.FindFirst Criteria
    
    If MyRS.NoMatch = True Then
        MsgBox "Could not find a match for " & cbxPLU
    Else
        Me.Bookmark = MyRS.Bookmark
    End If
    MyRS.Close
    Set MyRS = Nothing
End If

Exit Sub

MyError:
Call DispError(procname)
Exit Sub

End Sub

The error is: "3020 Update or CancelUpdate without Addnew or Edit." This error happens in the line "cbxPLU.requery" because doing the cbxPLU.requery also calls Form_BeforeUpdate which tries to update several fields, and fails.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo MyError

Debug.Print "Form_BeforeUpdate"

'DoCmd.RunCommand acCmdSaveRecord
txtUpdateDate.Enabled = True
txtUpdateUser.Enabled = True
txtImageName.SetFocus

'txtUpdateDate.SetFocus
txtUpdateDate = Now()
txtUpdateDate.Enabled = False

'txtUpdateUser.SetFocus
txtUpdateUser = gUsername
txtUpdateUser.Enabled = False

Exit Sub

MyError:
If (Err.Number > 0) Then
    Call DispError("Form_BeforeUpdate")
    'Stop
    Resume Next
    'Exit Sub
Else
    Resume Next
End If

End Sub

 

[vbaInet]

I used your code above instead of the Button Wizard code, and the error in cmdClone no longer appears. Now I am getting new errors from cbxPLU_AfterUpdate. I don't know why cbxPLU_AfterUpdate is running during my clone process but it is.

Which made me ask (again), why are you performing this function? Your clone process would always activate/call the AfterUpdate event of any control that is being cloned.

 

[ghudson]

How many fields are in a record? Maybe the old duplicatecurrentrecord.zip sample I posted @ make a duplicate of a record will help.

 

[bulrush]

Which made me ask (again), why are you performing this function? Your clone process would always activate/call the AfterUpdate event of any control that is being cloned.

I need to duplicate a record, and store the result on the same table as the source record.

If my combo boxes are not bound, why would their AfterUpdate events be fired during my clone process?

Ghudson,
There are about 40 fields on the form/table.

I have used my original Cloning code (from Access 95) on many forms, but those forms never had subforms, which is why I suspect the problem is the fact that you cannot clone a record on a form with a subform.

 

[vbaInet]

The clone will only affect the records of the main form and it will open a new record for your subform. So that shouldn't affect it. Ensure that the Control Source property of your combo boxes are blank. Comment out the code in your combo boxes and try out the clone process first to see if that works?

 

[bulrush]

Thank you all. I actually used the code from ghudson's link above and that worked with my subform, even though it doesn't clone the subform records (which is fine with me).

For some reason, the button wizard code refused to work.