I'm trying to make a class to search trough a recordset

[ajay]

Hi
I'm trying to make a class wich can search true a SQL7 recordset.
I have to make about 25 forms and each of then has been linked with another table.
What i want is a class wich i can call in all the form.
Any idea's

Thanks
Ajay

[Mongo]

Get with your DBA and create a stored proc like:

create proc TwentyFiveBottlesOfBeerOnTheWall As
  SELECT fld1, fld2, fld3 FROM TableOfBeer25
     WHERE UTakeOne = "Down" AND UPassIt = "Around"
  SELECT fldw, fldx, fldy, fldz FROM TableOfBeer24
    WHERE UTake = "OneDown" AND UPass = "ItAround"
     --  *Smirk*  Get the idea? Use different/applicable SARGs
  SELECT fld1, fld2, fld3 FROM TableOfBeer23
  .
  .  -- ad nausium...
  .
  SELECT fld1, fld2, fld3 FROM TableOfBeer02
  SELECT fld1, fld2, fld3 FROM TableOfBeer01
return

Then in a VB class mod

Option Explicit
Option Base 0

Public RS As Collection

Private Sub Class_Initialize()
  Set RS = New Collection
End Sub

Private Sub Class_Terminate()
  Do While RS.Count
    RS.Remove 1
  Loop
  Set RS = Nothing
End Sub

Public Function CallProcForBeer(ByVal strConn As String) As Boolean
' Call Your SQLServer7 Stored Procedure"
Dim pbx As PropertyBag
Dim mconCN As ADODB.Connection
Dim mcmdCM As ADODB.Command
Dim rsX As ADODB.Recordset
Dim rsA As ADODB.Recordset
Dim lngI As Long
  On Error GoTo Error_Handler
  Set mconCN = New ADODB.Connection
  With mconCN
    .Provider = "MSDASQL"
    .CursorLocation = adUseServer
    .ConnectionString = strConn
    .Open
  End With
  Set mcmdCM = New ADODB.Command
  With mcmdCM
    Set .ActiveConnection = mconCN
    .CommandType = adCmdStoredProc
    .CommandText = "TwentyFiveBottlesOfBeerOnTheWall"
  End With
  Set rsA = New ADODB.Recordset
  With rsA
    .CursorLocation = adUseClient
    .Open mcmdCM, , adOpenForwardOnly, adLockReadOnly
  End With
  Do While (Not rsA Is Nothing)
    '  add your 25 resultsets to the class collection
    If rsA.State = adStateClosed Then Exit Do
    Set rsX = New ADODB.Recordset
    rsX.CursorLocation = adUseClientBatch
    Set pbx = New PropertyBag
    With pbx
      .WriteProperty "rsA", rsA, rsX
      Set rsX = .ReadProperty("rsA", rsX)
    End With
    Set pbx = Nothing
    RS.Add rsX  
    Set rsX = Nothing
    Set rsA = rsA.NextRecordset
  Loop
  On Error GoTo Error_Handler
  If rsA.State = adStateOpen Then rsA.Close
  Set rsA = Nothing
  CallProcForBeer = True
  Set mconCN = Nothing
  Set mcmdCM = Nothing
  Exit Function
Error_Handler:
  Set pbx = Nothing
  Set rsA = Nothing
  Set rsX = Nothing
  Set mconCN = Nothing
  Set mcmdCM = Nothing
  CallProcForBeer = False
End Function

There's some error checking you need to do, if you are not certain
each of your queries will return at least one record, but this will
get you seriously started...