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... ;)