Copy fields to new record (1 Viewer)

[HeavyD]

Lovely folks,

I would like to be able to copy select fields from one entry to the next.

I have product return forms that can have an arbitrary number of items on it. The items may vary in serial number, asset tag number, and possibly product name. All other fields on the form will be the same. Each item will have it's own return record, with the same tracking number, date, OPI and other fields.

Simplified context:
Return Form
Tracking Number: Return_123
Return Date: Today's Date
OPI: John Doe
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductB-------Serial Number--------Asset Tag
ProductB-------Serial Number--------Asset Tag
ProductC-------Serial Number--------Asset Tag

It would save me a lot of time if I could click a button to copy all the unchanging fields and just leave the specific item fields blank.

Appreciate the insight.

 

[The_Doc_Man]

If you have a form controlling this process, you have the means to propagate fields from one new record to the next. The command button wizard will even help you by creating the "scaffold" on which to build this structure.

IF you use a button to create the new record, you can open up the button-click code to:

1. Copy the fields you wanted to propagate into local variables behind the button-click subroutine.
2. Create the new record and move to it.
3. Before exiting the button-click, deposit the copied fields into the record to which you just moved.

The "plain" version of this only has step #2. You would have to add the code for the "not so plain" version. The button wizard will build the "vanilla" routine for you more than once, so do that. Have two buttons built the same way but different names. Then modify one of the routines but not the other. Then you have TWO buttons, one of which makes a true "virgin" record and the other one "diddles" a bit before returning.

 

[theDBguy]

Hi. How many total fields do you have and how many do you want to copy?

 

[Dreamweaver]

This is what I use on one of my systems, I copy all fields which are then updated depending on the export type conditions.

This is used in a buttons on click event

Dim R As DAO.Recordset
Dim I As Long
On Error GoTo HandleErr

Set R = CurrentDb.OpenRecordset("SELECT * FROM tblOutPuts", dbOpenDynaset, dbAppendOnly)
    With R
        .AddNew
            !OutputName = "Give me a name!!"
            'Presets
            !ADataType = 4
            !ListType = "Numbering"
            !UseBB = False
            'End Presets
            !OutputType = Me.Cbo_OutputType
            !MembersColour = Me.Cbo_MembersColour
            !TitleColourID = Me.Cbo_TitleColourID
            !DatesColourID = Me.Cbo_DatesColourID
            !RecordColourID = Me.Cbo_TrackColour
            !StatsSize = Me.Cbo_StatsSize
            !MembersSize = Me.Cbo_MembersSize
            'Only Add Below If They have values
            I = !OutputID
        .Update
    End With
    'Update The Recordset before trying to use find
    Me.Requery
    Me.Recordset.FindFirst "[OutputID] = " & I 'Goto that record!
   
    MsgBox "A copy has been added and is now the current output", vbInformation + vbOKOnly, "Success"
'Clean Up
R.Close
Set R = Nothing

HandleExit:
    Exit Sub
   
HandleErr:
    Select Case Err.number
        Case Else
            MsgBox Err.number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

 

[LarryE]

Lovely folks,

I would like to be able to copy select fields from one entry to the next.

I have product return forms that can have an arbitrary number of items on it. The items may vary in serial number, asset tag number, and possibly product name. All other fields on the form will be the same. Each item will have it's own return record, with the same tracking number, date, OPI and other fields.

Simplified context:
Return Form
Tracking Number: Return_123
Return Date: Today's Date
OPI: John Doe
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductA-------Serial Number--------Asset Tag
ProductB-------Serial Number--------Asset Tag
ProductB-------Serial Number--------Asset Tag
ProductC-------Serial Number--------Asset Tag

It would save me a lot of time if I could click a button to copy all the unchanging fields and just leave the specific item fields blank.

Appreciate the insight.

Try using an APPEND action query. You can specify which record and which fields in that record to add to your table.

 

[HeavyD]

Thank you The_Doc_Man,
I'm exploring the Command Button Wizard -> Duplicate Record. I keep getting an error, "command or action 'paste' isn't available now"

If I click the button to save the record first, the DuplicateRecord works for about 3 entries, but then throws the same error.

I tried recreating the work flow of the RunMenuCommand->Save, Select, Copy, GoToNewRecord, Paste.

Any idea what is happening here?

 

[The_Doc_Man]

I suppose you could use a "Duplicate Record" but it would be better to do as I suggested. "Paste" involves the clipboard whereas what I outlined does not. Take a look at what MickJav showed you if you want to get fancy.

In the simplest case, you might have a button called MakeNew on the form.

Public Sub MakeNew_Click()
    If Me.Dirty then
        DoCme.SaveRecord
    End if
    DoCmd.GoToRecord , , acNewRec
End Sub

But let's say you had two text controls called Project and Agent that you wanted to copy if the next input is related to the current input. You would have a second button called MakeRelated to do that.

Public Sub MakeRelated_Click()
Dim strProject as String
Dim strAgent as String
    If Me.Dirty then
        DoCmd.SaveRecord
    End If
    strProject = Me.Project
    strAgent = Me.Agent
    DoCmd.GoToRecord , , acNewRec
    Me.Agent = strAgent
    Me.Project = strProject
End Sub

NOTE: There is NO error checking or validation in what I showed you because I have no clue as to your business rules. But this is what I meant for my previous discussion. In fact, the Button Wizards would probably put some error trap code in there just because that's what they do. The buttons check for the current record being dirty (modified) and save it if required, then give you a new record.

 

[Pat Hartman]

OR you can use the Order header to tie the returns to the order details. Start with a NOT updateable form bound to the order header. Use a combo in the subform to choose the item from the order that is being returned. You can then either add the returns to a separate table - not ideal. Or, add a return date and reason to the order details to tie everything together. You may also need a flag that indicates return to inventory or not.