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

[Seth Schrock]

Relationships make the data just as connected as having them in the same table.

I don't see a primary key field (as a normalized database would have). How do I know which OP goes with which OPCONT1 in the last group of post #8. There needs to be some way for the CONT1 record to be connected to the plain OP record.

Anyway, you can use an UPDATE query and just use references to your form controls to get the values that you want to update the record to. You just need to setup your WHERE clause to find the correct CONT1 record.

[nico5038]

What do you mean with "I have already a normalized database and in a table I have informations on the top." ?

When the database is normalized this statement would imply that the "top" information is a query...

Nic;o)

[julienmy5757]

As I already said, OP and OPCONT1 have the same OP .. And routing, and COMPPN ...
OP cont1 is the continuous quote of OP.
I didn't put all the data of the table it was just to explain, I have a primary key and it is a auto number so OP and OPCONT1 are different for the primary key.
If I use the update query I have the error on the top, and even if I use parameters like you can see on my code.
So I am asking you if you see something wrong, to tell me how to correct it so I will be able to update data as I want

Thank you

[julienmy5757]

The where is already set up to find the link between the quote numbers. I select one OP with a control of my form, and after I say in an other table that the quote number is this OP with "cont1" at the end.

This is on my code as you can see

[Seth Schrock]

I think that I'm starting to get the information that I need, although I'm still a little confused about what the two different blocks of code are for in your original post. Try this

Dim db as DAO.Database
Dim strUpdate As String

Set db = CurrentDb

strUpdate = "UPDATE tblRoutingMain SET SEQ = " & Me.Seq & _
			" WHERE [Quote Number] = '" & Me.[Quote Number] & "CONT1' " & _
			"AND COMPPN = '" & Me.COMMPN & "' AND Routing = '" & Me.Routing & "'"

DoCmd.SetWarnings False
db.Execute strUpdate, dbFailOnError
DoCmd.SetWarnings True

Set db = Nothing

I guessed that your control names were the same as the field names. If not, then you would need to edit them in the strUpdate string. This would have to go in the form's module so that the Me. references will work.

[nico5038]

@julienmy5757: You obviously need to look into normalization a bit longer, as the autonumber isn't the "functional " primary key, it's a technical solution to get a unique index in a single field.

One (only partial normalized) solution could be to drop the SEQ field and create an additional table with:
ROUTING SEQ
Heat treat 10
Passivate 20
Chem film 30

To get the sorting by SEQ, just JOIN the ROUTING field to the tblRouting and you can use the field in the ORDER BY.
Additional advantage is the possibility to have a combobox to select the ROUTING value from the tblRouting, thus making sure no typo's are made.

Nic;o)

[julienmy5757]

Nico, routing doesn't depend of seq ! I can have "passivate" for 10,20, 30.. And if I create a other table with primary key and join this table by primary key after I have to change also my records in this table when I am doing modifications with my software. So the problem is still the same. Update records with criterias..
Seth, in your code you take directly records from my form without using table tblRoutingmain ( criteria = QUOTE NUMBER without CONT1) right ? It could be a solution more simple than what I am trying to do.
Because what I am trying to do is 1: to create query with record like quote number = like my form,without CONT1. 2: create an update table to update SEQ where quote number = quote number and CONT1, COMPPN like in my query and Routing like in my query.

I let you know if it works thank you all !!!

[Seth Schrock]

You are correct that I'm directly referencing your form and not the table. It will pull the values from which ever record you are on. Be careful that you aren't on a CONT1 record when you run this it won't find any records.

[julienmy5757]

I have a special form who appears when I have Op and OpCont1 in the same table, and when I am on the OP record. I will put this code in. And I will use it for all the textbox after update if it works ! thank you again I let you know as soon as possible maybe I will have an error again with "item not found"

[julienmy5757]

( even if I hope not )

[julienmy5757]

About the end of Me.seq do you think that I dont't have to put '" Me.Seq & "' instead of just '" Me.Seq
? Because It doesnt work I have the error : end of statement expected

[Seth Schrock]

If Me.Seq is a text field then you would need single quotes around the field. If you could put a debug.print in line 9 of my code, run it and then attach what is printed in the immediate window, that would help (to open the immediate window, press Ctrl + G).

[julienmy5757]

I tried but when I run the code I have syntax error missing operator

[Seth Schrock]

I haven't tested it, but I think it will work.

[julienmy5757]