Screen.ActiveReport.OrderBy gives error 2427 and highlights different function.

[PeakH]

My report rptOrderConf has a button to bring up a shortcut menu that allows me to choose what I want the report sorted by.

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 = "[SortOrder]"
    Screen.ActiveReport.OrderByOn = True
End Sub

This used to work great, but I have changed the database a bit since I put this in place, and don't use this button very often so I don't know when it quit working.
The report is based on a query with a field ConfTableID. This field uses function GetConfID() as the criteria.

Public Function GetConfID() As Integer


    If CurrentProject.AllForms("frmOrderConf").IsLoaded = True Then
        GetConfID = Forms!frmOrderConf!ConfTableID
    ElseIf CurrentProject.AllForms("frmOrderList").IsLoaded = True Then
        GetConfID = Forms!frmOrderList!ConfTableID
    ElseIf CurrentProject.AllReports("rptOrderConf").IsLoaded = True Then
        GetConfID = Reports!rptOrderConf!ConfTableID
    ElseIf CurrentProject.AllReports("rptOrderConfSample").IsLoaded = True Then
        GetConfID = Reports!rptOrderConfSample!ConfTableID
    Else
        MsgBox "I can't tell what Order you are trying to open. Please go back and try again."
    End If
    
End Function

When I try to use any of the sort-by buttons, I get error 2427: "You entered an expression that has no value."
If I debug, a line from GetConfID() is highlighted: GetConfID = Reports!rptOrderConf!ConfTableID
I am wondering if the report/query has to reload to change the sort order, meaning nothing is technically open to get the correct criteria..? Although I get the error and no records have changed.

Any help would be really appreciated!
Thanks

[PeakH]

Apparently I posted this question earlier, but was not able to come up with an answer.
It's pretty difficult to attach my database as it's always too big and takes hours to take out all of the confidential information. If that's the only way to figure it out I can give it a go.


I tried putting a new button on the report just to test something a bit more simple with the code:

Private Sub Command155_Click()
Me.OrderBy = "[StrainName]"
Me.OrderByOn = True
End Sub

The exact same thing happens with this, it brings me back to the same issue with GetConfID = Reports!rptOrderConf!ConfTableID

Is there a way to go step by step through what access is doing so that I can determine why it's getting getting stuck here?

[PeakH]

Link to earlier posted thread: https://www.accessforums.net/showthread.php?t=66796

The link to the database is outdated.

[davegri]

GetConfID = Reports!rptOrderConf!ConfTableID

You need to see what the function is returning.
If this function is used by a query, it must be in a module. That means you can easily manually run the function from the immediate window, and show what it returns.
In the immediate window:
?GetConfID
Will print what the query returns. The ? is required.
So, set up whatever scenario you want, with whatever form or report open and do the thing in the immediate window.

[PeakH]

Okay so I first opened the report (nothing else open) and ran the function which returned the correct number.
When I tried the button and got the error, I selected 'End'. Then ran the function and got the correct number again.
If I instead select 'Debug' and run the function while 'debugging' the function returns 0. But then when I exit out of the debugger then open the VBA and run it again, still I get the correct number.
It was doing some weird stuff that of course now I can't reproduce, including turning all the report fields into #Name?. Also, whenever I press the shortcut button, the immediate window is populated with the numbers 159, and 1190 at the bottom. Not sure if that is anything.

[davegri]

So the function runs OK on its own? The problem occurs when the query invokes the function?
You can also run the query standalone. Set up your scenario with the chosen form/report open, then run the query and see what has been selected.

[PeakH]

The query runs fine as well if the report is open.
However, if the report is open and I refresh the report regardless of if the query is open or not, it gives me the error. I'm assuming that this is because it clears the records first and then reloads the query, meaning there are no records selected to get the ConfID from. Is this possibly what happens when the sort order is changed?

[davegri]

Sounds like the Chicken or Egg paradox. For the function/query to run properly, it needs the report fields present. The report fields depend on the the function/query delivering them up.

Perhaps the sort button on the report could save the appropriate field to a tempvar. You would need to change the function to look at the tempvar instead of the actual report field.

[PeakH]

I've never used tempvar before and was having trouble figuring out how to use it properly.

But I did have the idea of taking the function out of the source query criteria, and only including it in the Where condition of the buttons that open the report. That way, the query can first get what it needs from the form that opens the report, but then doesn't need the report to be populated in order to reload... Or that's the idea anyway.

I've tried playing around with it, pressing all of the buttons and it seems to work. Though I'm sure two weeks down the road I'll find out that it breaks something else. As is par for the course.

Thank you very much for your help! I really appreciate it.

[davegri]

Sounds Good. Different approach, different results. That's the way to hone in on the problem. Good luck with the rest of your project!