Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: run rime error 2427 you entered an expression that has no value

    I'm using VBA in MS Access to open an excel file from a folder. I can easily open an excel file if it has been used before. But if it hasn’t been used, then it returns error 2427. The problem is that the excel file that I want found is in the shared directory.

    Please help, the code is as follows:


    Private Sub Command02_Click()

    Dim fff
    Dim file As String
    file = "\\abcfileserver\Shared Data\debt\" debt_.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.mpan & "_template.xlsx" ' change to match the file w/Path
    Set fff = CreateObject("Scripting.FileSystemObject")
    If Not fff.FileExists(file) Then
    'MsgBox file & " was not located.", vbInformation, "File Not Found"
    debt_form.Command47.HyperlinkAddress = "\\abcfileserver\Shared Data\debt \" & debt_form.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.IDno & "_template.xlsx"

    Else
    'MsgBox file & " has been located.", vbInformation, "File Found"
    debt_form.Command47.HyperlinkAddress = "\\abcfileserver\Shared Data\debt \" & debt_form.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.IDno & "_template.xlsx"

    End If
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what line is generating the error?
    have you checked that the values on that line are 'sane' ie reasonable
    my guess would be its going to be on the if not exists line, and you are probably not pulling accross a varaible correctly from your debt_form
    so check
    debt_.cust_ref
    debt_form.IDno
    debt_form.cust_ref
    debt_form.mpan
    all have values you expect check the fiel exists.

    whether the file is in a shared directory or not shouldn't affect this. it could if the file is already opened by someone else
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What's the line that causes the error?
    2. As far as I can see, the HyperlinkAddress is the same whether the file exists or not. Is that what you want? If it is you don't need to repeat the line twice, you should place it outside the If structure:
    Code:
    If Not fff.FileExists(file) Then 
        'MsgBox file & " was not located.", vbInformation, "File Not Found"
    Else
        'MsgBox file & " has been located.", vbInformation, "File Found"
    End If
    debt_form.Command47.HyperlinkAddress = "\\abcfileserver\Shared Data\debt \" & debt_form.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.IDno & "_template.xlsx"
    3. There is a space in the given path: ...Shared Data\debt<space>\...
    4. In the given path: ... & debt_form.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.IDno &... two elements are repeated. Is this normal?
    5. What's the result of the assembled path when the code is run?
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    3

    line casuing error

    Thank you everyone for your prompt response.

    This is the line causing the error

    file = "\\abcfileserver\Shared Data\debt\" debt_.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.mpan & "_template.xlsx"

    the line is highligted in yellow.

    I have checked as suggested by Sinndho and healdem.

    The code gives me Run time error "2427". You entered an expression that has no value.

    Thanks guys

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what actually does
    file = "\\abcfileserver\Shared Data\debt\" debt_.cust_ref & "\" & debt_form.IDno & "\" & debt_form.cust_ref & "_" & debt_form.mpan & "_template.xlsx"
    translate into
    have you pout a watch or debug point on the code
    Debugging in the Visual Basic IDE (COM+)
    msaccess debugging ide - Google Search
    what are the values of
    debt_.cust_ref
    debt_form.IDno
    debt_form.cust_ref
    debt_form.mpan

    the error message you are reporting suggests that there is a variable there which is casuing the path or file name to be corrupted
    my guess would be its going to be
    either debt_.cust_ref or debt_form.IDno
    but there is no way to be certain as we dont'[ know what theses varaibels actually are as opposed to what they represent
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2011
    Posts
    3

    run time error '2427'

    Hi,

    The value of the variables are:

    debt_.cust_ref = CA-1111
    debt_form.IDno = 01235
    debt_form.cust_ref = eCA-1111
    debt_form.mpan = 11201235


    Thank you very much guys

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the value of 'file'
    you need to develop debugging skills matey
    telling us the values doesn't move resolving your problem any further forward
    as we don't have access to your file system

    you need to go through step by step and make certain that you think your code is doing is actaully what it is doing
    that involves finding where the error occurs
    understanding the satet of all relevant variables
    if you understand the debugger it will provide the information you neede to woirk what is going wrong


    you can change code on the fly, assign different values and so on .

    TechBookReport FileSystemObject Tutorial
    suggests your code is wrong


    dim fso as New FileSystemObject
    set fff = fso.opentextfile(file, 1) '1=read,2=write, 8 = append
    'then do your test to see if the file exists or put error trapping around this
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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