Using the same code for multiple forms 1

[icodian]

I have several forms that all have the same textbox & flexgrid on them. They all contain the exact same data and I want them to work exactly the same way on every form. They are basically fancy combo-boxes. You start typing in the textbox and the grid becomes visible and displays a list of choices. Then selecting a choice on the grid will populate the textbox with the selection and make the grid disappear.

Right now, I have the code for this in the Events and Subs in each form's code. However, this means if I want to make a change it all works, I have to make the change on every form which is a huge pain and it is easy to make mistakes and miss something.

I would like to have a common module that all of the forms use so I can change the code in one place and have it effect all of the forms. All of the text-boxes and grids have the same names on every form.

I've tried creating Public Subs in a module, but I can't figure out how to do this. I receive errors saying that the "Variable is not defined" when I reference the textbox name. I've tried passing the form name into the Sub, but can't get this to work either. My usual web searches haven't turned up anything. Can anyone point me in the right direction? Thanks!

 

[dilettante]

This is pretty much one of the main uses for UserControls.

You can almost treat them as a "Form within a Form" for applications like yours.

 

[vb5prgrmr]

This seems to work for me...(standard module)
[tt]
Option Explicit

Public Sub PassAForm(F As Form)
F.Text1.Text = "test"
End Sub
[/tt]

and called thus
[tt]
Option Explicit

Private Sub Command1_Click()
Call PassAForm(Me)
End Sub
[/tt]

As a test...(from form1)



Good Luck

 

[HughLerwill]

What happened to;

Dim MyNewForm as New MyFormTemplate
Dim MyNextNewForm as New MyFormTemplate
etc.

 

[dilettante]

If you do that sort of thing and you have many of these forms you'll end up with a huge program. Use a UserControl and you can write the code once and it will be shared.

 

[Andrzejek]

Let's say you have a text box (Text1) and a command button (Command1) on your Form and you want to have all the code in the Module. Try this:

Option Explicit

Private Sub Command1_Click()
    Call MySub(Text1)
End Sub

Option Explicit

Public Sub MySub(ByRef txtText As TextBox)
    MsgBox txtText.Text
End Sub

Would that work for you?

Have fun.

---- Andy

 

[strongm]

I'm with dilettante - this sounds like an ideal opportunity for a UserControl

 

[JoeAtWork]

What happened to;

Dim MyNewForm as New MyFormTemplate
Dim MyNextNewForm as New MyFormTemplate
etc.

This seems to me the obvious solution as well.

 

[strongm]

The strong implication in the OP and subsequent posts is that, whilst each form has a common element (the "fancy combo-boxes"), they then differ in detail, perhaps significantly. In which case the

Dim MyNewForm as New MyFormTemplate
Dim MyNextNewForm as New MyFormTemplate
etc.

solution is less than ideal (although a useful reminder of the 'proper' way of instantiating forms in a project)

 

[MajP]

You can build a custom class that traps all the events of controls. Then you can use a control anywhere and it will react to all events.

Here is an Access version, but the same in vb. It turns any list box into a filterable, sortable listbox that reacts to the form and listbox events.

Option Compare Database
Option Explicit

'Class Module Name: filterSortListBox
'Purpose: Turn any Listbox into a "Find As You Type"  listbox
'Created by: MajP

Private WithEvents mListbox As Access.ListBox
Private WithEvents mForm As Access.Form
Private mFilterString As String
Private mSortString As String
Private mRsOriginalList As DAO.Recordset
Private Sub mListBox_AfterUpdate()
  Call unFilterList
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub

Public Sub FilterList(FilterString As String)
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = FilterString
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mListbox.Recordset = rsTemp
    mListbox.Selected(0) = True
    mListbox.Value = mListbox.Column(0)
  Else
    MsgBox "No Records Found"
    Call unFilterList
  End If
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify filter string is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterList()
  On Error GoTo errLable
  Set mListbox.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mListbox = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub Initialize(theListBox As Access.ListBox)
   On Error GoTo errLabel
  If Not theListBox.RowSourceType = "Table/Query" Then
    MsgBox "This class will only work with a ListBox that uses a Table or Query as the Rowsource"
    Exit Sub
  End If
  Set mListbox = theListBox
  Set mForm = theListBox.Parent
  mForm.OnCurrent = "[Event Procedure]"
  mListbox.AfterUpdate = "[Event Procedure]"
 Set mRsOriginalList = mListbox.Recordset.Clone
 Exit Sub
errLabel:
 MsgBox Err.Number & " " & Err.Description
End Sub

Public Sub SortList(SortString As String)
  Dim rs As DAO.Recordset
  Set rs = mListbox.Recordset
  rs.Sort = SortString
  Set mListbox.Recordset = rs.OpenRecordset
  Set rs = mRsOriginalList
  rs.Sort = SortString
  Set mRsOriginalList = rs.OpenRecordset
End Sub

on the form you only have to instantiate the object. Thats it. Then you can sort or filter by passing in a string. Basically I have added additional methods and properties to a standard control.

so this is the form code

Public fsLst As New FilterSortListBox

Private Sub cmdApplyFilter_Click()
  fsLst.FilterList "ProductName ='" & Me.txtSearch & "'"
End Sub

Private Sub cmdSort_Click()
  fsLst.SortList ("ProductName Desc")
End Sub

Private Sub Form_Load()
  fsLst.Initialize Me.lstSearch
End Sub