Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Generating a report with the record source as a function

    I am writing a reports front end in access 2003 whioch connects via ODBC to a MySQL db, and rather than create some rather messy code to link tables, I would like to gather the data via ADO functions so that all the front end user has to do is to configure an ODBC DSN.

    The individual functions work fine, and I can get data for example, this returns the address data really well...
    ----------------------------------------------------------------------------
    Function get_All_Addresses(intAddressID)
    ' fire off a remote query in order to gather all addresses into a single string
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, strConnectionString As String, strSQL As String

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


    strConnectionString = "DSN=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "DSN") & ";"
    strConnectionString = strConnectionString & "uid=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "Login") & ";"
    strConnectionString = strConnectionString & "password=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "Pass") & ";"

    cn.ConnectionTimeout = 120
    cn.CursorLocation = adUseServer
    cn.Open strConnectionString
    strSQL = "SELECT * FROM ADDRESS WHERE AddressID=" & intAddressID & " ORDER BY AddressNumber"

    strOutput = ""

    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

    Do While Not rs.EOF
    ' list each address as a single line
    If Not IsNull(rs.Fields("Address1")) Then strOutput = strOutput & rs.Fields("Address1")
    If Not IsNull(rs.Fields("Address2")) Then strOutput = strOutput & ", " & rs.Fields("Address2")
    If Not IsNull(rs.Fields("Address3")) Then strOutput = strOutput & ", " & rs.Fields("Address3")
    If Not IsNull(rs.Fields("Address4")) Then strOutput = strOutput & ", " & rs.Fields("Address4")
    If Not IsNull(rs.Fields("Postcode")) Then strOutput = strOutput & " " & rs.Fields("Postcode")
    strOutput = strOutput & vbCrLf

    If Not IsNull(rs.Fields("Telephone")) Then strOutput = strOutput & "Telephone: " & rs.Fields("Telephone") & " "
    If Not IsNull(rs.Fields("Fax")) Then strOutput = strOutput & "Fax: " & rs.Fields("Fax") & " "
    If Not IsNull(rs.Fields("Mobile")) Then strOutput = strOutput & "Mobile: " & rs.Fields("Mobile") & " "
    If Not IsNull(rs.Fields("eMail")) Then strOutput = strOutput & "eMail: " & rs.Fields("eMail") & " "


    strOutput = strOutput & vbCrLf
    rs.MoveNext
    Loop


    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

    get_All_Addresses = strOutput

    End Function
    -------------------------------------------------------------------------------

    but when I try and put the function in the Record Source property of the report, it fails. This is the function, I am returning the entire recordset so that the report can work on it:

    -----------------------------------------------------------------------------
    Function Alphabetical_Listing()

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, strConnectionString As String, strSQL As String

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


    strConnectionString = "DSN=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "DSN") & ";"
    strConnectionString = strConnectionString & "uid=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "Login") & ";"
    strConnectionString = strConnectionString & "password=" & GetSetting("Portsmouth_Diocese_Reports", "Configuration", "Pass") & ";"

    cn.ConnectionTimeout = 120
    cn.CursorLocation = adUseServer
    cn.Open strConnectionString
    strSQL = "SELECT * FROM PERSON ORDER BY Surname ASC WHERE DoNotDisplay=0 OR DeletedRecord=0"

    strOutput = ""

    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

    Alphabetical_Listing = rs

    End Function
    -------------------------------------------------------------------------------

    Any Ideas anyone?



    Fr. Simon Rundell SCP
    Parish of the Holy Spirit
    Southsea UK

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    you can't put a function in record source property anyway!
    but you can put a function in Query or Reprts textbox field as control source. To do this you should put your function in a module and declare it Public
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •