Solved

Copying data from one record to another

Posted on 2001-06-05
17
295 Views
Last Modified: 2006-11-17
Hi, I have a simple question for you.  I have a form which is linked to a table in Access.  There are many times that I need to copy data from the last record and paste it into a new record on the same table.  I would like a button on this form to do just that.  Copy selected fields from the last record in the table and create a new record in this table containing this data.  Any help you can provide would be appreciated.
0
Comment
Question by:jakemail
  • 7
  • 4
  • 3
  • +2
17 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6157818
There's already a simple key combination (CTRL & ') that will perform this operation on each bound field of a form.
I prefer to leave this "under control" of the user as a whole copy of a previous record can cause fields to be duplicated "erroneously"....

Nic;o)
0
 

Author Comment

by:jakemail
ID: 6157829
Can anyone provide me with some code for a copy button?  Using the CTRL isn't a good solution here.  
0
 
LVL 1

Accepted Solution

by:
jfb_wfd earned 300 total points
ID: 6157886
Good afternoon. We use coded process that allows a user on a new blank record to press an "Auto Fill" button to pull as many fields from the previous record into the new one. All it does is go to the previous record, capture data elements into memory variables, then assign them to data elements in the new record.(This requires separate macros to Move Previous and Move Next, not shown here.) We also check each field for content before trying to bring it forward, to avoid the "Null" errors.

'************************************************************
'   Event Procedure for the AutoFill button. Process begins by checking
'   to see if the StationPropNum has a value. If it does not, then
'   the automatic fill-in begins.
'************************************************************

Private Sub AutoNum_Click()
On Error GoTo Err_AutoNum_Click

Dim strPropNumStart As String
Dim strPrimaryNumber As String
Dim strDescription As String
Dim strSite As String
Dim strLocation As String
Dim strOfficer As String
Dim strDivision As String
Dim datReceived As Date

'**********************************************************
'   If StationPropNum is blank, execute code within IF.
'   First step is to go to the previous record and capture the first six characters of the existing StationPropNum information.
'**********************************************************

If IsNull([StationPropNum]) Then

    DoCmd.RunMacro "macGoToPrevious"
    strPropNumStart = Left([StationPropNum], 6)
       
'**********************************************************
'   Capture other information from the old record to automatically
'   fill into the new record.
'**********************************************************
   
    If Len([ReportingOfficer]) > 1 Then
        strOfficer = [ReportingOfficer]
    End If
   
    If Len([Division]) > 1 Then
        strDivision = [Division]
    End If
   
    If Len([Description]) > 1 Then
        strDescription = [Description]
    End If
   
    If IsDate([DateLastEntry]) Then
        datReceived = [DateLastEntry]
    End If
   
    strPrimaryNumber = [PropPrimaryNumber]
    strSite = [Site]
    strLocation = [Location]

    DoCmd.RunMacro "macGoToNext"
   
'**********************************************************
'   The fields selected for automatic fill-in are assigned values.
'**********************************************************
   
    Me!StationPropNum = strPropNumStart
    Me!PropPrimaryNumber = strPrimaryNumber
    Me!ReportingOfficer = strOfficer
    Me!Division = strDivision
    Me!Site = strSite
    Me!Location = strLocation
    Me!Description = strDescription
    Me!DateLastEntry = datReceived
   
    Form.Refresh
   
End If

Exit_AutoNum_Click:
    Exit Sub

Err_AutoNum_Click:
    MsgBox Err.Description
    Resume Exit_AutoNum_Click
   
End Sub
0
 
LVL 2

Expert Comment

by:englishman
ID: 6158013
With a button called Command30:

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command30_Click:
    Exit Sub

Err_Command30_Click:
    MsgBox Err.Description
    Resume Exit_Command30_Click
   
End Sub

0
 
LVL 2

Expert Comment

by:englishman
ID: 6158071
Or if you have an ascending autonumber field, something like this for a button you can press when you are on a new record:
dim lastrecordID
lastrecordID = dmax("autonumID","tablename")
me.textboxname = dlookup("fieldname","tablename","autonumID=" & lastrecordID)
0
 
LVL 30

Expert Comment

by:Axter
ID: 6158912
Here's my favorite solution:

