Update record1 in record2 in the same table- CHALLENGE- Error SQL, DAO parameters

[julienmy5757]

Hello,
I am trying to modify (in a continous form) two differents records when you modify one..
I can see only one in the form.
The records are coming from the same table.

Error comes from qdf.parameters( quote number)

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("QrytblRoutingMain Routing2")
qdf.Parameters("QUOTE NUMBER") = [Forms]![frmAssemblyGeneralInfo]![QUOTE NUMBER]
qdf.Parameters("COMPPN") = [Forms]![frmAssemblyGeneralInfo]![frmSubAssemblyEnterPriceNew].[Form]![COMPPN]Set rst = qdf.OpenRecordset()
        If rst.RecordCount <> 0 Then
             seqNo = rst.Fields("Seq")
             quoteNo = rst.Fields("[QUOTE NUMBER]") & "CONT1"
             cPN = rst.Fields("COMPPN")
             routingStr = rst.Fields("ROUTING")


         strUpdateQuery = "UPDATE tblRoutingMain SET tblRoutingMain.[SEQ] = '" & seqNo & "'" & _
                    "WHERE [tblRoutingMain].[QUOTE NUMBER] = '" & quoteNo & "' AND [tblRoutingMain].COMPPN = '" & cPN & "'" & "' AND [tblRoutingMain].[ROUTING] = " & routingStr

         CurrentDb.Execute strUpdateQuery, dbFailOnError
        End If
 
Exit_SEQ_AfterUpdate:
  Exit Sub

Err_SEQ_AfterUpdate:
  Call errorhandler1_MsgBox("Form: " & TypeName(Me) & ", Subroutine: SEQ_AfterUpdate()")
  Resume Exit_SEQ_AfterUpdate



End If

Thank you for your help

Someone tell me maybe to do something like that but i don't know if it s correct.

Private Sub SEQ_AfterUpdate()
  On Error GoTo Err_SEQ_AfterUpdate
 
Dim quoteNo As String, seqNo As String
Dim cPN As String
Dim routingStr As String
 
  Dim daoDB As DAO.Database
  Dim daoQDF As DAO.QueryDef
  Dim daoPRM As DAO.Parameter
  Dim daoRS As DAO.Recordset
  Dim strQryName As String
 
strQryName = "QrytblRoutingMain Routing2"
 
PARAMETERS projectid integer;
SELECT [p].[QUOTE NUMBER],
       [p].[COMPPN],
       [p].[SEQ],
       [p].[ROUTING],
