How to export data in a subform to Excel (1 Viewer)

maff811

Registered User.
Local time
Today, 16:00
Joined
May 1, 2014
Messages
45
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:

Code:
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

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:00
Joined
May 1, 2014
Messages
45
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

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:00
Joined
May 1, 2014
Messages
45
I populate the subform based on unbound fields in the Main Form. The subform is an unbound form also.

Does this assist?

Code:
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’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
I populate the subform based on unbound fields in the Main Form. The subform is an unbound form also.

Does this assist?

Code:
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

Registered User.
Local time
Today, 16:00
Joined
May 1, 2014
Messages
45
OK, thanks for the advice. I'll look into those suggestions you have outlined.
 

Users who are viewing this thread

Top Bottom