Module connect to DB and recordset

[Cedtech31]

n my attempt to organize the code in a more moduler state I
Created a module called ConnectCloseDB that contains two funtions

OpenDB() and Close DB

Function OpenDB()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "c:\Documents and Settings\cspence\Desktop\CHCB_Projects\NewAmericans.mdb"
'  .Properties("Jet OLEDB:System database") = "\\serv1\db\NCAA.mdw"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With

End Function


Function CloseDB()

rs.Close

End Function

And in the main form I left

Private Sub cmdSumbit_Click()

OpenDB

While Not .EOF
       MsgBox "PatientID :" & .Fields("PatientID") & " and Language :" & .Fields("LanguageID")
       .MoveNext
   Wend

CloseDB
    
End Sub

I get the following error “ Complile Error: Invalid or unqualfied reference”

The debug leads me to .EOF

I thought by opening the database and accomplishing the recordset with OpenDB function that I would be able to run task to be able to add, edit and use msgbox.


What am I missing???

 

[WayneRyan]

Ced,

The connection (cn) and the recordset (rs) only have meaning
within the context of the OpenDB function. As soon as the
function exits, they have no meaning.

On your main form when you hit the line:

While Not .EOF

Two things are true.

1) You are not using a With statement, so what does the .EOF
pertain to?

2) Even if you had put rs.EOF, rs is no longer a valid object.

You could make the Recordset and Connection global to your form,
then it would have meaning as long as your form was open.

Wayne

 

[Cedtech31]

functions Global

I am new to VBA
how do I make the functions Global?

 

[WayneRyan]

CED,

You don't have to make the function Global. You want your entire form and
all of its code to see rs and cn. Declare them at the top of your form's
code page (not in the OpenDb function).

Option Compare Database
Option Explicit

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

' Your form's other functions etc. below
' They can reference cn & rs once the OpenDb function has been called

Wayne

 

[Cedtech31]

set variable

Thanks Wayne

I did as you stated but know it's complaining about the set variable. I am getting the following error


Run-time error '91'

Object variable or with block variable not set

Private Sub cmdSumbit_Click()

OpenDB

While Not rs.EOF
       MsgBox "PatientID :" & rs.Fields("PatientID") & " and Language :" & rs.Fields("LanguageID")
       rs.MoveNext
Wend
   

CloseDB
    
End Sub

I took your advise and added "rs" to refer to the recordset. But does that mean I have to make Public the set function?

of


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

 

[Cedtech31]

almost there

I moved

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

from the main form

to the OpenCloseDB module

which know look like this

Option Compare Database

Public cn As ADODB.Connection
Public rs As ADODB.Recordset


Public Sub OpenDB()


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "C:\Documents and Settings\Cedric\Desktop\Projects\NewAmericans\NewAmericans.mdb"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With

End Sub
Public Sub CloseDB()
Set rs = Nothing
rs.Close

Set cn = Nothing
cn.Close

End Sub

know my main for looks like this

Option Compare Database

Private Sub cmdSubmit_Click()

OpenDB



With rs

Do While Not .EOF
       MsgBox "PatientID :" & .Fields("PatientID") & " and Language :" & .Fields("LanguageID")
       .MoveNext
Loop

End With

CloseDB
    
End Sub

I thank everyone for their input so far. I have learned a lot. a couple of question and one more error

Why is it that when I declared

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

in the main form I got an error? but once I moved it to the module the error was solved. I thought that once I declare a global variable in any part of my app that it can be used thoughout the app.


Know for the error

"Eun-time error '91'
Object variable or with block variable not set"

the debug brings be to

Public Sub CloseDB()

Set rs = Nothing
rs.Close

Set cn = Nothing
cn.Close

End Sub

and to the following line rs.close

I thought that I had to set the recordset to nothing then close the recordset to properly exit the Database? WHat am I missing?

again, thanks for helping a novice out.

 

[WayneRyan]

C,

Glad to hear that you've got it sorted.

You should rs.Close, then set rs = Nothing.

Wayne