• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

Duplicate Record button in MS Access

Hi,

I have a database in MS Access.
There is a "Duplicate Record" button on my main form which is the in built button in ms access.
I need to make some modifications to the button.

When I press the duplicate record button, I want that that records duplicate should be created but some fields that I have for eg: Expiry, Commencing, Payment should not be duplicated and should appear blank in the new duplicate record.

Apart from this there is a field "Created Date" which is the date of creation for every record. I want that whenever the button is pressed, the created date field in the new duplicated record should show the current date and time with function Now()

Please help. Thanks

Below is the code that is there behind my Duplicate record button as of now
Private Sub LARenewal_Click()
On Error GoTo Err_LARenewal_Click
 
 
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste
 
Exit_LARenewal_Click:
    Exit Sub
 
Err_LARenewal_Click:
    MsgBox Err.Description
    Resume Exit_LARenewal_Click
    
End Sub

Open in new window

0
sahi0002
Asked:
sahi0002
  • 7
  • 3
  • 2
2 Solutions
 
peter57rCommented:
After the DoCmd.RunCommand acCmdPaste statement add...

me.Expiry = ""
me.Commencing = ""
me.Payment = null
me.[created date] = Now()

You need to check that the names are correct.
0
 
coleventuresCommented:
I'd take this approach.
Set up an append query.
Call it from a DupButton.
Query matches current record by unique value on form.
So, in the query you only include fields you want to dup.
Created Date will take care of itself. All other fields will be empty
As you wish.
Now, if you want to go to this new record you could find last rec or look for latest date found in date created field.


Robert
Louisiana USA
0
 
sahi0002Author Commented:
Hi guys,

I'm having a problem when using the standard duplicate button.

When I press it, it creates the dulpicate of the record but my last record gets missing and a record with all fields blank gets created and is displayed as teh first record on the form. :S

Please help
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sahi0002Author Commented:
Instead of using the standard code, can I use anything else in order to duplicate since my duplicate button is creating problem.
0
 
sahi0002Author Commented:
Robert, can you elaborate your approach a bit more. I didn't understand it completely. When you say Call it from a DupButton, what do you mean by that?
0
 
coleventuresCommented:
Create a button named "Duplicate"

In the CLICK Event the code would be:

Private Sub Duplicate_Click()
        DoCmd.OpenQuery "NameOfAppendQuery"
End Sub


Now I am not so sure of the following:
If your record set is ordered by DateCreated you could find the "new" last record you appended by this command.

DoCmd.GoToRecord (, , Last,)


Robert
0
 
sahi0002Author Commented:
Hi Robert,

I did as you said. Now my field "Contract ID" is a unique field for each record.

you said Query matches current record by unique value on form.
for that I did this:-

Private Sub Duplicate_Click()
 Dim strCriteria As String
        strCriteria = "[Contract ID]= " & Me![Contract ID]
        DoCmd.OpenQuery "NameOfAppendQuery"
End Sub

But when I puch the button, it asks me if i want to append 2000 rows i.e. my entire list of records. I just want it to append the record that is on the form when i push the button.
Please help

Also the line
DoCmd.GoToRecord (, , Last,)
is giving me an error.
0
 
sahi0002Author Commented:
This is the sql view of my query as of now:-

INSERT INTO [PB Listing] ( [Contract ID], [Created Date], [Site Name] )
SELECT [PB Listing].[Contract ID], [PB Listing].[Created Date], [PB Listing].[Site Name], *
FROM [PB Listing]
ORDER BY [PB Listing].[Created Date];
0
 
coleventuresCommented:
You are almost there.

The criteria needs to be within your append query and not where you have it (in the button event code).

Private Sub Duplicate_Click()
 Dim strCriteria As String
>>Remove This>>>strCriteria = "[Contract ID]= " & Me![Contract ID]
        DoCmd.OpenQuery "NameOfAppendQuery"
End Sub

ADD a 'WHERE' criteria statement...
INSERT INTO [PB Listing] ( [Contract ID], [Created Date], [Site Name] )
SELECT [PB Listing].[Contract ID], [PB Listing].[Created Date], [PB Listing].[Site Name], *
FROM [PB Listing]  WHERE [Contract ID] = me![Contract ID]
ORDER BY [PB Listing].[Created Date];

Robert
0
 
sahi0002Author Commented:
Hi Robert,

When I puch the button now. It asks me if i want to run an append query. I click yes,

Then a window pops up and asks me to enter a parameter value for me![Contract ID].
0
 
peter57rCommented:
You cannot use "me" in a saved query. You have to give the full syntax..

Forms!formnamehere![Contract id]
0
 
sahi0002Author Commented:
Thanks. Finally its working
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now