Setting Control Source in VBA code

[Guest_imported]

Hello everyone,

I have a report in which I have set the "On Open" property like so:


Private Sub Report_Open(Cancel As Integer)
Me.one.ControlSource = 1
End Sub


This lets me dynamically set the control source. In this case, it will set the textbox called "one" to read from the field called "1"

This is all well and good, but I want to use the value of another field to set the control source by, and I want to do it "on the fly". For example, I have a textbox called "howmany". Let's say it's value was 2, I want to be able to use this VBA:


Private Sub Report_Open(Cancel As Integer)
Me.one.ControlSource = me.howmany
End Sub


...and I want that to have the effect of setting the ControlSource of "one" as "2" (thus, reading from a field called 2)

The problem I am having is that, when using the OnOpen property, MS Access tells me that Me.howmany doesn't have a value yet. It only seems to have a value at the OnPage stage.... but by which time, it won't let me set the ControlSource anymore!

Any ideas gratefuly received!

Joseph

 

[Guest_imported]

I've also thought about setting the Control Source itself as something that would read from the "Howmany" field, but rather than have the value of the "howmany" field showing, have the value of the "howmany" field to actually BE the control source.

Can't seem to figure out a way to do this either though.

 

[WP]

Joseph,

Why are you trying to do this ?

Take one step back and look at the RecordSource of the report. If you need two fields the same then do in in the RecordSource SQL .


WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

Check out my website ![/sig]

 

[PaulF]

depending on the number of options &quot;HowMany&quot; can have you might consider using an IIf statement for the control source

= IIf([HowMany]=1,[1],IIf([HowMany]=2,[2],[3]))

or use a Function which will check the value of [HowMany] and set the ControlSource accordingly

Function HowManyIsIt()
Select Case [HowMany]
Case 1
HowManyIsIt = [1]
Case 2
HowManyIsIt = [2]
Case 3
HowManyIsIt = [3]
End Select
End Function


Then for the ControlSource you'd use: = HowManyIsIt()


PaulF [sig][/sig]

 

[Guest_imported]

Thanks Paul and Bill for your responses, but unfortunatly &quot;Howmany&quot; is going to get higher and higher as times goes on, so an IIF statement or the Select Case Function won't be feisable.

The database is for recording exam results (just a percentage) for lots of people. However, the exams are conducted on an ah-hoc basis so each person will have a different amount of exams to their name. The crosstab query displays the info like so:

Name 1 2 3 4 5 6 7 .. .. .. ..

John 50% 70% 90%
Bob 5% 90%
Stephen 70%

What the report needs to do is show the last 10 scores. But the higher the column heading number, the newer the exam... so, the above would have to be displayed like so:

John 90% 70% 50%
Bob 90% 5%
Stephen 70%

...in a case where someone had, say, 15 exams to their name: the records would be displayed as 15,14,13,12,11,10,9,8,7,6 with 5,4,3,2,1 missed off.

This is the reason I have the &quot;Howmany&quot; record counter. I want to set the ControlSource in the report so that field one is points to a field in the corsstab query who's name is identical to &quot;howmany&quot;'s value... then field two in the report would point to a field in the crosstab query who's name is &quot;howmany&quot; minus 1..... and so on. There would be ten fields on the report all together.

Changing the ControlSource on the report is no problem, but MS Access will only let me to it at the &quot;OnOpen&quot; stage, at which point, it says that &quot;Howmany&quot; is null.

Once again, all comments gratefuly received!

Joseph

 

[PaulF]

Joseph

Here is a solution, whether you find it suitable for your situation is up to you.

If you know IN ADVANCE, how many Fields are going to be available for Test Results then you can use this example.

It assumes that you have a table with student name, a HowMany field (numeric), and then fields for each exam result (numeric), starting with 1 and continuing to whatever.

For the report, you'd create 11 unbound textboxes for the test results (txt1 thru txt10 and txtAvg). Then you'd create a bound textbox for the Student Name (txtName) and the HowMany Field (txtHowMany). Next you'd create a bound textbox for each field that contains a test result (txtFld1 thru txtFld?), these textboxes have their visible property set to False, and would be stacked on top of one another (to minimize space). Then you'd add this code to the Detail's On Format Event. The result would be the last 10 exams (if ten were taken) and the average of the exams taken.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer, intHowMany As Integer
For i = 0 To 9
intHowMany = HowMany - i
If intHowMany > 0 Then
Me(&quot;txt&quot; & (i + 1)) = Me(&quot;txtFld&quot; & (intHowMany))
Else
Me(&quot;txt&quot; & (i + 1)) = vbNullString
End If
Next
i = 1
intHowMany = 0
For i = 1 To 10
If Me(&quot;txt&quot; & (i)) <> &quot;&quot; Then
intHowMany = intHowMany + Me(&quot;txt&quot; & (i))
End If
Next i
If txtHowMany > 10 Then
txtAvg = intHowMany / 10
Else
txtAvg = intHowMany / CInt(txtHowMany)
End If
End Sub


HTH

PaulF [sig][/sig]

 

[MichaelRed]

Use a layered query approach. Do a query to count each student's # of tests, and join this to your crosstab query an Student's name. This is your &quot;howmany&quot; field. Now that it is in the 'recordsource', you can set up your report to use the field value to determine the layout.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Guest_imported]

Hi Paul,

Thanks again for replying!

Your example would work, except for the fact that I don't know IN ADVANCE how many fields are going to be available.

All I know is that the name of the first field will be a number (it could be 10, it could be 27, it could be 280). If the first field was 27, the next nine would be 26,25,24,23,22,21,20,19,18. That's why I need to set the fields as and when the report is run, because the number of fields will change on a regular basis, and each time, the range of 10 that I need will shift up one.

Perhaps I need to re-think the way that I'm capturing the data in the first place.. but if you can think of anything else, I'd be glad to hear about it!

Thanks again Paul.

Joseph

 

[Guest_imported]

Hi Michael,

I already have the &quot;Howmany&quot; value as part of the RecordSource, but I can't use it in the report for the following reasons:

OnOpen:

Access says the field is Null. I presume the records haven't been loaded into the report at this stage?

OnActivate/OnPage:

Access won't let me change the ControlSource property as the data is already loaded.

 

[PaulF]

Joseph
What is the layout of a record

Student Name
HowMany
1
2
3
4
etc....

if this is the case, then you must know in advance how many fields are going to be there, unless you have an automated procedure to add a new field for additional test results when one is needed. If you manually add a new field, then you can manually add a new textbox to the report to account for the new field. If this is not the layout of the record, would you mind providing us with a sample.

Thanks

PaulF
[sig][/sig]

 

[MichaelRed]

If the [howmany] is known, then use a DLookup as the value for an UNBOUND text box?
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Guest_imported]

Thanks again Paul and Michael for your responses.

Paul:

The layout of the record is as follows:

Name Howmany 1 2 3 4 5 6 7..... etc etc

A new field is added by the crosstab query that produces this table as and when needed.

Michael:

DLookup is a good idea, but it will only look at the &quot;Howmany&quot; in the first record. Even though I have the relationships correctly defined, it takes the value of &quot;howmany&quot; from the first record and applies that to all records in the report.

 

[PaulF]

well, we can still do this, but my solution requires that you build a report in advance. It would be similar to the one discussed before, except you would have to build a large amount of textboxes named txtFld to accommodate the possible #s of exams. I'll leave the exact # up to you, but 200 seems like it would be more than enough. For each of these textboxes provide the number as its controlsource, even though it may not exist at this time (i.e. txtFld1 would have 1 as it's controlsource, and txtFld200 would have 200 as it's controlsource. Then add this code. The Report_Open code will loop through the fields in the crosstab query used as the report's recordsource. It will then set the controlsource for the textboxes, and if the field doesn't exist in the query then the textbox controlsource will be set to null, to prevent erroring out. The second part of the code (Detail), is untested, you may have to work on it, but it should backtrack thru the textboxes and reverse the order for the first 10 exams it finds for that record. Good Luck.... PaulF

Option Compare Database
Option Explicit
Dim intFieldCount as integer
Dim X As Integer

Private Sub Report_Open(Cancel As Integer)
Dim db As Database, rst As Recordset, qdf As QueryDef, fld As Field
Dim strFldName() As String
Dim theName As String
theName = Me.RecordSource
X = 0
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.name = theName Then
intFieldCount = qdf.Fields.Count
ReDim strFldName(1 To intFieldCount) As String
X = 0
For Each fld In qdf.Fields
X = X + 1
strFldName(X) = fld.name
Next
End If
Next qdf
Dim strMsg As String, i As Integer
Dim ctl As Control, blnHaveField As Boolean
For Each ctl In Me.Controls
blnHaveField = False
If TypeOf ctl Is TextBox Then
For i = 1 To X
If ctl.ControlSource = strFldName(i) Then
blnHaveField = True
End If
Next i
If blnHaveField = False Then ctl.ControlSource = vbNullString
End If
Next ctl
Exit Sub
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intField as integer
IntField = 0
X = 0
For X = (IntFieldCount - 2) to 1 ' Assuming only other fields are Name & HowMany
If me(&quot;txtFld&quot; & (x)) <> &quot;&quot; and me(&quot;txtFld&quot; & (x))<>&quot; &quot; then
If intField < 10 then
IntField = intField + 1
Me(&quot;txt&quot; & (intField)) = me(&quot;txtFld&quot; & (x))
End if
End If
Next X
End Sub


[sig][/sig]

 

[Guest_imported]

Thank you Paul! Sorry for my delay in replying. I have been quite busy here... but I have now managed to get the database working using the help you provided.

Thanks again!!!!