Create copy, duplicate of a record on form & subform. 1

[xollef]

Hello
I have a mainform with a subform and I would like to have a button on the mainform with which creates duplicates of both forms records. Oh, a copy of all info into the new record, except the JobID that links the forms together.

It is because there can be similar Jobs but they should be recorded under different numbers.
I have got it so far that I used the button wizard to create the duplicate record function on the main form. It duplicates all info except the JobID that will get a new IDnr.
I can create a similar button on the subform that does the same thing but I can't get the one button on the mainform to duplicate both forms. How can this be done?

Thanks in advance.

 

[JJOHNS]

I just did the same thing. The way I got it to work is really ugly, but effective. First, I created a query to call the record I want to copy. Then I created a copy of my main form and based it on the query, so when it opens, that record is displayed. Then I created another form for data entry. That means I now have three copies of my main form with subforms included.

Next I created a macro that opens the form based on the query and then opens the entry form on top of it.

I went into the entry form and set all the default values of the fields to equal the fields in the other form. The problem here was that I had to create a second copy of every subform, too, and put that in the entry form.

I hope you can follow what I did. It works, except you have to remember to close both forms when you're finished. Also, to avoid confusion with my users, in the form based on the query, I set all the field and button visible properties to false, so if the user happens to use the "X" to close the entry form, they'll only see a blank background.

 

[xollef]

I thank you for your reply, I think I can follow what you mean, but in my opinion it is a little to messy way to do this isn't it. Isn't there an easyer way?

I thought about creating the duplicate record button on both the forms and then hiding the button that is on the subform. And thereafter link the duplicate button that is on the mainform so it would perform two tasks. One to duplicate mainform, second to activate the second button, call it or something, like it would press it.

Isn't there someone that could answer if that is possible and then how to do it in that case(I am not a good VB coder, can copy and paste?

Thanks in advance.

 

[SA812]

just keeping it on top! I'd love to know how to do this!

SA

 

[JJOHNS]

I've got some code you can try. It worked for a coworker on another program, but I kept getting an automation error when I tried it. I even posted a question here two weeks ago about the same thing, but got no response. Anyway, this would be much better if you can get it to work:

Private Sub PositionNo_AfterUpdate()
Dim JobInfo As Recordset, SQLText

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
[Forms]![Job Announcement Bulletin]![JAB Sub].Form![Position No] = [Forms]![Job Announcement Bulletin]![JAB Sub].form![PositionNo]

SQLText = "SELECT * FROM [Query1] WHERE [PositionNo] = '" & Me![PositionNo] & "'"

Set JobInfo = CurrentDb.OpenRecordset(SQLText)
Me![HospitalCode] = JobInfo![HospitalCode]
'Me![AllYourFields] = Recordset![AllYourFields]

DoCmd.SetWarnings False
DoCmd.OpenQuery "UpdateJABCloseDateforAdd"
DoCmd.SetWarnings True
End Sub

This code pulls the necessary record from the table and then sets all the values of the current form to the same thing as the record. Every time I tried it, I got an automation error. However, I don't think I included the last three lines when I tried it, so maybe I've just discovered the problem that caused me about three days worth of headaches. Anyway, try it and see if it works for you.

 

[xollef]

Hey again.

Thanks again, JJOHNS but either I misunderstood completely or it didn't work. It is an afterupdate event which means it can't be placed on the duplicate buttons click even't. Am I still hanging in there or?
So where place this event if I wan't to press a button to duplicate the record on both the mainform and subform? I changed it to an click event and changed the forms to match mine and it still didn't help.

But back to my second question actually. Because the code I have for my duplicates button works. It creates a new record with the same info on it as the one record I was on when pressing the button. And I wouldn't have any problems creating a similar button on the subform also.

But then how to make the first button "press" the second button I can't figure out. I thought that would be very easy. Just with the wizard make duplicate record buttons on all subforms (if you have more than one) and then make them invisible. And then create a button on the mainform that "presses" all of the buttons. Like a macro that calls another macro. But when creating these buttons with the wizard it creates private sub functions. Can you call them with lets say a macro.

Or to turn this around I know how to call a macro with a macro. So if I could create the duplicate record function as a macro and assign it to a button, like I have described. One button on mainform and then one for each subform.
Then i could call them with another macro that I could assign to a button on a mainform and name, Copy jobinfo to new record.

Am I making any sence at all?
Hopefully someone can point me into the right direction of making this work.

 

[JJOHNS]

Now that I look back at your original question, I think you're already doing the hard part.

You have a button that is making a duplicate of the main form. You want the same button to also make a duplicate of the subform. You need to go into the subroutine and add some lines. Under each of the lines in the subroutine that reference the main form, you need to write the same line, but referencing the subform, so that every command carried out on the main form is also carried out on the sub form. You won't be able to do this with a wizard.

If you could copy the subroutine behind your button and put it up here, I might be able to help you with that.

 

[xollef]

Yes I actually have the button yes. As I have discribed it functions and if I create a similar button on the subform it functions also. But can't get this to duplicate both records, or call the other button or something.

The subroutine behind it is this:

Private Sub duplicate_record_Click()
On Error GoTo Err_duplicate_record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_duplicate_record_Click:
Exit Sub

Err_duplicate_record_Click:
MsgBox Err.Description
Resume Exit_duplicate_record_Click

End Sub

 

[JJOHNS]

Try this. First create a macro, such as mcrDuplicate

In design view, first line of macro is OpenForm (choose the subform

Second line is RunCommand (choose Duplicate)

Third line is close (Close subform)

Then call the macro after your three lines of code run:


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, ,acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, ,acMenuVer70

Dim stDocName as String
stDocName = "mcrDuplicate"
DoCmd.RunMacro stDocName



I haven't tried it myself, so I hope it works.

 

[xollef]

Oh thank you finally precisely what I was looking for I think I have tested the runcommand function as before but it didn't find any commands because I didn't open the subform first. I think it was that, anyway I got it working now.

If I could I would give five stars, but can only give you one more. As I suspected it was quite easy. ;] And thanks again a lot!