A stupid/basic question

[turfbult]

I want to connect to a database ONCE (Like in the formload procedure), do my sql statements in other procedures like command button clicks etc and then close the connection once I click exit ie. My connect, execute of sql statements and close are all in different procedures!!

How can I make all procedures see the connection, recordset etc I set up in form load????? Am I falling of the bus here??

I connect as follows.... but when I want to do a cnconnect.execute (ssql) in the "save" click event, I get an error that I need an open connection etc.

public sub form_load()

Dim cnConnect As Connection
Dim rsRecord As Recordset
Dim adoErr As Error

Set cnConnect = New Connection

On Error GoTo ConnectError
With cnConnect
.Provider = "microsoft.jet.OLEDB.4.0"
.ConnectionString = "user id=admin;password=; " & _
"data source=c:\settadminsystem\Sett.mdb; "
.Open
End With


Exit Sub
ConnectError:
For Each adoErr In cnConnect.Errors
MsgBox "ERROR LOGGING IN " & adoErr.Description, vbCritical, "LOGIN"
Next

[JHausmann]

change

Dim cnConnect As Connection


to

Public cnConnect As Connection