Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    15

    Unanswered: Writing Access functions

    Writing Access functions
    I am an old FoxPro programmer (but have been out of programming for many years) and I have been through the Access basics that I have been able to find on line. So I can create tables, forms enter data the basics. I am learning VBA no problem there. Now I want to write some functions. I would like to write one function that is passed an ID and returns the last name of the person having that ID. I would like to write this function in two ways:

    1. Connect to a table in my DB find the ID and return the name
    2. Have the function perform a query that returns a record set from which the name is returned.

    Does any one know where I can find some straight forward simple examples demonstrating this?

    This is a training exercise for me to learn how to:
    * Connect to tables and process data
    * Create a record set and process data from the recordset
    * Use debugging techniques breakpoints, run code from the immediate window etc.

    I would really appreciate the help

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Connect to a table in my DB find the ID and return the name:
    Code:
    Public Function GetLastName(ByVal ID As Long, ByVal TableName As String) As String
    
        GetLastName = Nz(DLookup("LastName", TableName, "ID = " & ID), "")
        
    End Function
    2. Have the function perform a query that returns a record set from which the name is returned.
    Code:
    Public Function GetLastName(ByVal ID As Long, ByVal TableName As String) As String
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT LastName FROM " & TableName & " WHERE ID = " & ID
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If rst.BOF = False Then
            GetLastName = Nz(rst!LastName, "")
        End If
        rst.Close
        Set rst = Nothing
        
    End Function
    Both functions suppose that the table from which the information is extracted resides in the current database.
    Have a nice day!

  3. #3
    Join Date
    Feb 2010
    Posts
    15
    Thank you! This is just what I needed.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good examples Sinndho.
    Last edited by pkstormy; 02-06-10 at 10:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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