Solved

Duplicate Record button in MS Access

Posted on 2009-05-10
12
501 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use VBA to update a record in a table in MS Access? 12 36
FrontEnd tools to create web database application 7 56
Calculation in Access 5 26
Switch 5 16
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

770 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