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?
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.
Our community of experts have been thoroughly vetted for their expertise and industry experience.