Use Data Set from Datasheet as Source of Report

[Guest]

Hello,

I dynamically populate a subform control, which is in Datasheet format, and
is based on user defined criteria in the main form. Also, the user may right
click to remove certain columns from view.

Is there a way to define/identify the resulting data set, both rows and
columns (I have the rows covered by the SQL statement used to populate the
subform) to export? I know how to export to Excel, I just need to grab those
columns which are visible. As the columns are dynamic, I want to do this
programmatically, based on what the user has selected.

Thanks.

 

[Guest]

Hi David

That was an interesting problem. I've found an obscure property of the
controls within a form called "ColumnHidden" which does the job. The main
problem with it is that not every control (notably the labels) has it so
you've got to use For/Next to stop errors happening:

Dim myCtrl As Control
Dim myProp As Property
Dim myColumns As String
myColumns = ""
For Each myCtrl In Me.tblLinks_Subform.Form.Controls
For Each myProp In myCtrl.Properties
If myProp.Name = "ColumnHidden" Then
If myProp.Value = False Then
If myColumns = "" Then
myColumns = myCtrl.Name
Else
myColumns = myColumns & ", " & myCtrl.Name
End If
End If
End If
Next
Next
MsgBox myColumns

 

[Guest]

Very cool Martin.

Thanks much!
--
David

Hi David

That was an interesting problem. I've found an obscure property of the
controls within a form called "ColumnHidden" which does the job. The main
problem with it is that not every control (notably the labels) has it so
you've got to use For/Next to stop errors happening:

Dim myCtrl As Control
Dim myProp As Property
Dim myColumns As String
myColumns = ""
For Each myCtrl In Me.tblLinks_Subform.Form.Controls
For Each myProp In myCtrl.Properties
If myProp.Name = "ColumnHidden" Then
If myProp.Value = False Then
If myColumns = "" Then
myColumns = myCtrl.Name
Else
myColumns = myColumns & ", " & myCtrl.Name
End If
End If
End If
Next
Next
MsgBox myColumns