Copy entire record

[fat controller]

Is there a way to copy the data from an existing record, to the record currently being viewed? All data with the exception of the primary key would be needed, and the primary key number would be set as a variable by DLookup.

 

[Minty]

On a form you can use a recordset clone to get a copy of the current record.

 

[fat controller]

Does that work to clone a prior record?

Essentially what I am trying to do is allow a user to 'import' data from a prior record, to allow them to edit that data to bring it up to date; by the time that the user will be doing this, they will already have created a new record (or if they haven't, that will be forced to happen first), and it is that record that needs to be populated by the copied data.

 

[Minty]

Recordset.Clone will make a copy of your existing forms entire recordset. You would simply find the record you wanted to copy (in your instance the current record so use it's unique ID to do the find) you then have the entire records data available to do what ever you want.

If you are only talking about a few fields per record , this might be overkill.

 

[fat controller]

I will need to read up on Recordset.Clone - thank you

 

[fat controller]

OK, I have had a bit of a read, but am unsure if I am understanding it correctly - the code I think I need is something along these lines?

  Dim varRoute
  Dim ToCopy
  Dim ToPaste
  varRoute = me.txtRoute.Value
  ToCopy = DMax(“RouteRecordsID”, “RouteRecords”, “Route=’” & varRoute & “’”)
  ToPaste = me.txtRouteRecordsID.Value
  Me.RecordsetClone.Findfirst “RouteRecordsID=” & ToCopy
  Docmd.Runcommand acCmdCopy
  Docmd.Runcommand GoToRecord , , acLast
  Docmd.Runcomand acCmdPaste
  Exit sub

 

[Minty]

You are mixing two types of recordset manoeuvring here.
The first part is finding the record you want to copy in the cloned recordset.
Your DoCmd statements would be copying the current form recordset .

You would normally refer to the recordset using something like (aircode untested) this would set the values on a form, alternatively you could add new record in code and then display it.

Dim rsCopy as recordset

Set rsCopy = me.recordsetclone
rsCopy.Findfirst “RouteRecordsID=” & ToCopy

If Not Rs.NoMatch Then
     Docmd.Runcommand GoToRecord , , acNew
     Me.YourField1Control = rsCopy.fields("YourField1Name")
     Me.YourField2Control = rsCopy.fields("YourField2Name")
     Me.YourField3Control = rsCopy.fields("YourField3Name")
     etc...
End If

rsCopy.close
set rsCopy = Nothing

Hopefully gives one method - there are others

 

[fat controller]

I am getting a 'variable not declared' error on the line:
rsCopy.Findfirst “RouteRecordsID=” & ToCopy
RouteRecordsID is the primary key of the table that I am wanting it to find?

 

[fat controller]

I am starting to wonder if I am going about this the wrong way - would SQL be a better way to achieve this? But then, would that not require a load of DLookups to get the data for each field?

 

[static]

The type of database makes no difference.

Adding values to a database is easy

currentdb.execute "insert into sometable values (val1, val2)"

currentdb.execute "update sometable set fld1=val1, fld2=val2)"

But you need to know what you are updating.

So... How are you selecting the record you want to copy from?

 

[Minty]

Sorry - I didn't declare or set up all the variables as you had done it in your previous example.

You can use a VBA SQL INSERT query to simply copy the existing form values into a new record. The format is along the lines of

sSqlString = "INSERT INTO YourTable (YourStringField1, YourNumberField4, YourDateField9) VALUES ('String value', 1234 , #Date in mm/dd/yyyy format #)

CurrentDb.Execute sSqlString

 

[fat controller]

I tried declaring RouteRecordsID As Field, but it still baulked at it? Would be handy if I could get that code working though, as much for the learning opportunity as anything.

I am selecting the record by DMax to find the Primary Key, and want to copy most (if not all) fields across with the exception of the Key

At the end of the day, I am not really worried which method achieves it, as long as it works, so that when a user enters a newly created record, they can then click a command and copy all the information from a prior record that shares the same Route ID into the current (newer) record.

 

[Minty]

It might be handy if you posted a stripped down version of your db , as I'm a little confused by your field names / form control names and what should be copied.

If you are copying all the data except one or two fields then the vba Insert is the way to go but you will need to list all the field names except the PK field;

Dim sSQL As String
sSQL = "INSERT INTO YourTableName "
sSQL = SQL & "SELECT AllYour fields, CommaSeperated, gohere FROM YourTableName "
sSQL = SQL & "WHERE YourPrimaryKey = " & Me.TheFomControlNameWithYourPrimaryKey & " ;"

CurrentDb.Execute sSql

 

[fat controller]

Doing it via SQL works perfectly, thank you. Having a wee bit of a battle with the subsequent events now, but I will (hopefully) work those out.

 

[Minty]

If you are trying to get the inserted ID of the record then modify the code slightly to;

  sSql= "INSERT INTO blah blah blah"
  Set db = CurrentDB    [COLOR="Green"]' This is the important bit as it identifies and fixes the db reference to the instance you do the insert with. [/COLOR]
  db.Execute(sSql)
  newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
  Set db = Nothing

 

[fat controller]

I have got that bit thanks, and it opens just fine to the desired record; I had other events that fired off the original text box (Before Update) that updated another table - they are now chucking their toys about a bit, but I am getting there.

 

[static]

This code uses 2 buttons - btnCopyRecord and btnOK.

btnCopyRecord opens another copy of the form. The user then finds the record they want to copy from and clicks btnOK.
btnOK closes the clone and copies all control values back to the first form.

change Form_Form1 to your form

Public f As Form

Private Sub btnCopyRecord_Click()
'create a copy of this form to get values from

    Set f = New Form_Form1
    f.Visible = True
    Set f.f = Me
    f.setup True
End Sub

Private Sub btnOK_Click()
'copy values back to parent form

    For Each c In Me.Controls
        Select Case TypeName(c)
        Case "CommandButton"
        Case "label"
        Case Else: f(c.Name) = c
        End Select
    Next
    DoCmd.Close
End Sub

Private Sub Form_Load()
    setup False
End Sub

Public Sub setup(copying As Boolean)
'screen setup

    btnOK.Caption = "Save back to other record"
    btnCopyRecord.Caption = "Copy from another record"
    
    btnCopyRecord.Visible = True
    btnOK.Visible = True
    
    If copying Then
        btnOK.SetFocus
    Else
        btnCopyRecord.SetFocus
    End If
    
    btnOK.Visible = copying
    btnCopyRecord.Visible = Not copying
End Sub