"Syntax Error or Access Violation" in Access 2007 calling a form that loads a query using ORDER BY

Hi:

I have a simple Access application that reads data from a SQL 2005 database.  It first displays a list of vendors, then, when a vendor is chosen, opens up a second form listing the transactions for that vendor.
The second form has a "Record Source" property in the Form of:
select * from fn_Get_Invoices(@[Forms]![Frm_List_Vendors]![Vendor No])

The function fn_Get_Invoices is a simple SELECT from a view:
SELECT      TOP 100 PERCENT VendorPaymentQuery.*
FROM          VendorPaymentQuery
WHERE      (AcctgVendorNo = @p1)

The VendorPaymentQuery view is a join of  a handful of tables, but is straightforward:

select TOP 100 Percent
xref.[Navision Source No_] as [Outside VendID],
vle.[Vendor No_] as [AcctgVendorNo],
Vendor.[Name],
vle.[External Document No_] [Vendor Invoice No],
PIH.[Order No_] as [Original Order No],
vle.[Document No_] as [Internal Doc No],
vle.[Posting Date],
vle.[Document Type],
vle.[Paid Status],
closedbyvle.[Document No_] [Chk No],
bale.[Amount] as [Chk Amt],
bale,[Chk Status],
dvlesift.[SUM$Amount] as [Doc Amt],
bale.[Posting Date] as [Check Dt]
from [Source Cross-Reference] xref
left outer join Vendor Vendor
on xref.[Navision Source No_] = Vendor.[No_]
inner join [Vendor Ledger Entry] vle
on xref.[Navision Source No_] = vle.[Vendor No_]
left outer join [Vendor Ledger Entry] closedbyvle
on vle.[Closed by Entry No_] = closedbyvle.[Entry No_]
left outer join [Bank Account Ledger Entry] bale
on bale.[Transaction No_] = closedbyvle.[Transaction No_]
left outer join [Detailed Vendor Ledg_ Entry] dvle
on vle.[Entry No_] = dvle.[Vendor Ledger Entry No_] and dvle.[Entry Type] = 1
left outer join [Detailed Vendor Ledg_ Entry$VSIFT$0] dvlesift
on dvle.[Entry No_] = dvlesift.[Entry No_]
left outer join [Purch_ Inv_ Header] PIH
on vle.[Document No_] = PIH.[No_]

PROBLEM:

The view returns records in an undesirable sort sequence (it returns records sorted by the field "closedbyvle.[Document No_]"

I have tried ALTER'ing the view adding an
ORDER BY PIH.[No_]
to the view

but when the function is called by Access, the records are not sorted by the above field.

So I tried inserting "Original Order No" in the "Order by" property of the Access Form.


But when I run the application, I get the error message:

"Syntax Error or Access Violation"

When I click the OK in the above error message box, it appears again.  I click OK, it appears a third time.  But THEN when I click OK, the called form appears with the correct desired sorting.

If I remove the "Order By" info in the property sheet, the error goes away.

I'm trying to determine if I can see any debugging info while running the form, but I can't figure out if the VB debugger can be helpful.  Can I set it up so that the debugger runs when I click the button that calls the second form?  Or does anyone know why the syntax error?

Thanks

Ron


Access-form-property.JPG