After updating the code, I am now getting a syntax error (image attached). Combo0 contains '16710A'.i change the code, just check if the filter field is correct.
Code:Public Function fncQuerySource() As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim sql As String, i As Integer Dim MasterID As String Dim sFilter As String Set db = CurrentDb If SysCmd(acSysCmdGetObjectState, acForm, "Pallet_Labels_Shipment_Number_Pop_Up_Form") <> 0 Then sFilter = " where [Order_Shipment_Number] = '" & [Forms]![Pallet_Labels_Shipment_Number_Pop_Up_Form]![Combo0] & "'" End If Set rs = db.OpenRecordset("select * from Shipments " & sFilter & " order by [Order_Shipment_Number];") With rs If Not (.BOF And .EOF) Then .MoveFirst End If Do Until .EOF MasterID = ![Order_Shipment_Number] If Len(sql) < 1 Then sql = "select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID Else sql = sql & " union select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID End If i = DCount("1", "Pallets", "Order_Shipment_Number = '" & MasterID & "'") If i < 3 Then sql = sql & _ " union ALL " & _ "select top " & (3 - i) & " " & MasterID & ",1000+[Pallet_ID],[Ordered_Item_ID],[Pallet_Item_Quantity] from zzTable2" End If .MoveNext Loop .Close End With Set rs = Nothing Set db = Nothing fncQuerySource = sql End Function
Is this referring to the syntax in the code because the report's record source query is working? I can't see any issues with the code though. The field names you entered for the filter look to be correct...