controls on a report 1

[seca650]

I want to gather a listing of all the controls on a specific report on the detail section. Is it possible to do, as well as gathering the data source properties of these controls.

The end product is to create a table comprised of these control data sources.

 

[KenReay]

Hi

Just interate thru the controls collection

something like: (not tested)

Dim ctl as Control
Dim strSQL as String

For each ctl in Me.Controls
strSQL = "INSERT INTO tblControlNames (CtlName) VALUES ('" & ctl.name & "');"
docmd.runsql strSQL
Next

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[seca650]

KenReay,

Thanks for the input, this is giving me exactly what I was looking for. However, I thought I would be able to transpose the table, like with excel. Do you know how to take the records produced by this and turn them into field headings.

Thanks

 

[KenReay]

Hi

I am not sure I understand what you mean, but I think you are talking about a Cross Tab query, try looking at Cross tab query in help and in query designer

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[seca650]

Ken
Thanks for the input, I have found my solution. I am creating a tabledef with all the textbox.controlsource (see below)



strSQL = "Create table " & tbltemp & " ( TAG_NO TEXT "
For Each ctl In rpt.Detail.Controls
If ctl.ControlType = acTextBox Then
If ctl.ControlSource <> "tag_no" Then
'' Debug.Print ">>>"; ctl.Name
'' Debug.Print ">>>"; ctl.ControlSource
'' strSQL = "INSERT INTO tblcontrolnames ([ctlsource]) Values (" & Chr(34) & ctl.ControlSource & Chr(34) & ");"
'' DoCmd.RunSQL strSQL
If passno = 1 Then
strSQL = strSQL & ctl.ControlSource & " TEXT"
Else
strSQL = strSQL & ", " & ctl.ControlSource & " TEXT"
End If
passno = passno + 1
End If
End If
Next
strSQL = strSQL & ")"
DoCmd.RunSQL strSQL