Module to Service Multiple Forms

[Alan]

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

 

[Barry Gilbert]

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

 

[Peter R. Fletcher]

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.

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

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