Results 1 to 10 of 10
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Run-time error '2427': Expression has no value. Except I'm pretty sure it does...

    I have a button on a report that changes the sort order of the report details. First when you click it there is a shortcut menu with different sort options (You can order by product type, product name, or by a custom order).
    I had it working sometime before and only noticed it not working today (I don't use it very often).
    Now after selecting what to order by (same thing happens no matter what is selected) I get error 2427, saying the expression I entered has no value.
    The report is based on a query with a criteria WHERE ((([T-OrderConf].ConfNum)=GetValue()))
    When I click to debug the error, the GetValue function comes up with the line "GetValue = Reports![rptSampleOrderConf]![ConfNum]" highlighted.

    Code:
    Public Function GetValue()
    'Returns the Order Confirmation number on an open order form or order report.
        If CurrentProject.AllForms("F-OrderConf").IsLoaded = True Then
            GetValue = Forms![F-OrderConf]![ConfNum]
        ElseIf CurrentProject.AllForms("F-OrderList").IsLoaded = True Then
                GetValue = Forms![F-OrderList]![ConfNum]
        ElseIf CurrentProject.AllReports("rptSampleOrderConf").IsLoaded = True Then
                GetValue = Reports![rptSampleOrderConf]![ConfNum]
        ElseIf CurrentProject.AllReports("rptOrderConfPS1").IsLoaded = True Then
                GetValue = Reports![rptOrderConfPS1]![ConfNum]
        Else
            GetValue = InputBox("Enter Confirmation Number")
        End If
    
    
    End Function
    rptSampleOrderConf is the report I have open and I am certain that [ConfNum] does have a value.



    Also here is the code for the shortcut menu:
    Code:
    Sub SortPopupMenu(x, y)
    'Menu on Order Confirmation Report to change order of details.
        Dim SortMenu As CommandBar
        Dim SortBy As CommandBarControl
        
            Set SortMenu = CommandBars.Add("", msoBarPopup, , True)
            Set SortBy = SortMenu.Controls.Add:  SortBy.Caption = "Product Type": SortBy.OnAction = "SortByProduct"
            Set SortBy = SortMenu.Controls.Add:  SortBy.Caption = "Strain Name": SortBy.OnAction = "SortByStrain"
            Set SortBy = SortMenu.Controls.Add:  SortBy.Caption = "Order Entered": SortBy.OnAction = "SortByItem"
            Set SortBy = SortMenu.Controls.Add:  SortBy.Caption = "Custom Sort": SortBy.OnAction = "SortByCustom"
        
        SortMenu.ShowPopup x, y
            
    End Sub
    
    
    Sub SortByProduct()
        Screen.ActiveReport.OrderBy = "[ProdType]"
        Screen.ActiveReport.OrderByOn = True
    End Sub
    
    
    Sub SortByStrain()
        Screen.ActiveReport.OrderBy = "[StrainName]"
        Screen.ActiveReport.OrderByOn = True
    End Sub
    
    
    Sub SortByItem()
        Screen.ActiveReport.OrderBy = "[InfoID]"
        Screen.ActiveReport.OrderByOn = True
    End Sub
    
    
    Sub SortByCustom()
        Screen.ActiveReport.OrderBy = "[Sort]"
        Screen.ActiveReport.OrderByOn = True
    End Sub
    Not sure if this is relevant at all but earlier today I had a problem where in order to get the proper ConfNum by using GetValue() I had to reverse the calculation that ConfNum is based on. ConfNum = ConfID(autonumber)+1280 (was needed to avoid repetition with the pre-access order confirmations). So I used the criteria GetValue()-1280, which worked. But I don't want to do this on the report because the details are currently correct.

    I have no idea why it's doing this, so I'm basically just trying to throw as much information out as possible.
    Any ideas? Any additional information needed?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am certain that [ConfNum] does have a value.
    You know this how? You interjected a message box or debug.print statement to show what the value of ConfNum is? If that is true, then I have to wonder if it's because you haven't specified a return type for the function. IMHO, it's just not good practice even if it works since those that follow you are left to guess, and sometimes, so are you many months later when trying to figure out what you were thinking.

    The other thing I wonder about is that I've always thought the action for a menu had to be a function. You've set it to a sub. When coding back in version 2003, I never could get a menu selection to work with a sub.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I know that [ConfNum] does have a value because the button I'm having trouble with is on the report "rptSampleOrderConf" which states [ConfNum], and the source query criteria only allows one at a time. Plus the source query uses the same GetValue function and is working with no problems.
    I added the return type As Integer in the GetValue function, but this did not change the error message. (Public Function GetValue() As Integer)

    I don't know anything about the function vs sub for shortcut menus... All my shortcut menu code has come from someone on this forum, or from links that someone here posted for me, as I am still in early stages of learning. My right click shortcut menu is also a sub but I have a separate function that calls the shortcut menu sub. My button code to call this menu though is also a sub, since when you go to create an on click event it automatically creates a sub:
    Code:
    Private Sub Command246_Click()
            Dim x As Integer, y As Integer
    ‘Positions the menu directly underneath the button.    
        Call MoveToTopRightrpt
        
        x = LeftToUse + 315
        y = TopToUse + 77
        
        Call SortPopupMenu(x, y)
        
    End Sub
    But as a sub, I had it working for at least a while.

    Since I had this working before, and haven't changed the code in any big way (or small that I remember, but I forget a lot so don't take my word on it) I'm thinking that it likely has to do with something I changed that it works in conjunction with.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds like the cause is buried somewhere. If you want to post a zipped copy of the db I'll see if I can find it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Is there a way to attach a larger file database? Last time it took a long time to get it down to size and now since I don't know where the problem lies I don't want to get rid of something that I think may not be related but actually is.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You compacted the db then zipped it and it's still too big? What size does it end up being when you did that?
    btw - I just noticed you're using 2016. If you've used a feature or property that's not compatible with 2007, I may not be able to help.

  7. #7
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Zipped, it's 7,098 KB
    It'll take time anyway to get rid of confidential information.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You compacted first? That's important. If still too large after you take care of the information, I guess a drop box is all that's left.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I had not compacted it, but still couldn't get it down to size. So close! So here is the drop box link:
    https://www.dropbox.com/s/4my5uetgld...ABASE.zip?dl=0
    Hold shift while opening it. And it works best to open "frmOrderList" first and then click on one of the stores. The button is the "Sort By" button.

  10. #10
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    If there seems to be too much missing to find the problem, I'll do a larger drop box file

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-18-2015, 11:43 AM
  2. Replies: 2
    Last Post: 07-21-2014, 12:44 PM
  3. Replies: 11
    Last Post: 05-01-2014, 11:56 AM
  4. runtime error code 2427 expression with no value
    By snoopy in forum Programming
    Replies: 8
    Last Post: 06-10-2012, 11:07 PM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums