Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Problem with Function that is to return results of a Query - getting a compile error

    Hello,

    Thank you in advance for your help!

    I have created a function that is supposed to return the Client Name. The input to the function is the Client ID number. I am testing the function in the immediate window and I am getting the compile error "Expected variable or procedure, not module" . My code is below, what am I doing wrong?


    Public Function ClientNameText(ClientNbr As Integer) As String

    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.Connection
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = Conn

    rs.Open "select clientname from client where clientid = clientnbr ;"

    rs.Close
    Set rs = Nothing
    Set db = Nothing


    End Function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's a problem with your code, but I think the error is to do with how you are calling it.

    What are you entering in the immediate window? Do you have a module called "ClientNameText"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2010
    Posts
    67
    Thanks for your reply!

    In the immediate window I enter ? ClientNameText(3). The (3) is the clientid number.

    I do not have a module called ClientNameText.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Could you enter then this, substituting ModuleName with the name of the module this is in please?
    Code:
    ?ModuleName.ClientNameText(3)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2010
    Posts
    67
    I entered ?ModuleName.ClientNameText(3) and got runtime error '424' object required.

    The result from the query is a stringed value and the string can contain spaces. Would this have an affect on the results?


    I modified my code (see below) :

    Public Function ClientNameText(ClientNbr As Integer) As String

    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.Connection
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = Conn

    rs.Open "select clientname from client where clientid = clientnbr ;"

    If Not rs.EOF And Not rs.BOF Then
    ClientNameText = ClientName
    Else
    ClientNameText = Null
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing


    End Function

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - your code won't even execute due to an error.
    You did change ModeuleName to the name of your module right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2010
    Posts
    67
    I recreated the function, It now works. I must have done something wrong pertaining to the ModuleName when creating the first function. Thank you so much for your help. I really appreciate it.

Posting Permissions

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