Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Duplicate Record button in MS Access

Posted on 2009-05-10
12
Medium Priority
?
544 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 800 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 1200 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 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