Duplicate a record and add to revision #

[karmacable]

I'm trying to figure out the code for duplicating a record and having a textbox (that keeps track of the revisions for that record) increase it's number counter up automatically.

I used the macro for creating a command button for duplicate record, but when I converted it using Access 2010's 'Convert Form's Macros to Visual Basic' button (still an iffy function), I get all kinds of errors, otherwise it would be pretty simple for me to code in the counter. If it's simpler to add to the macro, please let me know the steps.

Thanks,

 

[karmacable]

Okay, so I figured out the VBA to write, but my logic for the counter does not appear to be working. Here's the code;

Dim Revision As Integer
Revision = Me.txtRevisionNumber.Value

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Revision = Revision + 1

Exit_cdmDuplicate_Click:
Exit Sub

The txtRevisionNumber.Value has been set to 0 as default. I thought the above code would up the value of the textbox to 1, and so on for each revision. However, it simply copies the record and the textbox remains at 0? Where's my logic/coding failing?

Thanks,

 

[missinglinq]

Dim Revision As Integer
Revision = Me.txtRevisionNumber.Value

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Revision = Revision + 1
[B][COLOR="Red"]Me.txtRevisionNumber.Value = Revision[/COLOR][/B] 


Exit_cdmDuplicate_Click:
Exit Sub

FYI, Value is the Default Property for Textboxes, and because of this it can be omitted.

Me.txtRevisionNumber

means the same as

Me.txtRevisionNumber.Value

Linq ;0)>

 

[karmacable]

Perfect, that seems to do it!

One more question, is there anyway to get rid of the Access message "You seemed to have copied a large amount onto your clipboard....", which happens after you've duplicated a record and are ready to close your db?

Thanks again for the pointer missinglinq!

 

[missinglinq]

Here's a hack from the incomparable Stephen Lebans for doing just that.

Place these API Declarations at the top of the Form's Code Module

Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long)
As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Then use this code after your Paste/Append code to empty the Clipboard

'Open, Empty and Close Clipboard
Call OpenClipboard(0&)
EmptyClipboard
CloseClipboard

Linq ;0)>