copy into new record **

[GaryPanic]

OK guy i can copy into a new record nice and easy - here's the problem

I want to copy records 123 in to a new record (hold on!!!)
the new record has a numbering system
so the next number could be 250 (not 124) or it could be 2000

reasoning behind this record 123 is being renewed or an alternative option product is being provided ...
rather than fill in lots of informaiton again -is there a way of coping all the field s from record 123 and appending them to ( new record)
then I can edit the 3-4 field and hey presto ..

I tried append - and it appends to a blank record not the record ~i was on

 

[John Big Booty]

So, if I've understood you correctly; you have already created your new record and you want to import a whole bunch of information from a previous record, rather than appending that data to a completely new record

 

[SpentGeezer]

This may help??? I use to replicate records in a database, because replication then a few changes is quicker than starting from scratch in some instances.

Private Sub btn_copy_Click()
On Error GoTo Err_btn_copy_Click
Dim Rd_Set
Dim tbl_NamE
Dim autonumber
Dim obj_cnt As CONTROL
Dim obj_FIELDS
Dim s

s = MsgBox("Are you sure you want to make another copy of this record in the database?", vbYesNo, "Replication")
If s = vbNo Then
    Exit Sub
End If

tbl_NamE = "tbl_maintenance"
Set Rd_Set = CurrentDb.OpenRecordset(tbl_NamE)

If Rd_Set.recordCount > 0 Then
    Rd_Set.MoveLast
    autonumber = Rd_Set!OBJECTID.Value
    autonumber = autonumber + 1
Else
    autonumber = 1
End If

MsgBox "The New records Job Number is: " & autonumber, vbInformation, "FYI"
Rd_Set.AddNew
Rd_Set!OBJECTID = autonumber
Dim RST
Dim theINT
Dim strQueryName, qryDEF, qrySTAT
'Set RST = Me.CurrentRecord
theINT = txt_UniqueID.Value

    strQueryName = "qry_Copy"
    For Each qryDEF In CurrentDb.QueryDefs
        If qryDEF.Name = strQueryName Then
            CurrentDb.QueryDefs.Delete (strQueryName)
        End If
    Next qryDEF
    
    qrySTAT = "SELECT tbl_maintenance.*"
    qrySTAT = qrySTAT + " FROM tbl_maintenance"
    qrySTAT = qrySTAT + " WHERE ((tbl_maintenance.OBJECTID)= " & theINT & ");"
        
    Set qryDEF = CurrentDb.CreateQueryDef(strQueryName, qrySTAT)

    Set RST = CurrentDb.OpenRecordset(strQueryName)
    
    Dim Count
    Dim i
    i = 0
    Count = RST.FIELDS.Count
    Do Until i = Count
        If Not RST(i).Name = "OBJECTID" Then
            Rd_Set.FIELDS(i).Value = RST.FIELDS(i).Value
        End If
        'If Not IsNull(Rd_Set(i).Value) Then
            'MsgBox Rd_Set.FIELDS(i).Value
            'MsgBox RST.FIELDS(i).Value
        'End If
        i = i + 1
    Loop
    
    'make a copy of the attahcment
Dim theAttach
theAttach = Rd_Set!mem_photos.Value

Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")
If objFSO.FileExists(theAttach) Then
    objFSO.copyfile theAttach, theAttach & "_COPIED_" & autonumber & ".pdf"
    Rd_Set!mem_photos.Value = theAttach & "_COPIED_" & autonumber & ".pdf"
End If
    
    
    Rd_Set.Update



Exit_btn_copy_Click:
    Exit Sub

Err_btn_copy_Click:
    MsgBox Err.Description
    Resume Exit_btn_copy_Click
End Sub

 

[GaryPanic]

So, if I've understood you correctly; you have already created your new record and you want to import a whole bunch of information from a previous record, rather than appending that data to a completely new record

thats what i had in mind - or go to the old record and press create new record - but the numbering is the thing that throws me - i have get next number function as my reference number (not auto number)

 

[GaryPanic]

SG--++
i will have to read this one through to see if i can folow it

regards

 

[vbaInet]

If the IDs from the source table will be sorted in ASC order then there's a more succint way.

 

[GaryPanic]

ignore my solution it was over typing the orginal record...arrrh

 

[GaryPanic]

from an old post what do the number represent ?



Or, if you're not comfortable working with queries, you could just add a line or lines to the Wizard generated code to reset the values of the fields you don't want copied:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.Textbox1 = Null
Me.Textbox2 = Null
Me.Textbox3 = Null

People say the Wizard generated code is going to disappear, but they've been saying that for years, and, in pint of fact, Microsoft couldn't do that, for backward compatibility reasons.

 

[vbaInet]

I may have read your requirement wrongly, but if it's a direct copy and paste then here:

    With DoCmd
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdCopy
        .RunCommand acCmdRecordsGoToNew
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdPaste
    End With

    Me.[COLOR=Red][B]ID[/B][/COLOR].Value = Nz(dMax("[[COLOR=Red][B]Field[/B][/COLOR]]", "[COLOR=Red][B]TableName[/B][/COLOR]"), 0) + 1

If it requires you to find the record based on certain criteria before pasting, then here:

    Dim rs As DAO.Recordset, i As Integer
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM TableName WHERE [SomeID] = " & Me.txtID, dbFailOnError)
    
    DoCmd.RunCommand acCmdRecordsGoToNew
    
    With rs
        If .RecordCount <> 0 Then
            .MoveFirst
            
            For i = 0 To .Fields.Count - 1
                Me.Controls("txt" & .Fields(i).Name) = .Fields(i)
            Next
' OR
'            Me.txtbox1 = !Field1
'            Me.txtbox2 = !Field2
'            Me.txtbox3 = !Field3
'            .   .
'            .   .
'            .   .
        End If
    End With

    Set rs = Nothing

    Me.IDTextbox.Value = Nz(dMax("[Field]", "TableName"), 0) + 1

 

[GaryPanic]

option 1 looks promising - option two looks like a lot of coding ( the prinicple table is quite large )

I'll giv e it a go and let you know

regards
g

 

[GaryPanic]

After a few tweaks - its works- i could hug you - well may be ....
I had locked a few fields on the "old record" and it wasn't copying these - but I wrote unlocked this and it copied everything (I have the fields locked when some enters data in to them - it locks the screen as soon as they leave the record - stops accidents from happening - downside - you have to keep unlooking it to alter the record - I can live with that (histroy table - total lockout)

this is a real saver ..
the end users will then only have to change 3/4 fields 2 dates -1 contract
or in my case 1 contract and all of the work is done
considering there are about 50 fields (on the main record )

Many thanks - I have been racking my brain over this

 

[vbaInet]

it locks the screen as soon as they leave the record - stops accidents from happening

What about prompting them before changes are saved?

Glad to hear that worked for you!

 

[GaryPanic]

What about prompting them before changes are saved?

Glad to hear that worked for you!

No i want it locked - they can go to a tab -click a button and unlock -

basically if they accidently lean on the keyboard it will change data - so my lock method works - (but thanks for you view)

 

[vbaInet]

No probs!