Solved

Duplicate Record button in MS Access

Posted on 2009-05-10
12
525 Views
Last Modified: 2012-05-06
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
Comment
Question by:sahi0002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
ID: 24352237
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
 
LVL 3

Expert Comment

by:coleventures
ID: 24352261
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
 

Author Comment

by:sahi0002
ID: 24352510
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:sahi0002
ID: 24352668
Instead of using the standard code, can I use anything else in order to duplicate since my duplicate button is creating problem.
0
 

Author Comment

by:sahi0002
ID: 24352767
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
 
LVL 3

Expert Comment

by:coleventures
ID: 24353903
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
 

Author Comment

by:sahi0002
ID: 24360369
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
 

Author Comment

by:sahi0002
ID: 24360378
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
 
LVL 3

Accepted Solution

by:
coleventures earned 300 total points
ID: 24362047
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
 

Author Comment

by:sahi0002
ID: 24362297
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
 
LVL 77

Expert Comment

by:peter57r
ID: 24368071
You cannot use "me" in a saved query. You have to give the full syntax..

Forms!formnamehere![Contract id]
0
 

Author Comment

by:sahi0002
ID: 24370782
Thanks. Finally its working
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question