Need Help with Copy Record button (1 Viewer)

MarcieFess

Registered User.
Local time
Today, 06:34
Joined
Oct 25, 2012
Messages
107
I used a wizard to create a button that would supposedly copy the current record in a form to a new record. As far as I could tell, it didn't do anything. My record count didn't change.

Macros crash Access, so I always convert macros to VB.

I did that, and it still didn't work.

I found some code on the internet and copied it, changing it as necessary for my application.

Now it's prompting me to input a UPC.

Can someone help me with this, please? The user needs to be able to copy the record in the form and paste it to a new record, and then change the UPC (which is not the primary key but it's set to "no duplicates") It's always possible that I've used a generic name somewhere when I should be using a field or table name specific to my application.

Private Sub btnCopyProduct_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
On Error GoTo btnCopyProduct_Click_Err
'Tag Property to be used later by the append query.
Me.Tag = Me![ProductKey]
'Add new record to end of Recordset object.
With Rst
.AddNew
!ProductName = Me!ProductName
.Update 'Save Changes
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
'Run the Duplicate Order Details append query which selects all
'detail records that have the OrderID stored in the form's
'Tag property and appends them back to the detail table with
'the OrderID of the duplicatedmian form record.
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDuplicateProduct"
DoCmd.SetWarnings True
'Requery the subform to display the newly appended records.
Me![copy of frmProductInput].Requery
btnCopyProduct_Click_Exit:
Exit Sub
btnCopyProduct_Click_Err:
MsgBox Error$
Resume btnCopyProduct_Click_Exit
End Sub


Below is the code generated when I changed the macro...I commented it out
' On Error Resume Next
' DoCmd.RunCommand acCmdSelectRecord
' If (MacroError = 0) Then
' DoCmd.RunCommand acCmdCopy
' End If
' If (MacroError = 0) Then
' DoCmd.RunCommand acCmdRecordsGoToNew
' End If
' If (MacroError = 0) Then
' DoCmd.RunCommand acCmdSelectRecord
' End If
'If (MacroError = 0) Then
' DoCmd.RunCommand acCmdPaste
' End If
' If (MacroError <> 0) Then
' Beep
' MsgBox MacroError.Description, vbOKOnly, ""
' End If
 

MStef

Registered User.
Local time
Today, 11:34
Joined
Oct 28, 2004
Messages
2,251
You don't need the same data in the table.
It means that your tables not normalized wel.
Learn something about DATABASE NORMALIZATION.
 

Alansidman

AWF VIP
Local time
Today, 06:34
Joined
Jul 31, 2008
Messages
1,493
Marcie;
Here is some code I have used in the past to copy four control values to a new record so that you could then enter additional data for the remaining controls. You will have to change the control names, but it should give you an idea on what works.

Code:
Private Sub copyrecordbutton_Click()
On Error GoTo Err_copyrecordbutton_Click
Dim pown As Variant
Dim psource As Variant
Dim pclient As Variant
Dim ptax As Variant

pown = txtcurrent1.Value
psource = txtcurrent2.Value
pclient = txtcurrent3.Value
ptax = txtcurrent4.Value

RunCommand acCmdRecordsGoToNew

txtnew1.Value = pown
txt2new.Value = psource
txtnew3.Value = pclient
txtnew4.Value = ptax

   
Exit_copyrecordbutton_Click:
    Exit Sub

Err_copyrecordbutton_Click:
    MsgBox Err.Description
    Resume Exit_copyrecordbutton_Click
    
End Sub
Alan
 

MarcieFess

Registered User.
Local time
Today, 06:34
Joined
Oct 25, 2012
Messages
107
You don't need the same data in the table.
It means that your tables not normalized wel.
Learn something about DATABASE NORMALIZATION.

I know about database normalization. I'm not talking about having duplicate records. I'm talking about duplicating a record (these records have 25 fields of information) so that only the 2 or 3 fields that will be different have to be changed, without the user having to TYPE the information in the rest of the fields.
 

MarcieFess

Registered User.
Local time
Today, 06:34
Joined
Oct 25, 2012
Messages
107
Marcie;
Here is some code I have used in the past to copy four control values to a new record so that you could then enter additional data for the remaining controls. Alan

Thanks Alan...I'm sure this will work! That's exactly what we need to do; there are 2 fields that will have to be changed for sure and so they don't need to be copied. Appreciate that! Thanks.
 

Users who are viewing this thread

Top Bottom