Private Sub MakeCurrentDataDefault_Click()
If Not IsNull(Age) Then
    Age.DefaultValue = Age
End If
If Not IsNull(UserName) Then
    UserName.DefaultValue = """" + UserName + """"
End If
If Not IsNull(Phone) Then
    Phone.DefaultValue = Phone
End If
End Sub

The above sub function is link to a button.  What it does is makes the current values in the form, the default values.  So when you go to a New Record, it loads the default values, which was what you previously set.

To clear the default values, you can use the following function:
Private Sub ClearDefaultData_Click()
    Age.DefaultValue = ""
    UserName.DefaultValue = ""
    Phone.DefaultValue = ""
End Sub


A trick part in  loading the default values is that any string field needs to have quotes on both sides of the value you're setting for the default value.
Example:
UserName.DefaultValue = """" + UserName + """"

You don't need to do this for numerical fields.

There are some forms in which I always want certain fields to carry over to a new Record.
For example, say I'm entering hours, and I want those same number of hours to carry over to a new record, I can do the following:

Private Sub Form_AfterUpdate()
If Not IsNull(Hours) Then
    Hours.DefaultValue = Phone
End If
End Sub

With the above function, after the user enters the hours on the current records, when he/she goes to a new record, the previous value will display on the new record.

What's good about this method, is that if you back out of the record, you don't have to click UNDO!
That's because default values are not committed, and does not create a new record.

Try it out.
0
 
LVL 30

Expert Comment

by:Axter
ID: 6162414
jakemail,
Did you try out the above method?

Please give some feedback.
0
 
LVL 2

Expert Comment

by:englishman
ID: 6176528
With a button called Command30:

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click


   DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
   DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
   DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command30_Click:
   Exit Sub

Err_Command30_Click:
   MsgBox Err.Description
   Resume Exit_Command30_Click
   
End Sub

OR

If you have an ascending autonumber field, something like this for a button you can press when you
are on a new record:
dim lastrecordID
lastrecordID = dmax("autonumID","tablename")
me.textboxname = dlookup("fieldname","tablename","autonumID=" & lastrecordID)

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:Axter
ID: 6177465
englishman,
It's common etiquette to post your answer as a comment.

This is especially true, when others have already posted answers as comments.
0
 
LVL 30

Expert Comment

by:Axter
ID: 6177471
jakemail,
Please be sure to award points to the expert who's method you decide to use.
Thank you
0
 

Author Comment

by:jakemail
ID: 6177678
i did not use the solution that englishmans offered i used the solution that jfb_wfd.  how do i award the points to him.  sorry it took so long to get back with you.  
0
 

Author Comment

by:jakemail
ID: 6177686
this worked great.  thanks for your help.

0
 
LVL 30

Expert Comment

by:Axter
ID: 6177724
jakemail,
Thank you for insuring that the right expert got the points.
Good Job
0
 
LVL 1

Expert Comment

by:jfb_wfd
ID: 6177861
You're welcome. Thanks for asking, and for the points. Have fun on your projects.
0
 
LVL 2

Expert Comment

by:englishman
ID: 6185672
Look - I just got bored of waiting, it is possible to wait forever here sometimes.
0
 
LVL 30

Expert Comment

by:Axter
ID: 6188876
>>Look - I just got bored of waiting, it is possible to
>>wait forever here sometimes.

I can understand that.  What I usually do, is post a reminder message.
Something like the following:
Hi (Questioner),
Did you forget about this question.  Please give some feedback, or award points if the question has been answer.
Thank you


Now if you still don't get any feedback, and you were the first person to post a comment as an answer, then it would be OK to post your comment as an answer.
If your answer came after someone elses, then you should coordinate with the other expert as to which one should convert their comment to an answer.

Work as a team...
0
 
LVL 30

Expert Comment

by:Axter
ID: 6188886
Did a type-o.  The last paragraph should have read like the following:

Now if you still don't get any feedback, and you were the first person to post an answer as a comment,
then it would be OK to post your comment as an answer.
If your answer came after someone elses, then you should coordinate with the other expert as to which
one should convert their comment to an answer.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

12 Experts available now in Live!

Get 1:1 Help Now