Solved

Duplicate Record button in MS Access

Posted on 2009-05-10
12
479 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
  • 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now