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

:wave: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.

declare as GLOBAL in general session can help?

GLOBAL RSCustomer as Recordset

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.

Code:

---

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

---

Quote:

---

Originally Posted by 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.

Code:

---

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

---

---

What error...???? :)

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.

Quote:

---

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.

Quote:

---

Originally Posted by Monti124

:wave: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

Code:

---

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

Code:

---

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.

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.

Quote:

---

Originally Posted by vandoren

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

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.

Code:

---

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.