Duplicating a Record By Means of a Form Button?

[Maillme]

Hi there,

I have a form, which i enter data into, and i want to have a button on it, which when i press it will insert a new record, with all the same info (but with a different ID, which is an autonumber field).

My form/table has look-up's in it, not to sure if this will complicate matters,

many thanks for any help,

Neil

 

[Remou]

Hi Maillme
This is generated by the duplicate record wizard (Access 2000):
[tt]DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append[/tt]

Which translates to:
[tt]DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend[/tt]

Or did you have something different in mind?

 

[Maillme]

Hi Remou,

thanks again for your help1

Sorry, i never even knew that wizard existed, and it seems to work perfect for me - thanks again!

Neil

 

[Remou]

You are most welcome.
Some wizards can be annoying in Access 2000, because they generate code that the help says is out of date, as in the example above. Then there are so many of them ...

 

[Maillme]

Remou,

just to complicate matters a little further, is it possible, that after my duplicate record, i could then clear 2 of my fields? These being the first name and surname fields:

first_name
surname

many thanks - im not that great with access, but with the help of this forum, becoming better

Also, i think i shoudl possibly change form a technical user to somethign else, but i cant see where ot do this??? it doesnt appear to be under profile!

thanks again,

Neil

 

[PHV]

Also, i think i shoudl possibly change form a technical user to somethign else
Click the Contact Us link on upper right of this page and send a mail explaining your issue to management.

 

[Remou]

You can empty the fields with:
Me.first_name=" "
Me.Surname=" "
After you have run the copy. Note the space "<space>". This is because your field may not allow zero-length strings.
Great, isn't it? I would not be surprised to find that even the gurus learn something from these fora.
I have no idea how to change your description, but there may be a post about it ...

 

[Maillme]

that seems to work fine....BUT........ti doesnt take me to my new duplicated record - it keeps my on the one of duplicated, then if i navigate, i can see my duplication.....

id rather it took my to the record,

any idea's?

thanks again,

Neil

 

[Remou]

I am a bit puzzled, because in my version of Access it takes me to the new record. Could you try setting up a very simple form with temporary table and see if that works? You need to note the record number in the record selector bar.

 

[Maillme]

well, the funny thing is, it shows the next record in the record bar....e.g. if there are 14 numbers in my database, then i duplicate record, it then shows 15 - but it doesnt take me to my duplication??! weird......

if o go back, and forward to it, all my fields that i ask to be deleted, are deleted......


Neil

 

[Remou]

So, say you are on record 1 the record bar shows 1 of 15, you then click duplicate. What does the record bar show? <clutches at straws>

 

[Maillme]

ok.


I go to record 1 - the record bas shows 1 of 12

I press my duplicate button, the record bar then shows 13 of 13

But, the info doesnt clear is is supposed to.....so

I click my record bar back one (to 12) then forward one (to 13) - and my title, first_name and surname fields are clear, as is supposed ot be.

Weird eh ......

Neil

 

[Remou]

Ah Great! I was missing what you were saying. Please post your code.

 

[Remou]

Oh, and you could add a Me.Refresh after the code that clears the fields, if that does not work, then please post your code.

 

[Maillme]

Me.Refresh didnt work :9

---------------- Code: -----------------------------

Private Sub Command44_Click()
On Error GoTo Err_Command44_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
'clear firstname, surname and title fields
Me.first_name = " "
Me.surname = " "
Me.title = " "
Me.Refresh


Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click



End Sub

-------------- End Code -----------------------

thanks again,

Neil

 

[Remou]

Odd, alright. I have tried this and it seems to work. Change
Me.Refresh
to
DoCmd.GoToRecord , , acLast

 

[Maillme]

That done it! Im using Office 2003 if that has anythign to do with it??

Anyway, one last question, then i shall let you's all get peace

How can I, after going to this record set the focus to the first_name box?

thanks,

Neil

 

[PHV]

Have a look at the SetFocus method of the TextBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

I don't think so. Where I went wrong was in not trying to edit the record, once I did I had the same problem as you.

DoCmd.GoToControl "Firstname"

Enjoy