Should I be using a module?

[Smithsco]

I have a few select statements that 3 of my forms will use, instead of having these statements in each form should I place them in a module? Is that what they are supposed to be used for?

If so how do I pass the data between the module and the form?

Thanks for your help.

 

[AndyWatt]

If the same code is duplicated in three seperate forms, then I would say that it is a good candidate for moving the code into a module. You can do it by creating a Public Sub/Function and calling that to return the data to your form:

Public Function RecordsetDemo() As ADODB.Recordset
  RecordsetDemo.Open "SELECT Stuff FROM SomeTable"
End Function

and call from form:

Dim rstTest As ADODB.Recordset

Set rstTest = RecordsetDemo

Hope this is of some help.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[Smithsco]

Sorry it is a "case select".

 

[AndyWatt]

Hmm. That is a bit more questionable. It depends on what you're doing in the Select Case statements. Some example code would be helpful.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[CCLINT]

Public Sub OptionEx(frm As VB.Form, ID As Integer)
If StrComp(frm.Name, "Form1", vbTextCompare) = 0 Then
Select case ID
Case 0
frm.SomePublicMethod1
Case 1
frm.SomePublicMethod2
Case Else
frm.SomePublicMethod3
End Select
Else
Select case ID
Case 0
frm.SomePublicMethodA
Case 1
frm.SomePublicMethodB
Case 2
frm.SomePublicMethodC
Case Else
frm.SomePublicMethodD
End Select
End If
End Sub

And in the form, execute it like this:

Private Sub Option1_Click(Index As Integer)
Call OptionEx(Me, Index)
End Sub

or

Private Sub Option1_Click()
Call OptionEx(Me, 2)
End Sub