Append to next record?

[Michael Auer]

Is it possible to create a query and/or macro to copy fields 6-10 of the current record seen in a form to fields 1-5 of the next, already existing record in the table? The typical append query wants to create a new record, but I want the fields to be copied to the next record which already exists. Thanks!

 

[Mike375]

By "next record" do you mean the record that is physically on the next row of a tabular form or the next record when you click on the navigation botton at the bottom of a form.

 

[Moniker]

You can't append to an existing record. You can only update or delete an existing record. It sound like what you want to do is move the contents of five fields (fields 6-10) to five different fields, but in the next record that magically already exists. You can't really do this without getting into some coding and making a temporary dataset or three. If you can provide a little more detail, perhaps you can get a little more guidance.

 

[Michael Auer]

Thank you, Mike375 and Moniker.

If, for example, I am looking at record 27 in the form, I would like to copy the contents of fields 6-10 of that record and paste those contents into fields 1-5 of the already existing record 28, of which fields 1-5 are empty before the paste.

 

[KeithG]

Sounds like your data is not normalized. By record 28 do you mean you have an autonumber field with the value of 28? can you post an example?

 

[Michael Auer]

Thanks, KeithG.

The database was created by someone else, and I am trying to help him with this challenge. The data is probably not normalized.

The form shows one record at a time and is sorted by date. There is no autonumber field. One could use the navigation buttons to go from record 27 to record 28 by clicking on the "next" arrow.

 

[KeithG]

what are the fileds names and the table name?

 

[Michael Auer]

Unfortunately I do not have access to the database right now. For argument's sake, let's call them Table1 and Field1, Field2, etc. through Field10.

 

[Moniker]

You're just asking for data nightmares down the road, but here's one way to do it using ADO.

Sub MoveFields()

    Dim rsYourRecordset As ADODB.Recordset
    Dim MoveField1 As String
    Dim MoveField2 As String
    Dim MoveField3 As String
    Dim MoveField4 As String
    Dim MoveField5 As String

    Set rsYourRecordset = New ADODB.Recordset

    With rsYourRecordset
        .Open "SELECT * FROM YourTableName", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        .MoveLast
        .MoveFirst
        .Move X [COLOR="Green"]'Move to the record where you want to copy fields 6-10.  Subtract 1 from it.  To move to record 2, X=1, record 3, X=2, etc.[/COLOR]
        MoveField1 = .Fields("Field6")
        MoveField2 = .Fields("Field7")
        MoveField3 = .Fields("Field8")
        MoveField4 = .Fields("Field9")
        MoveField5 = .Fields("Field10")
        .MoveNext
        .Fields("Field1") = MoveField1
        .Fields("Field2") = MoveField2
        .Fields("Field3") = MoveField3
        .Fields("Field4") = MoveField4
        .Fields("Field5") = MoveField5
        .Update
        .Close
    End With

    Set rsYourRecordset = Nothing

End Sub

 

[Michael Auer]

Thank you so much, Moniker!

I will try it and let you know how it works for me.

Mike

 

[Michael Auer]

Moniker,

I would like to use a button on the form I created to run this code. I tried pasting your code inside the button's code, but I get an error message that says: "Compile error: Expected end sub."

Will a button on a form run this code? Thanks!

Mike

 

[Michael Auer]

Can anyone help me with this? Moniker was kind to write the code, I just am not quite sure what to do with it at this point. As I stated earlier, I would like to put a button on the form to run this code to copy the contents of fields 6-10 of the current record and paste them into fields 1-5 of the next, already existing, record. Thanks in advance!

Mike