Copying specific fields from one existing record to another existing record (1 Viewer)

GraemeG · Feb 25, 2011

Hello.

I have a database with many tables and related forms, which have approx 7 fields which are already populated with address information. They are unique by a field called 'Propref' which is a unique property reference number (UPRN)

What I need to be able to do is as follows:

1) The user searches for a record (DONE)
2) Completes the survey (remainding fields) (DONE)

However I would now like a form or button which allows the user to click and copy record.
I.e. they select the record they wish to copy from.
Then select another record which they wish to copy the data to the unpopulated fields.

So they simple access copy button does not work as it copies all fields to a new record.

Any help would be very much appreciated.

Regards
Graeme

GraemeG · Feb 26, 2011

Not sure this is what Iam after. I want to be able to select the surveyed record and then select the record to copy the data too and click clone. So basically a form with two combo box with the UPRN in and a clone button. First they slect surveyed prop from box then other record in other and click the button.

smig · Feb 26, 2011

there are many ways to do it.
one could be:

Code:

dim rs as dao.recordset
dim ValueList()
 
rs = currentdb.openrecordset("Select * from [tblYourTable] WHERE [UPRN] = " & UPRNCopyFrom)
ValueList(0) = rs("Field1")
ValueList(1) = rs("Field2")
...
rs.close
 
rs = currentdb.openrecordset("Select * from [tblYourTable] WHERE [UPRN] = " & UPRNCopyTo)
rs.edit
rs("Field1") = ValueList(0)
rs("Field2") = ValueList(1)
...
rs.update
rs.close
 
set rs = Nothing

vbaInet · Feb 26, 2011

GraemeG said:
So they simple access copy button does not work as it copies all fields to a new record.

Well, it works because it does exactly what is intended. Obviously your needs are specific.

You can use the copy button to copy and paste all the fields, then delete (i.e. set to Null) the ones you don't need in code. This is dependent on whether the number of fields required is more than the unwanted fields.

GraemeG · Feb 27, 2011

smig said:

there are many ways to do it.
one could be:

Code:

dim rs as dao.recordset
dim ValueList()
 
rs = currentdb.openrecordset("Select * from [tblYourTable] WHERE [UPRN] = " & UPRNCopyFrom)
ValueList(0) = rs("Field1")
ValueList(1) = rs("Field2")
...
rs.close
 
rs = currentdb.openrecordset("Select * from [tblYourTable] WHERE [UPRN] = " & UPRNCopyTo)
rs.edit
rs("Field1") = ValueList(0)
rs("Field2") = ValueList(1)
...
rs.update
rs.close
 
set rs = Nothing

Thanks. But this part is giving a debug - Variable Not Defined.

UPRNCopyFrom

smig · Feb 27, 2011

GraemeG said:
Thanks. But this part is giving a debug - Variable Not Defined.

UPRNCopyFrom

you need to put your oun data and variables in it.
you can't do a 1:1 copy fo the code and hope it will work.

UPRNCopyFrom, UPRNCopyTo are the keys of the records you want to copy from/to

vbaInet · Feb 27, 2011

Remember to Redim your array smig,

amischiefmaker · Oct 15, 2014

I know this thread is old, it had the type of question I wanted answered but not the solution unfortunately. I eventually found a solution that was right for me and thought it might be useful for others that come across it.

Option Compare Database
Option Explicit
Dim rs As dao.Recordset

Private Sub Identifier_AfterUpdate()
' If there is an error go to the rror procedure below
On Error GoTo Err_Proc
' Set the rs recordset value to the values in the documents query
Set rs = CurrentDb.OpenRecordset("Documents")
'If the record count is greater than zero then do the following
If rs.RecordCount <> 0 Then
' With the query recordset
With rs
' Find the first value in the documents query that matches current form field labelled Identifier
.FindFirst "Identifier" = Me.Identifier
' If there is a match then copy the value in the documents query version field to the current form version field
If Not .NoMatch Then
.Edit
.Fields("Version") = Me.Version
.Update
End If
End With
End If

Exit_Proc:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub
Err_Proc:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Proc
End Sub

ROSEK · May 29, 2021

I am trying something similar to this but having an issue. I am trying to copy a record within the same table but only want to coping 7 fields. I tried the following code but get a compile error (Invalid use of property) every time. The debugger highlights the rs =CurrentDb.

I am using Access 365 Version 2104

Any suggests would be great

Table Name: Build _Production_Order
________________________________
Private Sub Copy_Order_Click()

Dim rs As DAO.Recordset
Dim ValueList()

rs = CurrentDb.OpenRecordset("Select * from [Build_Production_Orders] WHERE [UPRN] = " & UPRNCopyFrom)
ValueList(0) = rs("Job_Number")
ValueList(1) = rs("Description")
ValueList(2) = rs("Customer")
ValueList(3) = rs("Salesperson")
ValueList(4) = rs("CNC_Time_(ea)")
ValueList(5) = rs("Fab_Time_(ea)")
ValueList(6) = rs("Assy_Time_(ea)")
ValueList(7) = rs("Pkg_Time_(ea)")

...
rs.Close

rs = CurrentDb.OpenRecordset("Select * from [Build_Production_Orders] WHERE [UPRN] = " & UPRNCopyTo)
rs.Edit
rs("Job_Number") = ValueList(0)
rs("Description") = ValueList(1)
rs("Customer") = ValueList(2)
rs("Salesperson") = ValueList(3)
rs("CNC_Time_(ea)") = ValueList(4)
rs("Fab_Time_(ea)") = ValueList(5)
rs("Assy_Time_(ea)") = ValueList(6)
rs("Pkg_Time_(ea)") = ValueList(7)
...
rs.Update
rs.Close

Set rs = Nothing

End Sub
____________________________________________

arnelgp · May 29, 2021

youre Array variable has no Length, you need to Redim your Array or use Static length:

Dim ValueList(0 To 7)

or:

Dim ValueList()

Redim ValueList(0 To 7)

and use Set when assigning an Object to a variable:

Set rs = CurrentDb.OpenRecordset("Select * from [Build_Production_Orders] WHERE [UPRN] = " & UPRNCopyFrom)

ROSEK · May 30, 2021

Thanks Arnelgp.
Maybe this is not the right code for what I am trying to do. We do mostly of one off mechanical builds for our customer but sometime get a repeat job. In those case we would want to copy an existing record but not all fields as the PO #, start date and end date would be different.

Maybe this code is the best way to do that I am not sure. The error is now syntax error. I can attach the actual database if you want to look at it

ROSEK · Jun 2, 2021

Arnelgp,

Here is the database I started but still having an issue with the partial copy of a record in the same table. I know there is a pre made command function to easily copy the entire record but I would prefer if we only copied some information. Then went directly to the form for that new record and not have to delete the fields that would require new information to be input by the user.

arnelgp · Jun 2, 2021

see if this is what you want.

ROSEK · Jun 2, 2021

perfect thanks

Copying specific fields from one existing record to another existing record (1 Viewer)

GraemeG

Registered User.

GraemeG

Registered User.

smig

Registered User.

vbaInet

AWF VIP

GraemeG

Registered User.

smig

Registered User.

vbaInet

AWF VIP

amischiefmaker

New member

ROSEK

New member

arnelgp

..forever waiting... waiting for jellybean!

ROSEK

New member

ROSEK

New member

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

ROSEK

New member

Similar threads

Users who are viewing this thread