Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Error #9 in function (XP/2K)

    I have the following function to get the Delivery number from the latest Delivery. It returns an error#9. Any ideas?
    tblKever is the table
    intRepBTL is the Delivery number
    dtRepBTL is the date.
    I would like the function to return the latest number plus 1. When it gets to 500 it start again.
    Thanks

    Public Function fgetBTLREP()
    Dim rst As Recordset
    Set rst = New Recordset
    Set rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockReadOnly


    rst.Open ("SELECT tblKever.dtRepBTL, tblKever.intRepBTL FROM tblKever WHERE (((tblKever.dtRepBTL)=DMax(""[dtrepbtl]"",""tblkever"")));")
    If Not rst.BOF And Not rst.EOF Then
    rst.MoveFirst
    fgetBTLREP = rst(0).Value
    Else
    fgetBTLREP = 1
    End If
    rst.Close
    Set rst = Nothing
    If fgetBTLREP > 500 Then
    fgetBTLREP = 1

    End If

    Debug.Print "hi"
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error #9 in function (XP/2K)

    When asking a question like this, it helps to be as specific as possible. In which line of the code does this error occur?

    When you get the error, you probably get a choice End / Debug / Help. Choose Debug; you will be taken to the code, with the offending line highlighted in yellow. You can inspect the value of variables by hovering the mouse over them. Does rst(0).Value display anything when you hover the mouse over it?

    You can stop execution by clicking the Reset button on the toolbar, or by choosing Run | Reset.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error #9 in function (XP/2K)

    If I run it in the immediate window by typing : "? fgetBTLREP" the cursor skips down 2 lines and does nothing. I put breakpoints on the whole code, but nothing happens. This is a public function of a form.
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error #9 in function (XP/2K)

    If nothing happens, how can you get Error # 9?

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error #9 in function (XP/2K)

    I copied the function from the from and out it in its own module, then it worked fine. There must have been a problem in trying to run even a public function of a form in the immediate window. The function is OK, just the running of it was somehow out of scope, so I could not test it.
    Thanks for the help.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Error #9 in function (XP/2K)

    If you want to test a public function in a form module in the Immediate window, you should call it like this:

    ? Form_Form1.GetLastName
    Adams

    This example opens a recordset and retrieves first record (ordered by last name) in table. Since form module is a class module you have to preface the function name with the class name for form as listed in Project Explorer (Form_NameofForm).

    Note: Did not get Error 9 (Subscript out of range) in any case so not sure what caused that.

    HTH

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error #9 in function (XP/2K)

    thanks

Posting Permissions

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