We help IT Professionals succeed at work.
Get Started

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

rsaritzky
rsaritzky asked
on
395 Views
Last Modified: 2021-04-21
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
Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE