• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

SQL statement giving error Run-time error -2147467259 (80004005)

sSelect = "UPDATE PROJ_ME SET [Alt_Code] = MEDEQ.ProductID, [Code] = MEDEQ.ItemCode, [Item] = ME_CODE.Item, [Name] = ME_CODE.description, [MSCode] =  ME_CODE.MSCode, [Type] = ME_CODE.Type, [Furnish] = ME_CODE.Furnish, [Install] = ME_CODE.[Install], [ListPrice] = ME_CODE.[ListPrice], [UnitCost] = ME_CODE.[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 & ")"
        cnnData.Execute sSelect
        Call Close_Conn

I have the above code giving me the following error:

Run-time error -2147467259 (80004005)
Microsoft ODBC Microsoft Access Driver  You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause.  Revise the SELECT statement of the subquery to request only one field.

How do I rearrange this code so that its working?
0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 4
  • 3
2 Solutions
 
Jokra_the_BarbarianCommented:
The only thing that you have to focus on is the subquery results. Also, you have [UnitCost] (SELECT...)

I am unfamiliar with this syntax. If you are running an update query, then you should be setting a field in Proj_Me table equal to a single result of a subquery. 1 field, 1 record from the subquery. Your subquery does not fit this rule:

(
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 & ")"
0
 
harfangCommented:
The SQL seems entirely wrong to me... it has
    UPDATE PROJ_ME SET <set various fields>
which is correct and then
    (SELECT DISTINCT [etc...] WHERE MEDEQ.ProductID = <some number>)

What is the relation between the first part (an update query) and the second (a select query)?

Should it not read:

    UPDATE  (ME_CODE LEFT JOIN PROJ_ME ON ME_CODE.Code = PROJ_ME.Code)
        INNER JOIN MEDEQ ON ME_CODE.Code = MEDEQ.ItemCode
    SET
        [Alt_Code] = MEDEQ.ProductID,
        <etc...>

???

I think you should create the update query using the query wizard, hardcoded for a given ProductID. Once it works as expected, copy the entire SQL and just replace the hard coded ID like you did above.

Are you even sure you want an update query?

Hope this helps
0
 
stephenlecomptejrAuthor Commented:
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.  Now, some other scenarios do come up that need to be explained:  This pop-up form comes up when you click on a command button on another form that has a subform inside itself.  This subform is tied to a query with multiple tables all in relation to each other.  
1.) Could it be that this opened subform is preventing the data from being updated?  I tried just opening the frmAddEquip that has the below code by itself and no it does not.

2.) When I look at the PROJ_ME table it had for the field (Alt_Code) in the Lookup column a combo box with a long SELECT statement of its own.  "SELECT MEDEQ.ProductID, ME_CODE.Code, MEDEQ.ItemCode, ME_CODE.description, MEDEQ.Manuf, MEDEQ.Model_no FROM ME_CODE INNER JOIN MEDEQ ON ME_CODE.Code = MEDEQ.ItemCode ORDER BY ME_CODE.Code;"  This field was again a Long Integer and its default is set on 0.  Again this is tied to MEDEQ's autonumber.  I tried changing this lookup to a textbox and that still didn't force the equip code in the table.
 
3.) It also had Indexed (Duplicates OK).  Now this is in direct contrast to the MEDEQ.ProductID which happens to be the table's autonumber.  These two fields are tied together in the relationship - is that what is causing my problem?

4.) Anything else that you need for me to expound please let me know as well.  Thank you for your replies.

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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
stephenlecomptejrAuthor Commented:
Also I later changed the following:

when it comes to the point where I DELETE from PROJ_ME I get the error:
Run-time error -2147467259 (80004005) [Microsoft] [ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'PROJ_EQ' includes related records.
So do I need to cut the relationship then delete the record then go back and reattach the record?


Call Open_Conn
sSelect = "SELECT * FROM [PROJ_ME] WHERE [Code] = '" & sSearch & "'"
rstData.Open sSelect, cnnData, 2, 3
If rstData.EOF = False Then
rstData.Close
sSelect = "DELETE FROM PROJ_ME WHERE [Code] = '" & sSearch & "'"
cnnData.Execute sSelect
End If
Call Close_Conn
Call Open_Conn
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
cnnData.Execute sSelect
Call Close_Conn

Please someone give a reply - at least something.
0
 
Jokra_the_BarbarianCommented:
About your last post regarding delete run-time error. Looks like you are receiving the error because the primary key in Proj_me is being utilized as a foreign key in another table. A relationship is most likely present as well. You would have to delete records (related to Proj_Me) in the other table first, then delete your record out of Proj_me.
0
 
stephenlecomptejrAuthor Commented:
The only thing is that those records need to exist in PROJ_EQ.
I'm going to try to setup some code that detaches the relationship and then reconnects the relationship after and hopefully that will work.
0
 
Jokra_the_BarbarianCommented:
It's entirely up to you, but I would not advise you to attempt to override the relationship that exists. It exists for a reason, referential integrity. Just do a 2 step transaction.
0
 
stephenlecomptejrAuthor Commented:
This is what I had to set up in order for the following to work:

Call Open_Conn
        sSelect = "SELECT * FROM [PROJ_ME] WHERE [Code] = '" & sSearch & "'"
        rstData.Open sSelect, cnnData, 2, 3
        If rstData.EOF = False Then
            Set rstData2 = CreateObject("ADODB.Recordset")
            Set rstData3 = CreateObject("ADODB.Recordset")
            rstData2.Open "SELECT * FROM MEDEQ WHERE [ProductID] = " & iProduct, cnnData, 2, 3
                If rstData2.EOF = False Then
                    rstData3.Open "SELECT * FROM [ME_CODE] WHERE [Code] = '" & rstData2.Fields("ItemCode") & "'", cnnData, 2, 3
                        If rstData3.EOF = False Then
                            rstData.Fields("Alt_Code") = rstData2.Fields("ProductID")
                            rstData.Fields("Name") = rstData3.Fields("description")
                            rstData.Fields("Item") = rstData3.Fields("Item")
                            rstData.Fields("MSCode") = rstData3.Fields("MSCode")
                            rstData.Fields("Type") = rstData3.Fields("Type")
                            rstData.Fields("Install") = rstData3.Fields("Install")
                            rstData.Fields("Furnish") = rstData3.Fields("Furnish")
                            rstData.Fields("Unitcost") = rstData2.Fields("Unitcost")
                            rstData.Fields("ListPrice") = rstData2.Fields("ListPrice")
                            rstData.Update
                           
                        End If
                       
                    rstData3.Close
                    Set rstData3 = Nothing
                End If
            rstData2.Close
            Set rstData2 = Nothing
            rstData.Close
        Else
            rstData.Close
            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
            cnnData.Execute sSelect
       
        End If
        Call Close_Conn
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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