Connection module and function problem

  • Thread starter Thread starter Billy B
  • Start date Start date
B

Billy B

I am trying to create functions that would open a connection and another that
creates a recordset when called from a module. Below is the code I have so
far but when I compile the project I get an 'invalid outside procedure'
error. Any help would be appreciated. I am still learning rules for passing
variables between modules, etc. Thank you

Option Compare Database
'Global as declared in general declarations section
'of the form
Dim cnn As ADODB.Connection
Dim rsStore As ADODB.Recordset

Private Sub cmdSaveStoreInfo_Click()
OpenConnection (cnn)
OpenRecordsetStore (rsStore)
With rsPlants
.AddNew
etc....
End Sub

Public Function OpenConnection(ByVal cnn As String) As ADODB.Connection
'If the connection is open, close it
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then
cnn.Close
End If
End If
'Open the connection

Set cnn = Application.CurrentProject.Connection
End Function

Public Function OpenRecordsetStore(ByVal rsStore As String) As ADODB.Recordset
'create and open recordset for table plants
Set rsStore = New ADODB.Recordset

Dim strSQLStore As String
strSQLStore = "Select * from tblStore"

If rsStore.State = adStateOpen Then
rsStore.Close
Else
Set rs.ActiveConnection = cnn
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic
rs.Supports (adAddNew)
rs.Supports (adUpdate)
rs.Open sqlStore, cnn
End If

End Function
 
I have tried every combiniation I can think of:
Declaring cnn variable as string, object, adodb.connection, ByRef, ByVal
in both the procedure and the function, as general declarations and within
the
procedure. I can't get anything to work. Guess I need more HELP!
Thanks
 
Back
Top