[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

"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
0
rsaritzky
Asked:
rsaritzky
  • 4
  • 3
2 Solutions
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now