• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 59
  • Last Modified:

MS Access SQL Query Issue Not Right At All

This one is driving me INSANE!!!!! I have several different forms and they are all search forms where I can search for different things. Well out of nowhere today I am trying to create a new form just like the rest and it is not working at all. i have compacted and repaired the database but it is giving BIG problems.

In my Query SQL it will not save the SQL. Once I exit and go back in it is the same as before and it will not find the "ORDERID" I am looking for.

I have a List box and I have one table with two fields. Orders.OrderID and Orders.CustomerID


On click of the list box i have the code:
Private Sub SearchResult_Click()
On Error GoTo Errorhandler
MsgBox "OrderID is " & Me!SearchResult


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmOrdersReview"

    stLinkCriteria = "[OrderID]=" & Me![SearchResult]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

Errorhandler:
Application.Echo True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error in SearchResult_Click"
End Sub

Open in new window


The message box returns the value i expect but then Access errors out and says :

---------------------------
Error in SearchResult_Click
---------------------------
Error 3079: The specified field '[OrderID]' could refer to more than one table listed in the FROM clause of your SQL statement. in Form_frmOrderHistorySearch
---------------------------
OK  
---------------------------


There is only one single table in the query so how is this even possible. I have been here with this issue for several hours.

SQL:
SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
ORDER BY Orders.OrderID;

Open in new window



This is driving my NUTS! Like I said I have several of these forms and they are all basically the same. I don't get it.  I thank you for the help.
0
Dustin Stanley
Asked:
Dustin Stanley
  • 2
2 Solutions
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Hi Dustin
No wonder this is driving you nuts. This is a recent issue, that the SQL doesn't update properly. I have heard the following workarounds should work(either of them):
1.Close the builder without saving, then answer Yes when asked if you want to save
2.Save the SQL statement as a query, then update the property to use the saved query
3.Copy the SQL from the builder and paste it into the property sheet

The Access team is aware, and are working on releasing a fix (date unknown)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Anders. At least this makes me not feel like idiot. I kept think....What have I messed up! I can confirm
1 & 2  
works. This is what I have done to get by.

I figured out my issue with the error. In the frmNewOrders Form seeing how it was a Query based form I forgot to put the table name in front of the OrdersID.

stLinkCriteria = "[Orders.OrderID]=" & Me![SearchResult]

Thanks for the help of letting me know I am not TOO CRAZY with the SQL issue.

Now my Sub looks like so:
Private Sub SearchResult_Click()
'On Error GoTo Errorhandler
MsgBox "OrderID is " & Me!SearchResult


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmOrdersReview"

    stLinkCriteria = "[Orders.OrderID]=" & Me![SearchResult]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

Errorhandler:
Application.Echo True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error in SearchResult_Click"
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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