"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
rsaritzkyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe OvermanEngineerCommented:
try changing
Original order No
to
[Original order No]
0
rsaritzkyAuthor Commented:
>try changing
Original order No
to
[Original order No]<
I thought of that.  I tried both [Original Order No]
and
fn_Get_Invoices![Original Order No]
Got the same error

0
rsaritzkyAuthor Commented:
One more followup:

If I just execute the function fn_Get_invoices, it prompts me for a vendor number.  I type in the vendor number and the query returns the result in the desired sort sequence.  However, if I call the FORM VendorPaymentQuery, which has the record source property of "select * from fn_get_invoices..." the result is not sorted in the same way (i.e. the undesired sort sequence).

The mystery continues...

Ron
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Joe OvermanEngineerCommented:
Can you post a sanatized copy of the data that I can debug?
0
rsaritzkyAuthor Commented:
Well, just because I felt like it, instead of putting the desired "order by" field in the "Order By" property of the Form, I put the "Order By" clause into the "Record Source" property, i.e.

select * from fn_Get_Invoices(@[Forms]![Frm_List_Vendors]![Navision Source No_]) order by [Original Order No]

This seems to have done the trick - my query is displayed in the desired sort sequence, and no error message.
I guess the "order by" property does not work without this error when the backend database is SQL Server...
Ron
0
Joe OvermanEngineerCommented:
A guess is all I could give for your last statement.  At least you have what you need without errors.
0
rsaritzkyAuthor Commented:
At this point, I have what I need, and although I'd really like to understand why the error is displayed, the information available on Access applications front-ending a SQL database and the differences in how Access behaves, is very limited.  I am going to try to confirm with other sources that my discovery is correct - that the "order by" property is not to be used with functions that query tables that are on a SQL backend.  I have another Access app against the same database, and I am having issues along the same line - I can't use the regular Access buttons and icons to either (a) change the sort or (b) filter the results.  Lots of errors, including the same "Syntax error or access violation".  Obviously I'm missing some key knowledge here.  But most developers are using VB as their front-end, not Access.  I started using Access because I thought it would be easier to build some simple apps, since I haven't developed anything using VB.  Although I'm an experienced developer and know SQL fairly well, my development experience has been everything BUT VB.  Guess it's time to bite the bullet and learn!

Ron
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.