[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Copying data from one record to another

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
jakemail
Asked:
jakemail
  • 7
  • 4
  • 3
  • +2
1 Solution
 
nico5038Commented:
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
 
jakemailAuthor Commented:
Can anyone provide me with some code for a copy button?  Using the CTRL isn't a good solution here.  
0
 
jfb_wfdCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
englishmanCommented:
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
 
englishmanCommented:
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
 
AxterCommented:
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
 
AxterCommented:
jakemail,
Did you try out the above method?

Please give some feedback.
0
 
englishmanCommented:
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
 
AxterCommented:
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
 
AxterCommented:
jakemail,
Please be sure to award points to the expert who's method you decide to use.
Thank you
0
 
jakemailAuthor Commented:
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
 
jakemailAuthor Commented:
this worked great.  thanks for your help.

0
 
AxterCommented:
jakemail,
Thank you for insuring that the right expert got the points.
Good Job
0
 
jfb_wfdCommented:
You're welcome. Thanks for asking, and for the points. Have fun on your projects.
0
 
englishmanCommented:
Look - I just got bored of waiting, it is possible to wait forever here sometimes.
0
 
AxterCommented:
>>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
 
AxterCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now