VBA needed to extract subreport info to excel

[caseyc17]

Still plugging away on my little project. Have a new twist that I'm once again struggling with...

I've created some VBA to extract query results and display them on an excel template by clicking a command button on a form sheet.

I added a subform to the form.

I would like to extract the sub form info and display it on the excel template as well.

Here's the code so far:

.Range("c16").Value = Me.[2009 Deferral Plan.AwardGroup_Total_Amount].Value
.Range("a66").Value = RSP_AWARD_subform.[Grant_Date].value

Red code takes info from the form and displays it in C16 of an excel spreadsheet. works perfectly.

I intend to have the subform "RSP AWARD" and the content the subform and place them in cells in excel.

My struggles are with identifying the suform I think because I get a "object doesn't support this method" error.

Any help is appreciated.

 

[Dartagnan]

Not a 100% certain, but you may need to reference the main form and then the sub form.

I believe the syntax would be something like this:
.Range("a66").Value = Forms![main formm]!RSP_AWARD_subform.[Grant_Date].value

 

[caseyc17]

Thanks for the response. I tried several variations on your advice. I either received the object error or it couldn't find the information. It seems like you've set me down the right track, I'll search around a bit.

 

[caseyc17]

Taking a different approach perhaps?

Does anyone know if there is a way to identify two separate forms with VBA?

I.e. I have Form 1 and Form 2. Form 1 is my main form. Form 2 is the one-to-many information that I need to present. I'm trying different variations of:

.range ("j4").value = Me.[Data from form1].value
.range ("j4").value = Forms![Form 2].[Data from form 2].value

Lots of different errors depending on how I try it.

 

[caseyc17]

Still no luck...

Private Sub Command109_Click()​

On Error GoTo Err_Command109_Click

Dim oXL As Object
Dim oEXCEL As Object
Dim sFullPath As String
Dim sPath As String


Set oXL = CreateObject("Excel.Application")
sFullPath = CurrentProject.Path & "\Sample.xls"
With oXL
.Visible = True
.workbooks.Open (sFullPath)


.range("A12").Value = Me.[FIRST NAME].Value & " " & Me.[Last Name].Value
.range("A15").Value = Left(Me.[2009 Deferral Plan.Scheme_Name].Value, 4)

.range("c15").Value = Me.[2009 Deferral Plan.AwardGroup_Total_Amount].Value

.range("C17").Value = Me.[2009 Deferral Plan.Vesting1_Cash/Bonds_Principle].Value
.range("C19").Value = Me.[2009 Deferral Plan.Vesting3_Cash/Bonds_Principle].Value
.range("C18").Value = Me.[2009 Deferral Plan.Vesting2_Cash/Bonds_Principle].Value​

End with

Dim frm As Form

DoCmd.OpenForm [RSP Award Form]
Set frm = ActiveForm
With oXL
.Visible = True
.workbooks.Open (sFullPath)

.range("h52").Value = Me.[Grant Date].Value

End With

Exit_Command109_Click:
Exit Sub

Err_Command109_Click:
MsgBox Err.Description
Resume Exit_Command109_Click​

End Sub​