Copy record into same table

[PuddleJumper]

First post: I need to use the form's recordsource(recordset?) of a record to copy it back into the table (an exact copy). Reason: When inspectors go out and have "identical" writeups (passes/fails) it saves time on entry. I used the following but now I did something to mess it up:

Do Until MultiCounter = TempCounter
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
DoCmd.SetWarnings True
MultiCounter = MultiCounter + 1
Loop

Used a loop to "multiply" the record.

Should I delete the forms recordsource and stay with a recordset?
- Problem I had was after copying, the recordsource would go to last record

Can you provide sample script?

 

[vbaInet]

Why are you needing to loop?

 

[PuddleJumper]

This allows me to multiply the number of times the record is required. for example, inspector looked at 5 Techical Orders (T.O.'s) and they all were good. I can capture their five inspections without having to type each one over again.

 

[vbaInet]

What your code is doing is capturing the first record, pasting it into the FIRST record, thereby overwriting it, next loop capture's the FIRST record again and pastes into the FIRST record.

I would have thought the sequence would be:

Do
Capture from A
Create new record in B
Insert into B
Loop

 

[PuddleJumper]

Are you saying then I should "move" to new record like:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

Do Until MultiCounter = TempCounter
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdPaste ' or CmdPasteAppend?
DoCmd.SetWarnings True
MultiCounter = MultiCounter + 1
Loop

 

[vbaInet]

No, it was just a thought. I guess that's what PasteAppend does but I was just wondering if it would make a difference.

Is the PasteAppend working for one record?

 

[PuddleJumper]

No, I see no newly added records from the process. Was wondering if I should resort to a recordset verses a recordsource...

The current record remains on the form still and the record counter moves by 1...but no records

 

[PuddleJumper]

Update: I replaced the "Error$" back into the error reporting...It states I'm missing a required field...shouldn't this be copied as well during the process?

 

[vbaInet]

The only way it would work is if you remember the bookmark of the last copied record, then move to the next record, replace bookmark variable, copy, paste, next.

 

[PuddleJumper]

Okay I think I'm following:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
AdminBookmark = Me.Bookmark
Do Until MultiCounter = TempCounter
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPasteAppend
DoCmd.SetWarnings True
Me.Bookmark = AdminBookmark
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
MultiCounter = MultiCounter + 1
Loop

Except this this missing field...sounds logical.
Thanks by the way

 

[vbaInet]

What's the exact error message?

 

[PuddleJumper]

vbaInet...you got me on track...almost their!

The error was stating a field was not being copied to the next record even with data in the original. Figured out that somehow I changed the "Enabled" and "locked" properties of the form for this field! Changed back to "Enabled = yes" and "Locked = no" and I'm coping records again. Just got to take myself back to original starting point for the "next record". Testing now...

 

[vbaInet]

Here's what I meant:

Do Until MultiCounter = 5
    MultiCounter = MultiCounter + 1
    AdminBookmark = Me.Bookmark
    DoCmd.RunCommand acCmdCopy
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdPasteAppend
    DoCmd.SetWarnings True
    if MultiCounter <> 5 then
        me.boookmark = AdminBookmark
        DoCmd.RunCommand acCmdRecordsGoToNext
    end if
Loop

Untested code though.

 

[PuddleJumper]

I'll test your shorter version...but now I'm smokin'. Wow, I needed this for our aircraft inspection at Vance AFB on Sunday....your SUPER!!!!

And I did a lot of searching on this site...Thanks vbaInet!!!

Thanks again,
Ed

 

[vbaInet]

You're welcome

Glad it's working now.

 

[PuddleJumper]

Final reply: My working code...
Dim AdminBookmark As Variant
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
AdminBookmark = Me.Bookmark
Do Until MultiCounter = TempCounter
MultiCounter = MultiCounter + 1
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdPasteAppend
DoCmd.SetWarnings True
Loop
Me.Bookmark = AdminBookmark
End If
End If
DoCmd.GoToRecord , , acNext

 

[marnieg]

vbaInet...you got me on track...almost their!

The error was stating a field was not being copied to the next record even with data in the original. Figured out that somehow I changed the "Enabled" and "locked" properties of the form for this field! Changed back to "Enabled = yes" and "Locked = no" and I'm coping records again. Just got to take myself back to original starting point for the "next record". Testing now...

Yest this works if you Enabled=yes and Locked=no, BUT I don't want the user to click in the field and update it. Is there another property setting I should set?

 

[vbaInet]

Only just got time to do this. See attached.

Things to note:
1. You can add controls you want to be excluded from the copy process.
2. It doesn't copy controls that are Null because it doesn't need to.
3. It doesn't copy controls that have a calculated Control Source. Again it doesn't need to because they are read-only.
4. I've commented out some of your code that wasn't compiling or was interferring so you can uncomment it at your leisure.

I was going to use a class for this but you probably won't have understood it so I went with a module.