Copy values of Main and Subform (only main form working)

[Heatshiver]

I need to copy a main form and subform's values to a new record. I can get the main form to copy its values over with the following code:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdPasteAppend

However, this does not affect the subform at all and just leaves it blank. I have been scouring the web and have found what I thought would be viable methods such as setting focus to the subform and such. But nothing seems to work.

What is the best way in VBA to copy the values of all fields in a subform so that I can combine it with the code above? Thanks for the help.

 

[Heatshiver]

If I add:

Me!fsubSTOTAct.SetFocus

Above it, I can set the focus on the subform, however once saved, it merely saves over the current record being displayed instead of saving as a new one...

 

[vbaInet]

Can you tell us your reason for wanting to duplicate records? And as you can see already, you have to copy the subform separately from the main form.

 

[Heatshiver]

Many of the records will have the same information for many days. Instead of having to write everything over, I am attempting to have a simple method to use the values from a previous date carry over to a new date.

 

[vbaInet]

I'm still very sceptical about your approach. Reason being you have to paste into the main form then proceed to pasting into the subform. Now if you have any validation in your main form that disallows duplicate records (of any kind) it will error.

Anyway, I'm going to use a combination of what you already have with some other technique, so here's some aircode:

dim rs as dao.recordset

set rs = me.[COLOR=Red]subformcontrol[/COLOR].form.recordsetclone
rs.bookmark = me.[COLOR=Red]subformcontrol[/COLOR].form.bookmark

me.setfocus

' create new record and paste old record into main form
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdPasteAppend

' copy across the values of the subform
with me.[COLOR=Red]subformcontrol[/COLOR].form
    .[COLOR=Red]txtID [/COLOR]= rs![COLOR=Red]ID[/COLOR]
    .[COLOR=Red]txtSurname [/COLOR]= rs![COLOR=Red]Surname[/COLOR]
    [COLOR=Red].... etc ...[/COLOR]
end with

set rs = nothing

Amend the red bits.

For the last part of the code, you can also create the record within the recordset and requery the subform for the record to show.

 

[Heatshiver]

Thanks vbaInet! I knew there would be something like this to use. I was trying Allen Browne's method, but this seems simpler.

I have replaced the bits, but I am having a problem for the subform values. I don't believe I am entering them correctly. What would .txtID be in my subform? And what would rs!ID be in my subform?

For example, I have a field in the subform from the control source named "STOTActComments"...

This is what I have:

Dim rs As dao.Recordset

Set rs = Me.fsubSTOTAct.Form.RecordsetClone
rs.Bookmark = Me.fsubSTOTAct.Form.Bookmark

Me.SetFocus

' create new record and paste old record into main form
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdPasteAppend

' copy across the values of the subform
With Me.fsubSTOTAct.Form
.STOTActComments = rs!STOTActComments
End With

Set rs = Nothing

Thank you so much for the help! I desperately need it.

 

[vbaInet]

rs!ID will be the name of a field, whilst .txtID is the name of the control the field is bound to.

 

[Heatshiver]

I placed the name of the control source as rs!ID and the name of the field as rs.txtID, but I am getting an error 3265 (Item not found in this collection). The code now looks like this:

Dim rs As DAO.Recordset

Set rs = Me.fsubSTOTAct.Form.RecordsetClone
rs.Bookmark = Me.fsubSTOTAct.Form.Bookmark

Me.SetFocus

'create new record and paste old record into main form
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdPasteAppend

'copy across the values of the subform
With Me.fsubSTOTAct.Form
.STOTActComments = rs![Step Oil Tools Activity]
End With

Set rs = Nothing

If I switch the two around I instead get an error 2113 (The value you entered isn't valid for this field)..

 

[vbaInet]

Step Oil Tools Activity is not a field in your subform's record source.

And from the names of your field and textbox it's not apparent that you're copying the value to the right control.

 

[Heatshiver]

If I am understanding correctly, the .txtID is the caption name "STOTActComments", also the name of the field in the table. The rs!ID is the name of the field in the table, "Step Oil Tools Activity", the control source for that field.

I am assuming that I am not understanding the rs!ID part correctly.

Thank you for the help.

 

[vbaInet]

No. txtID is the name of the textbox.

The ID part of rs!ID is the name of the field that txtID is bound to.

Look at my code again. I'm matching ID to ID, Surname to Surname... etc.

 

[Heatshiver]

Okay, so I changed the name of the textbox to txtSTOTActComments, and put that in place of .txtID. rs!ID is rs!STOTActComments.

After doing so I get the error 440 (The value you entered isn't valid for this field).

 

[vbaInet]

Upload your db let's see what you're doing.

 

[Heatshiver]

I have uploaded the part of the DB I am working on. I really appreciate the help. This has been very troublesome for me.

The code rests in the checkbox.

 

[vbaInet]

What form is it?

 

[Heatshiver]

Sorry, the code is in frmSTOTAct.

frmUserID is meant to be first, which you then plug in 0004 for the UserID. This will open up frmSTOTAct.

 

[vbaInet]

I thought you had a bunch of fields you wanted to copy. You don't need the code I gave you then, just copy the Value of the textbox into a variable and do the opposite when pasting into a new record.

 

[Heatshiver]

I will need to copy a bunch of fields as there are other tables and forms/subforms with much more fields. I just have been using this table to test with until I was able to get a method to work.

 

[vbaInet]

How many fields are you talking about? It would be just as easy to use variables, no meddling with recordset code if you're unsure how it works.

Create a Type and use that type for your variable assignments.

 

[Heatshiver]

At the most, maybe 40-50 fields at a time.