I'm having a runtime error in VBA Access and cannot figure out why. Can anybody help?

Question

Error runtime error 7873 access

My Code

Private Sub Command0_Click()
DoCmd.OpenQuery "SELECT EmployeeTable.Employee_Last_Name FROM EmployeesTable WHERE EmployeeTable.Employee_Last_Name = 'Smith';"
End Sub

Private Sub Command1_Click()
DoCmd.OpenQuery "SELECT * FROM OrderTable WHERE OrderTable.Order_Amount > 100"
End Sub

Private Sub Command2_Click()
DoCmd.OpenQuery "SELECT AVG(OrderTable.Order_Amount) FROM OrderTable "
End Sub

Private Sub Command3_Click()
DoCmd.OpenQuery "SELECT OrderTable.Employee_ID, OrderTable.Total_Amount FROM OrderTable "
End Sub

Any help would be greatly appreciated.
I uploaded a picture of my two tables and the questions I have to answer... I think its a syntax issue but I am not sure.

Answer 1

I checked two different reference sources for the error code 7873 and they both show the same explanation:



7873 '|' cannot be imported, exported, or copied to another database or project file.@@@1@@@1



I don't see that character (|) anywhere in your code. Puzzling, to say the least.



Here is a link to a 100 page PDF file with all Access 2010 VBA error codes.



http://www.fmsinc.com/microsoftaccess/errors/ErrorNumber_Description2010.html

Answer 2

any reason why you are doing them all at once, instead of one at a time.......find out which ones giving you errors



and it's Employeestable and OrdersTable for one

EmployeeTable.Employee

with an s/\



if they dont work after spelling, i dunno then sorry. I've not messed with the VBA side. Do you need ;'s when using vba at the end?

EDIT::

Not that it may help you, but my movie is over so i thought i'd play around with it.. the error you're getting is because the query's don't exist you need to make the query's. You're just trying to open one



Private Sub Command0_Click()

strSQL = "SELECT [Employees Table].Emp_Last_Name FROM [Employees Table] WHERE ((([Employees Table].Emp_Last_Name)='Smith'))"

Set qdf = CurrentDb.CreateQueryDef("NeweQuery", strSQL)

DoCmd.OpenQuery qdf.Name

End Sub



Above code will create a query BUT it will only create it if one of the name doesn't exist. So it's kind of inefficient.



And are you sure they want you to create it using VBA? And not just simply the sql code....which BTW your sql for the first one is wrong... it wants the count of the people with the last name smith.Not who has a last name of smith



http://www.blueclaw-db.com/docmd_openquery_example.htm



It says there that it opens a SAVED query.......... i dunno man. good luck hope your instructor can show you the correct way.

Answer 3

Docmd.openquery