none
Retrieve data from a table in a function

    Question

  • The following code is in a module (moGetHelpText).  Name is the lookup value from the calling procedure.  Field is the column name to be looked up int the table, and HelpText is the data to be inserted in to the a  label property.

    The calling statement is

          Me.lblHelp.Caption = GetHelpText("Count")

    The procedure does not produce an error, but it also does not insert the text into the label.

    Function GetHelpText(Name As String)
    
       Dim db As Database
       Dim Lrs As DAO.Recordset
       Dim LSQL As String
       Dim LGST As String
       
       'Open connection to current Access database
       Set db = CurrentDb()
       
       'Create SQL statement to retrieve value from GST table
       LSQL = "select HelpText from tblHelpText where Field = " & Name & ""
       
       
    End Function
    


    Bob

    Thursday, December 19, 2013 6:27 PM

Answers

  • you have not included the "Return" statement in the function.  First -- you need to define what you are returning -- in the function title:

    Function GetHelpText(Name As String) As String

    Next --

     LSQL = "select HelpText from tblHelpText where Field = " & Name & ""

    you would need a recordset object to retrieve information from your sql statement.  Or you could use DLookup function.  But DLookup is already a function, so it would be redundant to have a function like DLookup in a User-defined function.   The return statement for a user-defined function (In VBA) is like this:

    GetHelpText = someResult

    --it's the function name = someResult

    If someResult is text then you define the function As String.  If someResult is a number then you define your function As Integer or As Long (or As Double, As Date, ...)

    In your case I would set the Label.Caption = DLookUP("..", ".....") and forgo the user-defined function.  Simplicity works best

    Just remember this:-- a beginning programmer develops simple solutions for simple problems

    --an advanced programmer develops complex solutions for complex problems

    --A master programmer develops simple solutions for complex problems


    Rich P

    • Marked as answer by ARJAY010 Thursday, December 19, 2013 7:05 PM
    Thursday, December 19, 2013 6:46 PM

All replies

  • you have not included the "Return" statement in the function.  First -- you need to define what you are returning -- in the function title:

    Function GetHelpText(Name As String) As String

    Next --

     LSQL = "select HelpText from tblHelpText where Field = " & Name & ""

    you would need a recordset object to retrieve information from your sql statement.  Or you could use DLookup function.  But DLookup is already a function, so it would be redundant to have a function like DLookup in a User-defined function.   The return statement for a user-defined function (In VBA) is like this:

    GetHelpText = someResult

    --it's the function name = someResult

    If someResult is text then you define the function As String.  If someResult is a number then you define your function As Integer or As Long (or As Double, As Date, ...)

    In your case I would set the Label.Caption = DLookUP("..", ".....") and forgo the user-defined function.  Simplicity works best

    Just remember this:-- a beginning programmer develops simple solutions for simple problems

    --an advanced programmer develops complex solutions for complex problems

    --A master programmer develops simple solutions for complex problems


    Rich P

    • Marked as answer by ARJAY010 Thursday, December 19, 2013 7:05 PM
    Thursday, December 19, 2013 6:46 PM
  • Hi Bob,

    apart from getting the syntax right I would suggest some changes in your code:

    Function GetHelpText(strFldName As String) Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String

    'Open connection to current Access database Set db = CurrentDb()

    'Create SQL statement to retrieve value from GST table
    'Include the string parameter in apostrophes strSQL = "SELECT HelpText FROM tblHelpText WHERE Field = '" & strFldName & "'"

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    'If there is a record then take the value of the first field If Not rs.BOF Then GetHelpText = rs.Fields(0).Value End If

    i.e. you should avoid "Name" as it is a key word in Access and VBA and you should use prefixes that immediatly show the data type of variables as "str" for strings.


    cu
    Karl
    ***
    Catalog for the professional Access application: http://www.donkarl.com/en/catalog

    Thursday, December 19, 2013 6:50 PM