FOR EACH statement trouble!!??

Not sure if I am even on the right track.... I am using an SQL 2000 database. I need to update/compare two tables and I'm looking for a logical way to do it....

I need to check all records in one table to see if the text in one particular column is equal to the text in another column in a seperate table.... and if so, perform an update query.

OK, this is confusing I know. I have a table "chains", FOR EACH record in chains, I need to take the field "speciminid" and see if it matches the field "speciminid" in my other table "records"

And if speciminid in chains = speciminid in records, set chainpath = the chainnum(automatic integer) for that particular record in chains!

I think I am way off, but I set up a form, linked with an ADODC control to my table chains. then I have a command that executed this...

Private Sub Command1_Click()
Dim chains As Collection
Dim chainnum As Variant
Dim speciminid As Variant
For Each chainnum In chains
DataEnvironment1.AutoUpdateRecords chainnum, speciminid 'this is the update query to enter the chainnum into the records table if Speciminid in chains = speciminid in records....
Set DataEnvironment1 = Nothing
Next
End Sub

can anyone help? I suck at coding.

thanks for any input or suggestions anyone might have!

Kristin

Hi,

If I may ask, why go through so much trouble writing a complete script to do something that the DBMS (SQL2000) can do just as easily with a single statement? Let me explain. If I understand you correctly, you have two tables with a related field (speciminid). The speciminid is a one on one relation with both chains and records.

Now, you can make use of this relation by issueing the following update statement on the database:

UPDATE Chains
SET chains.chainpath = o.chainnum
FROM Records a, chains o
WHERE a.speciminid = o.speciminid

This will update every chains record with the chainnum from the record that contains the exact speciminid. This will execute nice and fast. If you make it a stored procedure in the database, you can make it even more faster. Now, if I didn't understand your problem at hand, please give an example of the data (1 or 2 records) and I'll look into it. Any questions, please ask.

Grtz.©

M.