Datasheet view custom fields duplication (1 Viewer)

[megamef]

Hi All,

I have a database with a large number of records: ~2000, so I use a form in datasheet view to filter and sort the records to find a particular one.

This works fine for locating a certain record.

Sometimes I then need to make a new record but I will be duplicating a few fields from the one I just found.

At the moment I manually do this by copying each field I want into word and then pasting as appropriate into fields in the new record.

Is there anyway I can still use datasheet view to search for a record, then select certain fields and have them duplicated into a new record?

Cheers Steve

Edit: I should add that the datasheet view is locked for editing, I have a separate form in single form view for entering a new record. Sort of vital information i missed out there.

 

[CJ_London]

If you are just using access functionality, then I would filter the form so the record you want to copy is at the bottom - it needs to be the last record before the new record row

Then for the columns you want to copy, in the new record row, hit ctrl-' (single quote)

alternatively just copy and paste column by column

Or select the record you want to copy, copy it and paste it into the new, then modify the columns you want to change (autonumber fields are not copied, a new one will be assigned)

 

[megamef]

Sorry CJ,

I just edited my post to include information I missed out the first time I explained it.

the datasheet view is locked for editing, I have a separate form in single form view for entering a new record.

So really I'd need my data sheet view as a sub form within a single form and then a button on the single form labeled "select fields to duplicate", then the user clicks that button, then clicks the fields they want in the subform, then they press another button labeled "duplicate selected fields in new record". These actions then open my new record single form with the selected fields duplicated.

 

[CSL]

You can get the values for the new records by using something like


!NewRecordField = Form_FormName!SourceField

but yes you would need to somehow select which fields you wanted if , the whole record wasn't required.

 

[CJ_London]

it would be a lot easier if your entry form is your main form for entering new records (or could be another subform). Going with the easiest option...

The main form would have a recordsource for the table and set to data entry/allow additions.

In the form header or footer you would put the subform where you can select the record you want to copy

Also in the header section you would have your button, behind which the code would be something like

private sub btnCopy_click

    if msgbox("You want to copy this record?" & vbcrlf & vbcrlf & mysubform.form.customername,vbyesno)=vbyes then 'change customername to a relevant field
        me.customername=mysubform.form.customername
        me.ordernumber=mysubform.form.ordernumber
    end if

end sub

to use, you would select a record in the subform then click the button

code not tested and ensure you change names to suit your own control/field names

If you want your entry form to be a subform on the main form the code would be much the same except you would change

me.customername=mysubform.form.customername
me.ordernumber=mysubform.form.ordernumber

to

me.entrysubform.form.customername=mysubform.form.customername
me.entrysubform.form.ordernumber=mysubform.form.ordernumber