troubleshooting Question

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

Avatar of rsaritzky
rsaritzky asked on
Microsoft AccessSQL
7 Comments2 Solutions396 ViewsLast Modified:

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],
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_]


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
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?




Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros