Easier Way to Change My Controls

[Chaplain Doug]

I have a page that has 184 controls on it which are just
going to be for display (not input). At present each
control has a field data source. I need to change the
data source instead to a function call with the current
field being a parameter in the call. I don't want to have
to make changes in the properties window 184 times. Is
there a way to get at the controls like there is with
Queries and the SQL display mode or something like it?
Thanks.

 

[Graham R Seach]

Doug,

Off the top of my head, there are two ways to do this.

Firstly, if you multi-select similar controls, you can assign properties (of
which RecordSource is one) en masse. Assigning a function to the
RecordSource is as simple as:
=MyFunctionName(myargument list)

Secondly, it might be worth considering using one or more ListBoxes instead
of a million textboxes, etc. Since the controls are read-only, you can have
a single complex query to retrieve and display the form's values (or at
least significant portions of it). The RowSource can be given the name of a
special custom function that can be used to populate the list.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Dirk Goldgar]

I have a page that has 184 controls on it which are just
going to be for display (not input). At present each
control has a field data source. I need to change the
data source instead to a function call with the current
field being a parameter in the call. I don't want to have
to make changes in the properties window 184 times. Is
there a way to get at the controls like there is with
Queries and the SQL display mode or something like it?
Thanks.

184 data controls on one form! Yow! What is this form doing, if I may
ask?

You could run a subroutine something like this (WARNING -- AIR CODE):

'----- start of code -----
Sub FixControlSources(FormName As String)

Dim frm As Form
Dim ctl As Control
Dim strCtlSource As String

DoCmd.OpenForm FormName, acDesign, _
WindowMode:=acHidden

Set frm = Forms(FormName)

On Error Resume Next

For Each ctl In frm.Controls

strCtlSource = vbNullString
strCtlSource = ctl.ControlSource

If Len(strCtlSource) > 0 Then
' ctl is not unbound

If Left(strCtlSource, 1) <> "=" Then
' ctl is not calculated, so it must be bound.

' Add brackets if not already present, just in
' case.
If Left(strCtlSource, 1) <> "[" Then
strCtlSource = "[" & strCtlSource & "]"
End If

' Change the controlsource of ctl to a
' function call.
ctl.ControlSource = _
"=fncMyFunction(" & strCtlSource & ")"

End If
End If
Next ctl

Set frm = Nothing

DoCmd.Close acForm, FormName, acSaveYes

MsgBox "Finished fixing form " & FormName & "."

End Sub
'----- end of code -----

Note that you would want to modify the statement

ctl.ControlSource = _
"=fncMyFunction(" & strCtlSource & ")"

to call the function you had in mind.

Having saved the above routine in a standard module, you could call it
from the Immediate window:

FixControlSources "MyFormName"

 

[Chaplain Doug]

Dear Dirk:
I am displaying the number of lessons graded for each type
of lesson (there are 184 different lessons). I set the
control source to:

=GetTotal()

The code for GetTotal is:

Private Function GetTotal()
Dim ctlCurrentControl As Control
Dim strCurrentControlName As String
Set ctlCurrentControl = Me.ActiveControl
strCurrentControlName =
ctlCurrentControl.Properties.Item("Name")
GetTotal = CummTotal("YTD", strCurrentControlName,
[YEAR])
End Function

I am lucky since the name of the control is the same as
the name of the field I want to get the total for.
HOWEVER! It only works properly for the first control on
the page [GEN 1]. Thereafter, Me.ActiveControl remains
set to the first control's name even though all the others
have a differnet name. Hence they all get the same
total. Why?

-----Original Message-----

I have a page that has 184 controls on it which are just
going to be for display (not input). At present each
control has a field data source. I need to change the
data source instead to a function call with the current
field being a parameter in the call. I don't want to have
to make changes in the properties window 184 times. Is
there a way to get at the controls like there is with
Queries and the SQL display mode or something like it?
Thanks.

184 data controls on one form! Yow! What is this form doing, if I may
ask?

You could run a subroutine something like this (WARNING -- AIR CODE):

'----- start of code -----
Sub FixControlSources(FormName As String)

Dim frm As Form
Dim ctl As Control
Dim strCtlSource As String

DoCmd.OpenForm FormName, acDesign, _
WindowMode:=acHidden

Set frm = Forms(FormName)

On Error Resume Next

For Each ctl In frm.Controls

strCtlSource = vbNullString
strCtlSource = ctl.ControlSource

If Len(strCtlSource) > 0 Then
' ctl is not unbound

If Left(strCtlSource, 1) <> "=" Then
' ctl is not calculated, so it must be bound.

' Add brackets if not already present, just in
' case.
If Left(strCtlSource, 1) <> "[" Then
strCtlSource = "[" & strCtlSource & "]"
End If

' Change the controlsource of ctl to a
' function call.
ctl.ControlSource = _
"=fncMyFunction(" & strCtlSource & ")"

End If
End If
Next ctl

Set frm = Nothing

DoCmd.Close acForm, FormName, acSaveYes

MsgBox "Finished fixing form " & FormName & "."

End Sub
'----- end of code -----

Note that you would want to modify the statement

ctl.ControlSource = _
"=fncMyFunction(" & strCtlSource & ")"

to call the function you had in mind.

Having saved the above routine in a standard module, you could call it
from the Immediate window:

FixControlSources "MyFormName"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[John Vinson]

Dear Dirk:
I am displaying the number of lessons graded for each type
of lesson (there are 184 different lessons). I set the
control source to:

If you have 184 different lessons in 184 different table fields in a
single record... say after me, "Forgive me, Father, I have sinned by
commiting spreadsheet upon a database". This is going to be an
unworkable data structure VERY quickly!

If you have a many (students) to many (lessons) relationship, a much
better design is to have three tables: Students; Lessons; Grades. The
Grades table will have only three fields - StudentID, LessonID, Grade;
and each student will have (up to) 184 *ROWS* in this table, not 184
fields.

 

[Chaplain Doug]

Dear John:

What fields would you have in the Lessons table? How will
these three tables be related?

 

[Dirk Goldgar]

Dear Dirk:
I am displaying the number of lessons graded for each type
of lesson (there are 184 different lessons). I set the
control source to:

=GetTotal()

The code for GetTotal is:

Private Function GetTotal()
Dim ctlCurrentControl As Control
Dim strCurrentControlName As String
Set ctlCurrentControl = Me.ActiveControl
strCurrentControlName =
ctlCurrentControl.Properties.Item("Name")
GetTotal = CummTotal("YTD", strCurrentControlName,
[YEAR])
End Function

I am lucky since the name of the control is the same as
the name of the field I want to get the total for.
HOWEVER! It only works properly for the first control on
the page [GEN 1]. Thereafter, Me.ActiveControl remains
set to the first control's name even though all the others
have a differnet name. Hence they all get the same
total. Why?

I agree with John Vinson that your data design is probably faulty. I'll
let you hash that out with him, though. Leaving that aside, your
function as written won't work, because Screen.ActiveControl returns the
control that has the *focus*, and there's only one of these at a time.
The fact that you call your function from the controlsource of a
particular control doesn't mean that control has the focus.

What you'd need to do to have even a hope of getting this to work is
rewrite your function as follows:

'---- start of code -----
Private Function GetTotal(CtlName As String)
GetTotal = CummTotal("YTD", CtlName, [YEAR])
End Function

'---- end of code -----

Then the controlsource for each control would be set to pass the name of
the control to the function; for example, "=GetTotal('Lesson1')" for a
control named "Lesson1", "=GetTotal('Lesson2')" for a control named
"Lesson2", and so on. The routine I posted could be adapted you to set
those controlsources.

However, I strongly suggest you consider revising the design of your
form -- and possibly your tables -- instead.

 

[John Vinson]

Dear John:

What fields would you have in the Lessons table? How will
these three tables be related?

Well, I don't know what a "lesson" is in your context, but the
classical school enrollment database would seem to be pretty close:

Pupils
StudentID
LastName
FirstName
<other bio info>

Lessons
LessonNo
Subject <e.g. "Introductory Relational Design">
<any other information about this lesson by itself>

Grades
StudentID << link to Pupils, each pupil gets up to 184 grades
LessonNo << link to Lessons, each lesson is taught to many students
Grade << this student's grade for this lesson

The "Subject" in the Lessons table might be whatever you're now using
as the fieldname of one of your 184 fields.

 

[Chaplain Doug]

Dear John:

My "grades" also include 24 dates of unit completion:
e.g., GEN 1, GEN 2, . . .,GEN 8, GEN UNIT 1(Date of
completion). There are 24 units of 8 lessons each.
Anyhow, my current table has all info (Student and 24*9
fields). I am confused as to how all of this new table
design (non-spreadsheet) will work. Can I communicate
with you directly via email? I am at (e-mail address removed).
God bless and thanks for all the help.

 

[John Vinson]

Dear John:

My "grades" also include 24 dates of unit completion:
e.g., GEN 1, GEN 2, . . .,GEN 8, GEN UNIT 1(Date of
completion). There are 24 units of 8 lessons each.
Anyhow, my current table has all info (Student and 24*9
fields). I am confused as to how all of this new table
design (non-spreadsheet) will work. Can I communicate
with you directly via email? I am at (e-mail address removed).
God bless and thanks for all the help.

I'd be willing... but only with a consulting contract in hand, I fear.
I'm an unpaid volunteer here, like everyone else, and as such I must
reserve private EMail support to paying customers.

That said...

it sounds like you should have a 24-row table of Units, and a 192-row
table of Lessons, with (perhaps) three fields - Unit, LessonNo (1 to
8), and a Description field explaining what's covered in GEN 2 (I'm
guessing that's Genesis? Would you be willing to share your lesson
material on Mark 14-15 for the Bible study class I'm teaching? <bg>)

Your Grades table then might better be called a Completion table if
you're storing completion dates. It would have four fields:

StudentID as a link to the Student table (who completed the lesson)
Unit
LessonNo together form a link to Lessons, which lesson they did
CompDate The date that they completed this lesson

You could have a Form based on the student table, with a subform based
on the Completion table; this would show - on one line per lesson -
exactly which lessons were completed when by each student.

This gives you a much simpler form design than trying to wrestle with
216 controls!