Solved Export Datasheet in a subform with hidden columns (1 Viewer)

[Philkred]

Hi Everyone in need of some assistance if possible!

I want to be able to capture only visible fields from a subforms datasheet, export to excel file and attach to email.

I can toggle visible columns on the subform's datasheet, export as excel attachment (via docmd.transferspreadsheet) to email but when you open the file it has all the fields. How do I capture the fields I've made decided to hide? I guess I need to somehow loop through whats visible and create a Query based on those fields as I take it that once this runs it goes off of the datasheets original layout

Thanks, Mike

 

[Gasman]

I'd probably build the sql as you mentioned, then use that to amend the sql for the querydef then use that query to export the data.?

 

[Philkred]

I'd probably build the sql as you mentioned, then use that to amend the sql for the querydef then use that query to export the data.?

Thats the thing I'm not really sure how to do that with a datasheet/looping through it, I'm still learning!

 

[Gasman]

This should get you started.

Dim ctl As Control
Dim strSQL as String
' The following code loops through all of the controls and sets the ColumnHidden property to False (they are all seen)

strSQL = "SELECT "
For Each ctl In Forms![Workorders]![Workorder Parts].Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        If ctl.ColumnHidden = False Then
            strSQL = strSQL & "[" & ctl.ControlSource & "], "
        End If
    End If
Next 
Debug.Print strSQL

That only shows the logic, you would be checking for ctl.ColumnHidden = False and then get the ctl.ControlSource value and build a string with that data and a ",".
Remember to remove the last ",") before adding the other clauses.
I've put a Debug in there so you can see what you produce, comment it out when all working.

I would also be stepping through it with F8 in the Debug window.
I've used { & ] in case your field names have spaces. Mine never do.

HTH

 

[Philkred]

Thank you for this, I will give it a go.

 

[Gasman]

I've just created this quickly and this is what it shows

Sub ShowSources()
Dim ctl As Control
Dim strSQL As String

For Each ctl In Forms![FrmTransactions].Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        If ctl.ColumnHidden = False Then
            strSQL = strSQL & "[" & ctl.ControlSource & "], "
        End If
    End If
Next
Debug.Print strSQL
End Sub

and debug.print shows

[=DCount("*","Transactions","[Description]= '" & [txtDescription] & "'")], [ID], [Date], [Account], [Num], [Description], [Memo], [Category], [Clr], [Amount],

You will notice that I did not bother with the initial select, I was just looking to see it worked and that I have a Dlookup in one control as I did not have a proper datasheet form to use, so just changed a normal form to a datasheet. Something to watch out for though?
The string also needs to have the last ", " removed.

HTH

 

[Philkred]

I've just created this quickly and this is what it shows

Sub ShowSources()
Dim ctl As Control
Dim strSQL As String

For Each ctl In Forms![FrmTransactions].Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        If ctl.ColumnHidden = False Then
            strSQL = strSQL & "[" & ctl.ControlSource & "], "
        End If
    End If
Next
Debug.Print strSQL
End Sub

and debug.print shows

[=DCount("*","Transactions","[Description]= '" & [txtDescription] & "'")], [ID], [Date], [Account], [Num], [Description], [Memo], [Category], [Clr], [Amount],

You will notice that I did not bother with the initial select, I was just looking to see it worked and that I have a Dlookup in one control as I did not have a proper datasheet form to use, so just changed a normal form to a datasheet. Something to watch out for though?
The string also needs to have the last ", " removed.

HTH

Also quick question how would I lay this out if I have form > subform > subform/datasheet

 

[Gasman]

Look here http://access.mvps.org/access/forms/frm0031.htm

I'd have to experiment, but I believe it would be along the lines of MainForm-SubformControl-Form-SubformControl - Form then Control ?

 

[Philkred]

Look here http://access.mvps.org/access/forms/frm0031.htm

I'd have to experiment, but I believe it would be along the lines of MainForm-SubformControl-Form-SubformControl - Form then Control ?

Yes I just found this thanks I shouldn't of asked and just looked better, however I dont this this will work as I'm using toggle buttons to hide each field ill upload an image of what the screen looks like.

 

[MajP]

See my answer. Sort, hide, export

Creating custom views (column orders) in subforms with datasheet view

I have a subform in datasheet view whose column order I would like to be be able to customize, perhaps with a button on the main form. Since there are different usages of the data based on the task users may be engaged in, it would be very helpful for them to have a way to order the columns...

www.access-programmers.co.uk

 

[Philkred]

See my answer. Sort, hide, export

Creating custom views (column orders) in subforms with datasheet view

I have a subform in datasheet view whose column order I would like to be be able to customize, perhaps with a button on the main form. Since there are different usages of the data based on the task users may be engaged in, it would be very helpful for them to have a way to order the columns...

www.access-programmers.co.uk

Thank you MajP, I used what you linked and it worked perfectly

 

[MajP]

Good to here. You may want to read the article cited in the thread. There are lots of hidden datasheet properties that can allow you to better control a datasheet.