[RESOLVED] How to acces a Recordset from another form using the same connection

[Monti124]

Hi Again,
Can some one help me with this. Iam trying to access a recordset on a main form from another form. For example if I have a recordset name RSCustomer and the connection is open I want to be able to perform a search from another form.

[vandoren]

declare as GLOBAL in general session can help?

GLOBAL RSCustomer as Recordset

[Monti124]

Thank you Vendoron for your response,
I try using the Global declaration but it wont work.
the the fallowing code is how Iam declaring my
variables. but even when I remove the Withevents I still get an error.

Option Explicit
Public WithEvents CustomerConn As ADODB.Connection
Public WithEvents rscustomer As ADODB.Recordset
Dim mblAddMode As Boolean

[akhileshbc]

Thank you Vendoron for your response,
I try using the Global declaration but it wont work.
the the fallowing code is how Iam declaring my
variables. but even when I remove the Withevents I still get an error.

Option Explicit
Public WithEvents CustomerConn As ADODB.Connection
Public WithEvents rscustomer As ADODB.Recordset
Dim mblAddMode As Boolean

What error...????

[si_the_geek]

And what code are you using in the other form?

In case you aren't aware, you must use the name of the form that the variables are declared in.

but even when I remove the Withevents I still get an error.

Based on that, it sounds to me like you don't understand what WithEvents is - and are therefore almost certainly not using it. If that is the case, you should remove it.

[TysonLPrice]

Hi Again,
Can some one help me with this. Iam trying to access a recordset on a main form from another form. For example if I have a recordset name RSCustomer and the connection is open I want to be able to perform a search from another form.

One way is just to pass it to the other form Either the recordSet, Connection, or both)

Form1

Option Explicit

Private Sub Command1_Click()
    Dim sConnString As String
    
    Dim rsTemp As New ADODB.Recordset
    Dim cnTest As New ADODB.Connection
    Dim strSql As String
    
    sConnString = "Server=DevSQL\TPA1;Database=Manhattan;Driver=SQL Server;Trusted_Connection=Yes"
    With cnTest
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open
    End With
    
    strSql = "select top 1 * from Claim NoLOck"
    
    rsTemp.Open strSql, cnTest, adOpenStatic, adLockReadOnly
    
    Call Form2.DisplayForm(rsTemp)

End Sub

Form2

Option Explicit
Private rsForm2RecSet As New ADODB.Recordset
Private Sub Form_Load()

    Label1.Caption = rsForm2RecSet.Fields("pkClaim")

End Sub

Public Sub DisplayForm(rsRecSet As ADODB.Recordset)

    Set rsForm2RecSet = rsRecSet
    Me.Show vbModal
    
End Sub

Notice DisplayForm is Public in Form2.

[JayCR]

I have recently asked a similar question about this, and was advised that when you use a form, and the form closes, all the local variables / connections etc are lost. You are best to declare the connections and recordsets (globally) in a module and call them in to the form from the module.

[TysonLPrice]

declare as GLOBAL in general session can help?

GLOBAL RSCustomer as Recordset

Public is the more current usage.

http://www.vbforums.com/showthread.php?t=461099

[Monti124]

Thank you all for you response,
I Finally got the code working.
Im not sure this is the Right way but is working.
What I did was declare a Second Recordset global in a module . Then I used this variable for the recordset to search my Data Base. when I got the result I plug that into the Datasource property of the first recordset.

Set RSsearch = New ADODB.Recordset

RSsearch.CursorType = adOpenStatic
RSsearch.CursorLocation = adUseClient
RSsearch.LockType = adLockPessimistic
RSsearch.Source = "SELECT * FROM [Customer] WHERE [City]='" & CVar(txtcity.Text) & "'"
RSsearch.ActiveConnection = CustomerConn
RSsearch.Open


Set Grid2.DataSource = RSsearch
Set RsCustomer.DataSource = RSsearch

Hope this will help others with same problem.