Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  SQL statement query ran in Access but still SQL

Author  Topic  sl9799 4 Posts Posted - 2005-04-10 : 10:10:24 This is what I have so far but it still does not work: Basically I have this pop-up form that allows me to add an equip code (sSearch) to table PROJ_ME. Sometimes this equip code already exists in PROJ_ME so now I have to change my SELECT statement to UPDATE instead of INSERT. PROJ_ME is tied to MEDEQ and ME_CODE. ME_CODE is important because it contains the description of the item since these tables are properly normalized. MEDEQ has multiple equip codes that may be the same but its unique identifier is ProductID thus I have [PROJ_ME].[Alt_Code] = [MEDEQ].[ProductID]. When the following is ran, it still will not update the [PROJ_ME].[Alt_Code] field with the new ProductID. I don't have any error handling in the below code to be sure no error will come up and yet none does. I just need someone to check my SQL statement and let me know its set properly! Anything else that you need for me to expound please let me know as well. Thank you for your replies. I also just found out that I have another table PROJ_EQ that is tied also to PROJ_ME thus I can't update PROJ_ME. Private Sub cmdAdd_Click() Call Open_Conn sSelect = "SELECT * FROM [PROJ_ME] WHERE [Code] = '" & sSearch & "'" rstData.Open sSelect, cnnData, 2, 3 MsgBox rstData.EOF If rstData.EOF = False Then sSelect = "UPDATE ([ME_CODE] LEFT JOIN [PROJ_ME] ON [ME_CODE].[Code] = [PROJ_ME].[Code]) INNER JOIN [MEDEQ] ON [ME_CODE].[Code] = [MEDEQ].[ItemCode] SET [PROJ_ME].[Code] = [MEDEQ].[ItemCode], [PROJ_ME].[Alt_Code] = [MEDEQ].[ProductID], [PROJ_ME].[Item] = [ME_CODE].[Item], [PROJ_ME].[Name] = [ME_CODE].[description], [PROJ_ME].[MSCode] = [ME_CODE].[MSCode], [PROJ_ME].[Type] = [ME_CODE].[Type], [PROJ_ME].[Unitcost] = [MEDEQ].[Unitcost], [PROJ_ME].[ListPrice] = [MEDEQ].[ListPrice], [PROJ_ME].[Install] = [ME_CODE].[Install], [PROJ_ME].[Furnish] = [ME_CODE].[Furnish] WHERE [PROJ_ME].[Code] = '" & sSearch & "'" Else sSelect = "INSERT INTO PROJ_ME ( Alt_Code, Code, Item, Name, MSCode, Type, Furnish, Install, ListPrice, Unitcost ) SELECT DISTINCT MEDEQ.ProductID, MEDEQ.ItemCode, ME_CODE.Item, ME_CODE.description, ME_CODE.MSCode, ME_CODE.Type, ME_CODE.Furnish, ME_CODE.Install, MEDEQ.ListPrice, MEDEQ.Unitcost FROM (ME_CODE LEFT JOIN PROJ_ME ON ME_CODE.Code = PROJ_ME.Code) INNER JOIN MEDEQ ON ME_CODE.Code = MEDEQ.ItemCode WHERE MEDEQ.ProductID = " & iProduct End If rstData.Close cnnData.Execute sSelect DB.Execute sSelect Call Close_Conn End Sub