I am trying to use Bob Larson’s excellent Export A Form's Recordset To Excel code. It works of course, but I am using a datasheet view in my subform and I am hiding columns based on the users selection. I only want to export the visible recordset from the subform. There are over fifty possible fields but the user might only select a few fields to query and the user will not want all the data fields exported. Bob’s code exports the filtered data but it also exports all the data fields. Anyway to tweak Bob’s code to do what it does but only export the visible fields?
Below is the code I am using to hide the columns when the subform is opened…
Code:
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If DLookup("[InUse]", "tblFields", "FieldName = '" & ctl.Name & "'") = -1 Then
ctl.ColumnHidden = False
Else
ctl.ColumnHidden = True
End If
End If
Next
Ooh, that is a challenge. I think what you would need to do is to iterate through the columns and get the FIELD names that go with those columns and then create a SQL statement which basically takes the form's recordset and does an additional SELECT but only with those fields and then export THAT recordset. Does that make sense?
Ooh, that is a challenge. I think what you would need to do is to iterate through the columns and get the FIELD names that go with those columns and then create a SQL statement which basically takes the form's recordset and does an additional SELECT but only with those fields and then export THAT recordset. Does that make sense?
Forgive my naivety, but what would an additional SELECT look like in a SQL?
Here is the simple select SQL...
SELECT tblMyTable.* FROM tblMyTable;
Here is the SQL I can build with a few of the selected fields that I want in the subforms record source...
SELECT Field1, Field3, Field5 FROM tblMyTable;
How would the custom SQL be used with your code? I tried adding a SQL string to the Set rst = "SELECT Field1, Field3, Field5 FROM tblMyTable;" line in your code but it failed.
Ok, what you could do is when looping through the controls, save the field names of the visible fields into an array. Loop through the array and create your sql statement.
Or you can simply build the sql statement within that loop instead.
Code:
Dim strSQL as String
If Not ctl.ColumnHidden Then
If Len(strSQL) = 0 then
strSQL = "SELECT " & ctl.ControlSource & ", "
else
strSQL = strSQL & ctl.ControlSource & ", "
end if
End If
The record source of the sub form is simply a select SQL of the table to display everything. The code I listed in the original post above is what I use to hide the fields the user did not select in another form to display only the fields they want to see in the sub form. All that works great. The problem is trying to output the users data with only the visible fields and also any filtered data they selected using the common right-click options within the datasheet view of the sub form.
SELECT tblMyTable.* FROM tblMyTable;
I did create the below code to build the SQL of only the fields selected by the user but I could not get the built SQL string to work with your code in the Set rst = "SELECT Field1, Field3, Field5 FROM tblMyTable;" line to replace the Set rst = frm.RecordsetClone line of your code.
Code:
Dim sSql As String
Dim sFieldNamesInUse As String
Dim rs As DAO.Recordset
Dim sRecordSource As String
sSql = "SELECT tblFields.FieldName, tblFields.InUse, tblFields.SortOrder FROM tblFields WHERE (((tblFields.InUse) = -1)) ORDER BY tblFields.SortOrder;"
Set rs = CurrentDb.OpenRecordset(sSql)
Do While rs.EOF = False
sFieldNamesInUse = sFieldNamesInUse & rs![FieldName] & ", "
rs.MoveNext
Loop
rs.Close
sFieldNamesInUse = Left(sFieldNamesInUse, Len(sFieldNamesInUse) - 2)
sRecordSource = "SELECT " & sFieldNamesInUse & " FROM tblMyTable;"
Debug.Print sRecordSource
When I added the rs.OpenRecordset(sRecordSource) line to the end of my code [I commented out the re.Close line] I get a runtime error # 3421 Data type conversion error on the rs.OpenRecordset(sRecordSource) line.
How can I simply replace the Set rst = frm.RecordsetClone line in your code with my custom SQL string I am building with the code I posted above? I get the error message "Type Mismatch" and it high lights the SELECT Field1, Field2, Field3 FROM tblMyTable; part of the line...
Set rst = "SELECT Field1, Field2, Field3 FROM tblMyTable;"
I know I am late to the party, but I will add some info that does not negate any of the fine advice received by Bob and vbaInet.
A SubForm control DOES NOT have its SourceObject property set to a Form object. You can set the property to a Table or Query object as well. When that is done, MS Access creates a "virtual form" in order to display the records of the Query or Table. That form object can be passed around in memory and manipulated from code, and thus passed to Bob's code. I bring up this point because you can utilize this fact and set your SubForm controls Source Object propert to a Query Object name (ie: Query.sel_ExcelExport). Then in your code that builds the SQL statement with the InUse fields you can set the Query Objects SQL property to the SQL statement you just built.
So, leave your SubForm Control's Source Object proproperty EMPTY at design time, then immediately after you create the SQL statement that gets your records add the following code (note: AIR CODE) to modify your Query Object, SubForm control, and Column Widths ...
Code:
'Modify the SQL statement of a query object and set that Query Object as
'the source of records for the SubForm control.
CurrentDb.QueryDefs("sel_ExcelExport").SQL = sSQL
Me.nameOfSubFormControl.SourceObject = "Query.sel_ExcelExport"
'Create an object variable for the virtual form. Note that the .Form object
'of the SubForm control won't exist until you set the SourceObject.
Dim frm As Form 'You may want to scope this for the module.
Set frm = Me.nameOfSubFormControl.Form
'Autosize the column widths or set to a max width. Note that the
'ControlType returned by the control is not in the enumerated constants
'thus the On Error Resume Next.
On Error Resume Next
For Each ctl In frm.Controls
ctl.ColumnHidden = False
ctl.ColumnWidth = -2 'Autowidth
If ctl.ColumnWidth > 2 * 1440 Then ctl.ColumnWidth = 2 * 1440 '2 in. max
Next
On Error GoTo 0 'Reset the error handler
'Turn off nav buttons (Optional) Set any other form properties to modify the
'appearance here.
frm.NavigationButtons = False
Then you can call Bob's code --- UNCHANGED --- with:
Send2Excel(Me.nameOfSubFormContol.Form)
--- Or, if the frm variable from above code is scoped at the module level ---
Send2Excel(frm)
-------
Now your records are dumped and you would than have a Query Object (sel_ExcelExport) that you would have available for use elsewhere (becareful though as Form level filters would not be applied the query object --- unless you modifed the SQL statement to reflect your Form level filters, also new data coming into the db may satisfy the WHERE clause and thus not be reflected in the Excel file).
Another "pro" for useing this method is that you can keep Bob's code unchanged, and thus if, by chance, Bob updates the code you can do a direct copy/paste with out concern.
-----
Just some food for thought --- definately not circumventing the advice given!
if ColumnHidden is TRUE that means that the Column is HIDDEN. So FALSE would be the selection.
