How to export data in a subform to Excel

[maff811]

Hello,

I have been on several forums, but haven't quite yet managed to crack this one. I think I am also getting tied up because of the Navigation Form.

What I am trying to do is export the data contained in an unbound subform that displays data based on parameters that a user has entered on the main form. I have no issues getting the data to display in the subform, but I am unable to get the code right to export that data to Excel.

The forms I am using are:

1. Navigation Form
2. ExpenseSearchForm (Main Form)
3. ExpenseSearchSubform (Subform)

The code I have included on a command button is:

DoCmd.OutputTo acOutputForm, Forms![Navigation Form]!NavigationSubform.Forms!ExpenseSearchSubform, acFormatXLS, "C:\Expenses.xls", True

When I click the command button, I get:

Run-time error '438':
Object doesn't support this property or method

I think I am close, but cannot get it across the line.

Any help would be greatly appreciated.

 

[theDBguy]

Hi. You could try using Excel Automation to transfer the data. Or, you could try using a temporary table or query to transfer the data.

 

[maff811]

Thanks theDBguy. I have seen these suggestions mentioned elsewhere. Before I explore those suggestions, can you tell if the code I have used is flawed?

 

[theDBguy]

Thanks theDBguy. I have seen these suggestions mentioned elsewhere. Before I explore those suggestions, can you tell if the code I have used is flawed?

Probably, but only because you're trying to export a subform. Try doing a small test of exporting a popup form, just to see if that approach has merits. I can't tell you for sure since you didn't show us how you're populating your unbound subform.

 

[maff811]

I populate the subform based on unbound fields in the Main Form. The subform is an unbound form also.

Does this assist?

SELECT Expenses.ExpenseID, Expenses.DatePaid, ExpenseTypes.ExpenseType, Expenses.Description, Properties.Address, Properties.Suburb, Properties.State, Properties.Postcode, Expenses.Amount, [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Or [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Is Null AS Expr1, [Description] Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] & "*" Or [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] Is Null AS Expr2, [DatePaid] Between [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] Is Null Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Is Null AS Expr3
FROM (Expenses INNER JOIN Properties ON Expenses.Property = Properties.PropertyID) INNER JOIN ExpenseTypes ON Expenses.Expense = ExpenseTypes.ExpenseTypeID
WHERE ((([ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Or [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Is Null)=True) AND (([Description] Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] & "*" Or [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] Is Null)=True) AND (([DatePaid] Between [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] Is Null Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Is Null)=True))
ORDER BY Expenses.DatePaid DESC , ExpenseTypes.ExpenseType, Expenses.Amount;

 

[theDBguy]

I populate the subform based on unbound fields in the Main Form. The subform is an unbound form also.

Does this assist?

SELECT Expenses.ExpenseID, Expenses.DatePaid, ExpenseTypes.ExpenseType, Expenses.Description, Properties.Address, Properties.Suburb, Properties.State, Properties.Postcode, Expenses.Amount, [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Or [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Is Null AS Expr1, [Description] Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] & "*" Or [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] Is Null AS Expr2, [DatePaid] Between [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] Is Null Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Is Null AS Expr3
FROM (Expenses INNER JOIN Properties ON Expenses.Property = Properties.PropertyID) INNER JOIN ExpenseTypes ON Expenses.Expense = ExpenseTypes.ExpenseTypeID
WHERE ((([ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Or [ExpenseType]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ExpenseType] Is Null)=True) AND (([Description] Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] & "*" Or [Forms]![Navigation Form]![NavigationSubform].[Form]![Description] Is Null)=True) AND (([DatePaid] Between [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeFrom] Is Null Or [Forms]![Navigation Form]![NavigationSubform].[Form]![DateRangeTo] Is Null)=True))
ORDER BY Expenses.DatePaid DESC , ExpenseTypes.ExpenseType, Expenses.Amount;

Hi. It's just terminology. I couldn't imagine an unbound form to display multiple records. To me, once you apply a Record Source to it, then you are making it bound. So, I would try either converting that SQL into a Make-Table query or apply it to a temporary QueryDef object, so you can export it. Of course, there's also the option of using the subform's Recordset and Excel Automation.

 

[maff811]

OK, thanks for the advice. I'll look into those suggestions you have outlined.

 

[theDBguy]

OK, thanks for the advice. I'll look into those suggestions you have outlined.

Let us know how it goes.