FROM [tblRoutingMain] AS [p]
WHERE [p].[QUOTE NUMBER] = uiutils_ReadFormTextBox(Me.[QUOTE NUMBER],vbNullString) AND [p].[COMPPN=uiutils_ReadFormTextBox([Forms]![frmAssemblyGeneralInfo]![frmSubAssemblyEnterPriceNew].[Form]![COMPPN],vbNullString)
ORDER BY [p].[ROUTING];
 
Set daoDB = CurrentDb()
Set daoQDF = daoDB.QueryDefs(strQryName)
Set daoPRM = daoQDF.Parameters!projectid
 
 
daoPRM = 1
 
Set daoRS = daoQDF.OpenRecordset()
        If daoRS.RecordCount <> 0 Then
             seqNo = daoRS.Fields("Seq")
             quoteNo = daoRS.Fields("[QUOTE NUMBER]") & "CONT1"
             cPN = daoRS.Fields("COMPPN")
             routingStr = daoRS.Fields("ROUTING")
 
 
strUpdateQuery = "UPDATE tblRoutingMain SET tblRoutingMain.[SEQ] = '" & seqNo & "'" & _
                    "WHERE [tblRoutingMain].[QUOTE NUMBER] = '" & quoteNo & "' AND [tblRoutingMain].COMPPN = '" & cPN & "'" & "' AND [tblRoutingMain].[ROUTING] = " & routingStr
 
         CurrentDb.Execute strUpdateQuery, dbFailOnError
        End If
 
  daoQDF.Close

  'Clean up the connection to the database
  Set daoRS = Nothing
  Set daoPRM = Nothing
  Set daoQDF = Nothing
  Set daoDB = Nothing
 
Exit_btnEdit_Click:
  Exit Sub
 
Err_SEQ_AfterUpdate:
  Call errorhandler_MsgBox("Form: " & TypeName(Me) & ", Subroutine: SEQ_AfterUpdate ()")
  Resume Exit_SEQ_AfterUpdate

[Seth Schrock]

With out a better explanation of what you are trying to do and how, I can't make much sense of what your code is doing. Also, we need to know the exact error description and the error number.

Personally, you seem to be going about this the hard way, but again I'm not exactly sure what you are trying to do.

[julienmy5757]

The error is ERROR 3265 ITEM NOT FOUND IN THIS COLLECTION
Imagine that in a table you have a quote number and the same quote number with "cont1" at the end.

When you modify the first you want to modify the other.
This is what I am trying to do.

I have a subform, in continous form. the row source is for the normal quote number. Without "cont1".

So..

When I modify this number, I want to modify the number with Cont1 at the end based on the ROUTING because I have unique value of this ROUTING for a special QUOTE NUMBER and COMPPN.

[Seth Schrock]

Where do you get the new values with which you want to update the second record? So far it sounds like a simple UPDATE query would work.

[julienmy5757]

In my code I have an Update query, but I need to put in criteria for QUOTE NUMBER,COMPPN, ROUTING.

The news values are in the same table TblRoutingMain but for the QUOTE NUMBER without "cont1".

I forgot to say that I want to update all the SEQ values for QUOTE NUMBER "CONT1". based on the ROUTING.

[julienmy5757]

The item QUOTE NUMBER is not found but when I am trying to do this, It exists..

X=[Forms]![frmAssemblyGeneralInfo]![QUOTE NUMBER]
DEBUG.Print X
OPP003903

And It exists also in my query QrytblRoutingMa in Routing2

[Seth Schrock]

So do the fields match between the main record and the CONT1 record? I'm still unsure where the new values come from. Just saying that they come from the same table doesn't tell me all I need to know. Do they all come from the same table? What determines which record you want them to come from? You say "based on the ROUTING which would lead me to believe that related records have the same ROUTING, but then it sounds like this is a unique number, so I'm really confused.

[julienmy5757]

Esxcuse me I really apreciate your help I will explain again.

Table TblRoutingMain :


Quote number_____SEQ _______ ROUTING________ _COMPPN
OP_____________ ___20_________H eat treat_______X1
OPCONT1________ ___20_________H eat treat_______X1



I change the SEQ value of OP with my subform because the subform is based on OP and COMPPN thatI decide:


Quote number_____SEQ _______ ROUTING________ _COMPPN
OP ______________ 10________ Heat treat______ X1
OPCONT1________ ___20________ Heat treat_______X1



And I want that in my table:

Quote number_____SEQ _______ ROUTING ________COMPPN
OP ______________ 10________ Heat treat______ X1
OPCONT1________ ___10________ Heat treat______ X1



But I can Have something Like :

Quote number____SEQ ______ROUTING__ ____COMPPN
OP_____________ _10________Heat treat ____X1
OP_____________ _20________Pass ivate______X1
OP_____________ _30________Chem film______X1
OPCONT1________ _10 _______Heat treat_____X1
OPCONT1________ _20________Pass ivate______X1
OPCONT1________ _30________Chem film______X1


I change the second SEQ of OP:

Quote number____SEQ ______ROUTING__ ____COMPPN
OP_____________ _10________Heat treat ____X1
OP_____________ __8________Pass ivate______X1
OP_____________ _30________Chem film______X1
OPCONT1________ _10 _______Heat treat_____X1
OPCONT1________ _20________Pass ivate______X1
OPCONT1________ _30________Chem film______X1

Tanks to an auto-increment function i will change all of the SEQ for the OP Quote number :

Quote number____SEQ ______ROUTING__ ____COMPPN
OP_____________ _20________Heat treat_____X1
OP_____________ _10________Pass ivate______X1
OP_____________ _30________Chem film______X1
OPCONT1________ _10 _______Heat treat_____X1
OPCONT1________ _20________Pass ivate______X1
OPCONT1________ _30________Chem film______X1

And I want after this auto-incrementation ( not in this post ) update the SEQ from OP to OPCONT1 based on routing, quote number and comppn:

Quote number____SEQ ______ROUTING__ ____COMPPN
OP_____________ _20________Heat treat ____X1
OP_____________ _10________Pass ivate______X1
OP_____________ _30________Chem film______X1
OPCONT1________ _20_______Heat treat_____X1
OPCONT1________ _10________Pass ivate______X1
OPCONT1________ _30________Chem film______X1

[julienmy5757]

Excuse me for the format, Spaces were deleted I don't know why

[nico5038]

Basically you have a normalization error at hand that can be solved with an additional table.
The first table should hold the Routing and the SEQ fields.
A second table is needed with the [Quote number]
The COMPPN isn't clear to me, but when it's depending on the [Quote number] you'll need to add it to the second table.

This structure will eliminate the need for the update entirely.

Nic;o)

[julienmy5757]

Could you please tell me how to do that with a code ? Thank you for your help

[julienmy5757]

I can't separate this 4 informations in differents tables

[Seth Schrock]

Sorry, I've been busy and forgot to get back with you.

Nico is correct that you need to split your information into related tables. You can then use queries to view the data as if it was all in one table. However this is much simpler than doing it with the CONT record. I really can't think of any reason that you can't separate the information. Look at Database Normalization and Table Structures and Wikipedia: Database Normalization. Both do a very good job of explaining database normalization. Problems like yours is part of the reason that database normalization is used.

[julienmy5757]

Thank you but it is not what I want..
I have already a normalized database and in a table I have informations on the top.
Quote number,componen t number COMPPN, SEQ and ROUTING can't be separated in this case..
I just want to have a code to update datas like I said before, one record in another

Thank you I really appreciate your help .

[julienmy5757]

Why I can't separate this 4 informations ? Because it is the table of reference to make together the informations of routing and seq order by QUOTE NUMBER and COMPPN.
I have not the same sequence for each and I don't have a particular routing for a number of sequence. Seq is just an order of assembly and routing the description of steps