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.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
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
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.