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?