Module to Service Multiple Forms

This is hopefully a simple question.

I have a Sub Routine that I was given a bit of help with previously that
updates a number of captions on any form, details of which are stored in
tblControlSQL

Public Sub UpdateFormInfo(strDataSet As String)

Dim rstData As Recordset
Dim CtrlSource As Control

strSQL = "SELECT * FROM tblControlSQL WHERE (((tblControlSQL.DATASET)='" &
strDataSet & "') AND ((tblControlSQL.Type)='Button'))"
Set rstData = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

While rstData.EOF = False
With rstData
Set CtrlSource = Forms!UpdateData.Controls(.Fields("Name"))
CtrlSource.Caption = .Fields("CountRowsValue")
If CtrlSource.Caption = 0 Then
CtrlSource.ForeColor = 32768
ElseIf CtrlSource.Caption = "N/A" Then
ctl.ForeColor = vbBlack
Else: CtrlSource.ForeColor = vbRed
End If

.MoveNext
End With
Wend
End Sub

I can now see a use for reusing this code and would like it to work on other
Forms other than 'Forms!UpdateData'... can anyone point me in the right
direction of having a Forms variable ???

I have tried changing the sub to UpdateFormInfo(strDataSet As String,
FormName as Form) however continually get errors.

Any help would be gratefully appreciated

Regards

Instead of passing in a form object, pass in the name of the form:
UpdateFormInfo(strDataSet As String, ByVal strFormName as String)

Then refer to it like this:
CurrentProject.AllForms(strFormName)

Barry

"Alan" wrote:

This is hopefully a simple question.

I have a Sub Routine that I was given a bit of help with previously that
updates a number of captions on any form, details of which are stored in
tblControlSQL

Public Sub UpdateFormInfo(strDataSet As String)

Dim rstData As Recordset
Dim CtrlSource As Control

strSQL = "SELECT * FROM tblControlSQL WHERE (((tblControlSQL.DATASET)='" &
strDataSet & "') AND ((tblControlSQL.Type)='Button'))"
Set rstData = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

While rstData.EOF = False
With rstData
Set CtrlSource = Forms!UpdateData.Controls(.Fields("Name"))
CtrlSource.Caption = .Fields("CountRowsValue")
If CtrlSource.Caption = 0 Then
CtrlSource.ForeColor = 32768
ElseIf CtrlSource.Caption = "N/A" Then
ctl.ForeColor = vbBlack
Else: CtrlSource.ForeColor = vbRed
End If

.MoveNext
End With
Wend
End Sub

I can now see a use for reusing this code and would like it to work on other
Forms other than 'Forms!UpdateData'... can anyone point me in the right
direction of having a Forms variable ???

I have tried changing the sub to UpdateFormInfo(strDataSet As String,
FormName as Form) however continually get errors.

Any help would be gratefully appreciated

Regards

Alternatively, and possibly more conveniently, pass the Form as an
object (Me in the calling routine), and refer to the name in the Sub
as a Property of the passed Form. Using your Sub declaration, the
Form's _name_ would be FormName.Name.


On Thu, 21 Sep 2006 14:13:02 -0700, Barry Gilbert
wrote:

Instead of passing in a form object, pass in the name of the form:
UpdateFormInfo(strDataSet As String, ByVal strFormName as String)

Then refer to it like this:
CurrentProject.AllForms(strFormName)

Barry

"Alan" wrote:

This is hopefully a simple question.

I have a Sub Routine that I was given a bit of help with previously that
updates a number of captions on any form, details of which are stored in
tblControlSQL

Public Sub UpdateFormInfo(strDataSet As String)

Dim rstData As Recordset
Dim CtrlSource As Control

strSQL = "SELECT * FROM tblControlSQL WHERE (((tblControlSQL.DATASET)='" &
strDataSet & "') AND ((tblControlSQL.Type)='Button'))"
Set rstData = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

While rstData.EOF = False
With rstData
Set CtrlSource = Forms!UpdateData.Controls(.Fields("Name"))
CtrlSource.Caption = .Fields("CountRowsValue")
If CtrlSource.Caption = 0 Then
CtrlSource.ForeColor = 32768
ElseIf CtrlSource.Caption = "N/A" Then
ctl.ForeColor = vbBlack
Else: CtrlSource.ForeColor = vbRed
End If

.MoveNext
End With
Wend
End Sub

I can now see a use for reusing this code and would like it to work on other
Forms other than 'Forms!UpdateData'... can anyone point me in the right
direction of having a Forms variable ???

I have tried changing the sub to UpdateFormInfo(strDataSet As String,
FormName as Form) however continually get errors.

Any help would be gratefully appreciated

Regards

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher