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

[julienmy5757]

It works .I put a space between seq and Where. First step.
I also changed the reference Me.X without Me. because I am using the row source of my subform
Now When I change a value in my txt box based on the Quote number OP, it changes this value with the Quote number CONT1 too.
But it changes only the SEQ that I have in my txtbox.
Because of an auto-increment function, i would like to change all the seq .. how can I do that with the code, for example with a loop ?

[nico5038]

@julienmy5757: How does your application know to have to link OP to OPCONT1 ?

[julienmy5757]

Nico, in the program you can create OPCONT1 with a command button. it is just a copy of OP, for example you have : OP64687, the cont1 will be OP64687CONT1
So to link this two I just need to use &"CONT1"

[julienmy5757]

So now the question is : How can I update all the Seq and not only the seq modificated ?

[Seth Schrock]

You just need to modify the WHERE clause of the UPDATE query in order to include the records that you want updated. What I would do is to create a regular SELECT query to test your WHERE clause to make sure that it produces the results that you want as the records that the SELECT query finds will be the same records that will get updated once you move the WHERE clause to the UPDATE query.

[julienmy5757]

I think that the problem is the seq at the beginning and the routing at the end.. If I create a table and I said move first , do while not EOF, update, move next .. Do you think that it could works ?

[julienmy5757]

Or maybe just the seq at the beginning

[Seth Schrock]

I'm not sure how you are choosing which records to update so I'm not sure what method would work.

[julienmy5757]

If I create a query like in my first code ?

[Seth Schrock]

Which one? I don't understand what the purpose of the two are or what the difference is.

[julienmy5757]

The first. The second is a purpose of a friend

[julienmy5757]

I mean that I could create a table, a query I don't know and after update the data from this table in the quote number with cont1
By an other way maybe

[Seth Schrock]

So you are wanting to go through every regular OP record and update its CONT1 record? If correct, a loop through the regular OP records would be needed. Something like

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

Set db = CurrentDb

strOP = "SELECT * FROM tblRoutingMain " & _
		"WHERE ..."  'Add your WHERE clause here
		
Set rstOP = db.OpenRecordset(strOP, dbOpenDynaset)

With rstOP
	Do While Not .EOF
		seqNo = !Seq
		quoteNo = ![QUOTE NUMBER] & "CONT1"
		cPN = !COMPPN
		routingStr = !ROUTING
		
		strUpdate = "UPDATE tblRoutingMain SET SEQ = " & seqNo & _
					" WHERE [Quote Number] = '" & quoteNo & "' AND " & _
					"COMPPN = '" & cPN & "' AND Routing = '" & routingStr & "'"
					
		DoCmd.SetWarnings = False
		db.Execute strUpdate, dbFailOnError
		DoCmd.SetWarnings True	'This could be moved to Exit_SEQ_AfterUpdate so
								'that you are turning this on and off all the time
		
		.MoveNext
	Loop
End With

[julienmy5757]

Yes this is what I want to do !! Thank you !
What is this notation ? --> !seq

[Seth Schrock]

The With statement (line 17) makes it so that you don't have to type that part of the command. So anything that starts with either a period or a bang (the exclamation point) gets interpreted as rstOP followed by the command. So the !Seq is the field Seq in rstOP. The same for !COMPPN, !ROUTING, and ![QUOTE NUMBER].