VBA Export subform

1
votes

Im trying to on button export a subform but all I am getting it various errors mostly It cant seem to recognize the subform object. The closest I am I think is the following : 

    DoCmd.OutputTo acOutputForm, Me.Form.<Subform_name>.Name, acFormatXLSX, savefileas, True

subform_name is the object name in the main form Subform_form the source form

the sub form doesn't come from a separate query but record set is set from VBA of the parent form

The error on this vba is 2102 = "the form name is misspelled or refers to a form that doesn't exist."

savefileas =

    Public Function savefileas() As String
Dim fd As FileDialog, filename As String

On Error GoTo ErrorHandler

 Set fd = Application.FileDialog(msoFileDialogSaveAs)

 If fd.Show = True Then
     If fd.SelectedItems(1) <> vbNullString Then
         filename = fd.SelectedItems(1)
     End If
 Else
     'Stop Code Execution for Null File String
     End
 End If
 savefileas = filename & ".xlsx"

 'Cleanup
 Set fd = Nothing
 Exit Function

ErrorHandler:
 Set fd = Nothing
 MsgBox "Error " & Err & ": " & Error(Err)
 End Function
2
ms-accessvba
Try to use Me.<Subform_name>.SourceObject instaed of Me.Form.<Subform_name>.Name – Sergey S.
That works thank you, it is however showing the complete list though and not the displayed list which has been filtered by the parent forms setting of record source. I guess that is due to setting it to pull the raw subform rather than the nested one with the crafted record source – user681413

2 Answers

2
votes

I don't think this is going to work.

DoCmd.OutputTo wants to open the object standalone, then export it.
The second parameter, ObjectName is not an object reference, but the object name.

It doesn't work on an already open object, even less on a subform with modified recordsource.

A better way is to create a temporary querydef with the subform's recordsource.
Then export this query.

1
votes

I have this working how I want now thanks to nudge from Andre, may not be the most efficient method but im getting the results I want.

To help any others that may come to this for a similar solution.

Im exporting to csv which was my ideal format I wanted.
Export button : 

    Private Sub Command2_Click()
DoCmd.TransferText acExportDelim, exportcsv, "export_query", savefileas, True

End Sub

On Open

    Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim export_query As QueryDef
Dim db As Database
Set db = CurrentDb
strSQL = <sql statement as defined by set record code>

<code that sets my subform record source> 

Set export_query = db.QueryDefs("Export_Query")
export_query.SQL = strSQL    
End Sub

Then each filter on update is a copy of the on open, so its setting the query used for export each time its setting the subform recordset IE what is shown.