Using dynamic field names in a query for a subreport.

[rookie52]

Hi,

I have created a parent report which is based on a standard query. In the text field of the report I specified the control source to be "userID". However, the subreport is based on a query which is dynamically generated by VBA (it uses a loop which compares as below
table A field 1 with Table B Field 1
table A field 1 with Table B field 2 etc..

Then
Table A field 2 with Table B Field 1
Table A field 2 with Table B Field 2

My question is how do I dynamically set the control source to reflect the current query?

I am also interested to know if there is a function to calculate how many field a given table has?

Thanks in advance!

 

[KenReay]

Hi

I am also interested to know if there is a function to calculate how many field a given table has?

The Fields collection count property will give you number of fields in a table, if you add a reference to DAO library then

Dim Db as DAO.Database
Dim tdf as DAO.TableDef

Set Db = CurrentDb()
Set tdf = Db.Tabledefs("Yourtablename")

To set the Recordsource

Me.MySubFormControl.FROM.RecordSource = YourSQLStatement

? tdf.Fields.Count


Regards

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

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

UK

 

[KenReay]

OOPs typo

Me.MySubFormControl.FORM.RecordSource = YourSQLStatement

Regards

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

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

UK

 

[rookie52]

Ken,

Thanks a lot for your quick reply!

The field count works perfectly!

I am however, still confused by the recordsource solution you gave. I have put the line of code
Me.MySubFormControl.FORM.RecordSource = YourSQLStatement in the open action of my subreport, it reads

Me.role1.Form.RecordSource = str_Q1
Where role1 is the text area where the result should be displayed.


the str_Q1 query works fine, I've tested that separately, but I am getting an error.

You entered an expression that has an invalid reference to the Property Form/Report

Do you have any suggestions on how to address this?

Also, should the text box in the subreport be unbound when in design view? My other text box relies on the control source - do I need to assign something to the control source property, or is it okay to leave that blank?

Thanks!

 

[KenReay]

Hi

Just set up a small test db

Private Sub Form_Load()
Dim strSQL As String
'
strSQL = "SELECT tblChild.* FROM tblChild ORDER BY tblChild.ItemNo DESC;"
'
Me.frmChild.Form.RecordSource = strSQL

End Sub

Works OK for me

The code is in the onload event of the Master Form

frmChild is the name of a sunform CONTROL on the master foem

Regards

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

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

UK

 

[KenReay]

Hi Also tried it witch a saved Query def, so

Private Sub Form_Load()
Dim strSQL As String
'
strSQL = "SELECT tblChild.* FROM tblChild ORDER BY tblChild.ItemNo DESC;"
'
'Me.frmChild.Form.RecordSource = strSQL
Me.frmChild.Form.RecordSource = "qSQL"

End Sub

Works OK

Regards

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

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

UK

 

[rookie52]

Hi,

I think my question may not have been clear enough.

I am dealing with Reports and Subreports (not forms)- I have been able to set the recordsource using VBA with the following code in the open action of the subreport
me.recordsource = rst_roles1.name

I want to display the returned info from a query in specific text boxes on the report. The hardcoded information in my subreport appears, but not the dynamic info. This is becuase (I'm assuming) the text box controls on the report are unbound. I cannot hard code the query in the control source because the field name I want to display in the report varies. eg
select role_1 from security where userID = "securityroles"
select role_14 from securiy where userID = "securityroles"

This is how I think it should look, however, the properties of text and control source do not appear in the automatic drop down list.
Me.role1.control source/text = "security role code"

(role1 is the text box in the subreport)

My question in Summary
How do I insert the results from a query into a specific text box in a subreport.

THANKS!!!

 

[KenReay]

Hi

select role_1 from security where userID = "securityroles"
select role_14 from securiy where userID = "securityroles"

This looks like a case of repeating values in a table, not good Db design

Regards

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

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

UK