Adding Recordsets in new Form

[RG743]

Hi All,

I'm just starting, learning alot through forum, Thank You.

I have a MySQL database - "test" with 1 Table - "names", I am trying to make a simple app that will show a datagrid on form1 with 1 recordset, then when a command button is hit on form1, bring up form2 with a second recordset.

Form1 works fine, but when the command button is hit to bring up form2, it produces an error.

I'm trying to have 1 connection, in module1, then be able to open recordsets
in different forms. I have a Dim statement in the module and both forms for the recordsets, and setting them in each form, but I'm not sure what I'm missing.

Thank you

Module Code:

Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim username As String
Dim passwd As String
Dim serverIP As String
Dim db As String
Public Function connectMysql(username As String, passwd As String, serverIP As String, db As String, conn As ADODB.Connection)
   Set conn = New ADODB.Connection
   'Set rs = New ADODB.Recordset
   conn.CursorLocation = adUseClient
   conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & serverIP & ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";" _
   & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
   conn.Open
End Function

Form1 Code:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim username As String
Dim passwd As String
Dim serverIP As String
Dim db As String
Dim ssql As String



Private Sub Command2_Click()
    Form2.Show
    
End Sub

Private Sub Form_Load()
Call connectMysql("root", "000", "", "test", conn)
Set rs = New ADODB.Recordset
ssql = "SELECT * FROM names"
rs.Open ssql, conn

Set DataGrid1.DataSource = rs

Form2 Code:

Dim conn As ADODB.Connection
Dim rs2 As ADODB.Recordset
Dim ssql As String



Private Sub Form_Load()

Set rs2 = New ADODB.Recordset
ssql = "SELECT * FROM names"
rs2.Open ssql, conn

Set DataGrid2.DataSource = rs2

[Hack]

1. Change the Dim statements in your module to Public
2. You have your recordset variable declared in your module. You do not have to redeclare them on your form (the same with your connection object.)
3. You don't need two recordsets. One will do both for you.

[RG743]

Thanks for the help