Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Exit sub from Function (2003 SP-3 )

    i have a control on a form that calls a function as a part of with an on click event. The function is listed below. I am attempting to exit the sub from within the function and return to the form, but I am not sure how to accomplish it.

    Any ideas are greatly appreciated.

    Function TestForFile(FileName As String, TblName As String)
    Dim fso As Object
    Dim lngRetval As Long
    'logExit = False

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FolderExists(FileName) = False Then
    lngRetval = MsgBox( _
    "The " & TblName & " is missing. Do you want to continue?", _
    vbYesNo + vbExclamation + vbDefaultButton1, _
    "Excel File Missing.")

    Select Case lngRetval
    Case vbYes
    Case vbNo
    ' logExit = True
    Exit Function
    End Select
    Else
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, TblName, FileName, True, ""
    End If

    End Function


    Thanks in advance for your consideration.


    Ken

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

    Re: Exit sub from Function (2003 SP-3 )

    What exactly is the problem? You execute Exit Function if the user clicks No.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exit sub from Function (2003 SP-3 )

    Hans,

    Thanks for your question. There are three options in the function.

    1. if the file exists, then import the spreadsheet and return to the On Click event
    2. If the file does not exist and the user clicks "Yes", then exit function and return to the On Click event (and do some more stuff)
    3. (My challenge) If the file does not exist and the user clicks "No", to not continue, then exit function, exit on click event and return to the form

    Does that make sense?

    Thanks again for your ideas.

    Ken

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

    Re: Exit sub from Function (2003 SP-3 )

    You'd have to pass a value back to the On Click event procedure and use an If ... Then ... statement there to exit if necessary.

    (Your code does not exit the function if the user clicks Yes)

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exit sub from Function (2003 SP-3 )

    This is probably not the best coding, but if they click "Yes", it goes to the End Select then End if statement then end function. Should I have added code to exit the function at the vbYes case?

    How do I pass a value back to the On click event procedure? I was trying to use the logExit to pass it back to the on click procedure, but with no luck. i tried setting the logExit to true in the function if the user clicked no and then used the following as the next line In the on click procedure:
    If logExit = True Then Exit Sub

    However, logExit indicates that it is Empty when it returns to the procedure. I'm sure it siomething easy, but it is beyond my comprehension right now.

    Thanks again.

    Ken

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

    Re: Exit sub from Function (2003 SP-3 )

    I see now that the vbYes case works as you intended. No need to add code for that. In fact, you can remove the line Exit Function for vbNo, since execution will skip DoCmd.Transferspreadsheet and go to the end.

    You should assign a value to the function, for example:

    Function TestForFile(FileName As String, TblName As String) As Boolean
    ' The default return value will be False
    ...
    ...
    Case vbNo
    ' Set return value to True
    TestForFile = True
    End Select
    ...

    In the On Click event procedure, use something like this:

    If TestForFile(..., ...) = True Then Exit Sub

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exit sub from Function (2003 SP-3 )

    Hans,

    thanks! I still have one unresolved issue. using the following code I get "Function call on left-hand side of assignment must return Variant or Object" on the TestForFile line:
    Case vbNo
    TestForFile(FileName, TblName) = True
    End Select

    What am I missing?

    thanks!

    Ken

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

    Re: Exit sub from Function (2003 SP-3 )

    Please read my previous reply carefully. The line should be

    TestForFile = True

    When you assign a value to a function, you just use its name, you don't specify any arguments. Within the body of the function, the function name behaves as if it is a variable.
    (That is an oversimplification, but for this purpose it'll do)

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Exit sub from Function (2003 SP-3 )

    Hans,

    I am so sorry. I did not catch on.

    That works very well, as usual and as expected. You are great!

    Ken

Posting Permissions

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