I don't know whether I'm doing this right.  What I was trying to do is
have the database connection on a function so that I wouldn't have to code
another lines for every connection I have to do to the database.  
Since there is only one mdb fileI'll be using in the entire program , why not
make it function.  But is it possible?  Below is the sample code which 
obviously does not work :) 

-----------------------------------------------------------------
Function DBConn() As ADODB.connection
 Dim strProvider As String
 Dim strDbase As String
 
 strProvider = "Microsoft.Jet.OLEDB.4.0"
 strDbase = CurrentProject.Path & "\Cargo.mdb"
 
 DBConn.Open  "Provider =" & strProvider & ";" & _
              "Data Source =" & strDbase & ";" & _
              "User ID=Admin;" & _
              "Password="
              
End Function


Function AutoNo(IDNo As Integer) As Long
  Dim rsAutoNo As ADODB.Recordset
  Dim strSQL As String
  Dim MaxNo As Long
  
  strSQL = "SELECT Max(tblTRANXCargoBx.CargoID) AS MaxNo " & _
           "FROM tblTRANXCargoBx"
 
  Set rsAutoNo = New ADODB.Recordset
  rsAutoNo.Open strSQL, ConnStr, adOpenStatic, adLockReadOnly

  If IsNull(rsAutoNo.Fields("MaxNo")) Then
   MaxNo = 0
  Else
   MaxNo = rsAutoNo.Fields("MaxNo")
  End If
  
  rsAutoNo.Close
  Set rsAutoNo = Nothing
  DBConn.Close
  Set DBConn = Nothing
  
  AutoNo = MaxNo + 1
End Function

-----------------------------------------------------------------

Thanks,

Enzo
YahooID: onestepcloser2insanity