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]![
The function fn_Get_Invoices is a simple SELECT from a view:
SELECT TOP 100 PERCENT 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],
vle.[External Document No_] [Vendor Invoice No],
PIH.[Order No_] as [Original Order No],
vle.[Document No_] as [Internal Doc No],
closedbyvle.[Document No_] [Chk No],
bale.[Amount] as [Chk Amt],
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_]
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?