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

[julienmy5757]

Ok I change the declaration of rstOP.
I let the WHERE for the moment because I don't understand how can I replace the values that I need without the WHERE clause

I have now an error ( surprise )..

Compile error- argument not optional lign 32 for ".SetWarnin gs ="

Edit : It is ok I remove the "="

[Seth Schrock]

Try removing the equals sign on both the true and false ones so that it is just DoCmd.Setwarnin gs False.

The WHERE clause comes into play with strUpdate. By having that WHERE clause for strOP, you will only find the one record (the current one) which defeats the purpose of the loop.

[julienmy5757]

It works !!!!!!!!!!!!!!! !!!!!!!!!!!!!!! !!!! Thank you a lot

[julienmy5757]

I let the WHERE clause

[Seth Schrock]

Good! You can play with the WHERE clause by creating a new query and see which criteria returns the records that you want, but that will be up to you to determine.

[julienmy5757]

I tried to modificate values in my form,
As you know I have an autoincrementat ion function to change all the values : 10,8,50 will be 10,20,30

and It copy all this number in the OPCONT1 so.. it's cool.

Now if I want to update OP when I change OPCONT1 the criteria will be :
[QUOTE NUMBER]= '" & Mid([QUOTE NUMBER],Intrev([QUOTE NUMBER],CONT1] & "'
?


Also when I am trying to change the ROUTING based on the seq :

On Error GoTo Err_ROUTING_AfterUpdate
Dim db As DAO.Database
Dim strOP As String
Dim rstOP As DAO.Recordset
Dim seqNo As Integer
Dim quoteNo As String
Dim cPN As String
Dim routingStr As String
Dim strUpdate As String

DoCmd.RunCommand acCmdSaveRecord

Set db = CurrentDb
  
strOP = "SELECT * FROM tblRoutingMain " & _
        "WHERE [tblRoutingMain].[QUOTE NUMBER] = '" & [Forms]![frmAssemblyGeneralInfo]![QUOTE NUMBER] & "' AND " & _
        "[tblRoutingMain].COMPPN='" & [Forms]![frmAssemblyGeneralInfo]![frmSubAssemblyEnterPriceNew].[Form]![COMPPN] & "'"
Set rstOP = db.OpenRecordset(strOP, dbOpenDynaset)
  
With rstOP
       .MoveFirst
    Do While Not .EOF
        seqNo = !Seq
        quoteNo = ![QUOTE NUMBER] & "CONT1"
        cPN = !COMPPN
        routingStr = !ROUTING
  
        strUpdate = "UPDATE tblRoutingMain SET ROUTING = " & routingStr & _
                    " WHERE [QUOTE NUMBER] = '" & quoteNo & "' AND " & _
                    "COMPPN = '" & cPN & "' AND SEQ = '" & seqNo & "'"
  
        DoCmd.SetWarnings False
        db.Execute strUpdate, dbFailOnError
        DoCmd.SetWarnings True
        .MoveNext
    Loop
End With




Exit_ROUTING_AfterUpdate:
  Exit Sub

Err_ROUTING_AfterUpdate:
  Call errorhandler1_MsgBox("Form: " & TypeName(Me) & ", Subroutine: ROUTING_AfterUpdate()")
  Resume Exit_ROUTING_AfterUpdate

Error number 3075 missing operator for routing criteria

[Seth Schrock]

[QUOTE NUMBER] = Mid([QUOTE NUMBER], 1, Len([QUOTE NUMBER]) - 5)

I tested this by replacing QUOTE NUMBER with OP2441CONT1 and it returned OP2441.

[julienmy5757]

Oh sorry I know why, I put '" for seq and " for routing..

[julienmy5757]

Okay thank you a lot, I will put this in all my program

[nico5038]

OK, I'll rest my case.

Just keep in mind that good design is simple design an that's the hardest to achieve.

Nic;o)

[julienmy5757]

Yes for maintenance too. But I can't use your method because of the rest of my software.. too complicated to explain

[zmbd]

A lot of this could have been solved sooner if julienmy5757 had provided the underlying structure of the tables and their relationships.

julienmy5757: If your database was normalized, you wouldn't be haveing to update your records this way - anytime you are duplicating the same information across multiple tables, you have a normalization problem. This is what Nico and Seth were trying to point out in the earlier posts. If you will post the structure of your tables we can show you; however, at this point, please do so in a new thread.

[tbl_name1]![field_name1] - PK, autonumber
[tbl_name1]![field_name2] - numeric, long, FK to 1:M [tbl_name2]
[tbl_name]![field_name3] - text(50)(requir ed, no nulls)
etc...
where PK = primary key, FK = forgien key, "1:M" = one to many.