Creating Duplicate Fields

[demax182]

Hi,

First off, this forum has to be the best forum to go to when in need of help. Responses are always quick. Now to my question, I know you can create duplicate records, but it duplicates all the fields of your previous record. Is there a code to duplicate only certain fields of your record? Again, thanks in advance for all your help.

 

[SiJP]

using recordsets yes..

dim strmyOldFieldValue as string
dim strmyNewFieldValue as string
dim strSQL as string
dim rs as dao.recordset

strSQL = "SELECT yourfield FROM yourtable WHERE critera"

set rs = currentdb.openrecordset(strSQL)

strmyoldfieldvalue = rs("yourfield")

with rs
.addnew
!yourfield = strmyoldfieldvalue
.update
end with

rs.close

.... air code

------------------------
Hit any User to continue

 

[SiJP]

strmyNewFieldValue wasn't needed.. sorry.. was typing code as I thought of it ;D

------------------------
Hit any User to continue

 

[MartyBoy]

Hi Demax182

At the risk of exposing my relative inexperience, but at the same time trying to save true soldiers like PHV and TheAceMan1 from spending so many hours answering so many questions ....

You can do what you want to do in duplicating certain fields to the next new record easily enough using code. I don't know if you are familiar with Access VB, but by saving the required fields to variables in an AfterUpdate event from the previous record, the values stored in the variables can then be used to populate the required fields of the next record. This can easiest be done by using somthing like the following code (abreviated) ...

Sub AfterUpdate()
' Declare Variables (however many are needed)
Dim CopyField1, CopyField2, CopyField3 As String 
' or As Date, Variant, Integer etc as appropriate for the field types 

' Copy required fields to variables
CopiedField1 = Me![i][DataField1][/i]
CopiedField2 = Me![i][DataField2][/i]
' etc etc to save the current values. [i]DataField1, 2[/i] etc are your fieldnames from your form (I presume?)

' Create a new record
Me.MoveNext
If Me.NewRecord = True Then
Me![i][DataField1][/i] = "CopiedField1"
Me![i][DataField2][/i] = "CopiedField2"
' etc etc to fill all the required new fields
End If 

' Put the cursor into the first field to require new data
Me![FieldNameX].SetFocus

End Sub

I "think" the above will work, I haven't tested what I just typed, but it indicates a prcoess I have used before.

If you aren't familiar with Access Basic then post a reply and I will provide a tested example.

Good Luck

 

[SiJP]

Martyboy, reading through your code it's pretty spot on too - Don't do yourself such injustice!


------------------------
Hit any User to continue

 

[FancyPrairie]

Another way to do it is to set the AfterUpdate property of each of the controls you want to duplicate. Like this:

AfterUpdate.....=DuplicateField()

Then insert the following function into the module of your form:

Function DuplicateField()

    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl
    
    ctl.DefaultValue = """" & ctl.Value & """"
    
End Function