linzy

I am wanting to update a row at a time, by a value in another table.

I get two errors one it says that it cant open a closed record, and the other being a error wrong in Update syntax error.

Update a row at a time would you use Update and what is the correct sytnax..thanks

I think the thing about my record being closed is because this line

Set rsUnit = adoconnection.Execute("SELECT unit_id, Unit FROM tblUnit,tblImport WHERE tblImport.Unit = tblUnit.unit_fullname")

should be before the do while bit As I have just realise that it will send the same SQL string each time for me checking each row.

adoconnection.Open connectstring

Set rsUnit = New adodb.Recordset



Do While Not rsUnit.BOF And Not rsUnit.EOF
Set rsUnit = adoconnection.Execute("SELECT unit_id, Unit FROM tblUnit,tblImport WHERE tblImport.Unit = tblUnit.unit_fullname")

adoconnection.Execute "UPDATE tblImport (Unit) VALUES ('" & rsUnit.Fields("unit_id") & "')"
rsUnit.MoveNext
Loop

Thinker

It would help if you could explain step by step what you want to
do. Are you wanting to get values from one recordset and update
a field in a second table?

linzy

I want to read one table a row by row,
One that row I want to select a particular field and compare it to another field within another table b .

Those two fields share the same name, from the other table b I want to be able to get the id number from it and put it back into table a.

hence why I thought I would use update...

Wouldnt be sure how to use two recordsets at once...

linzy

Wont that set all the fields to the id number?

I would only like to set unit field at a row at a time..If that makes sense?

Thinker

Ok, lets take this step by step.
Start by getting an updateable recordset from tablea
Enter a loop
for each row in the recordset, create a second recordset by
selecting tableb where a value in a field in the first recordset =
the value in the field in the tableb
if the resulting recordset is not empty (BOF and EOF are not true)
change the field in the first recordset, and do the update.
Close the second recordset
movenext on the first recordset
repeat the loop if the first recordset is not EOF

I hope this makes sense.

linzy

I started to try and code this, But I understand what you say.



But I dont understand how I can do a for loop to move along each row with a record set. I do no what a for loop is.


for each row in the recordset, create a second recordset by
selecting tableb where a value in a field in the first recordset =
the value in the field in the tableb

My understanding recordsets is this declares a new one for me


Set rsUnit = New adodb.Recordset
Set rsUnit = adoconnection.Execute( my sql command)

Thinker

Taking your example a bit farther...

linzy

do
rsImport = adoconnection.execute( "select unit_id from tblb where tableaUnit = '" table b & rsUnit!Unit_Fullname & "'")
If Not (rsImport.BOF And rsImport.EOF) Then
tablea = table b
rsImport!Unit = tableb!unit_id
rsImport.Update
End If
rsImport.MoveNext
Loop Until rsImport.EOF

thats what I am after..if that makes sense?

Thinker

There are things in your code I don't understand. Your select
statement in the loop is trying to select from a table named,
tblb, referencing a field named, tableaUnit. Maybe those really
are the names of your table and field.
I also have no idea about this statement,
tablea = table b
This makes no sense to me.
Otherwise, you seem to have the idea.

Good luck

linzy

Thinker I think I am confused.

All I no is that I have a table with a load of values in it.

And at a row at a time. I want to check a certain field within that table against the another table within the database.

So the other table I need to check through each row to see if the first table field value exists within the second table and if it does and it should to update the first table with the other tables id number for that value.

a row at a time
In the first bit Table AUnit field will equal a string
Table B (field) will also be a string

So I have table A and table A (Unit field) and want to check each row in Table B to see if table A(Field) matches table B(Field).

In the second bit Table b (id) for the table A (unitfield) = table b (field)

I would like table B (id) to updated in the table A (Unit Field)

If it does I want to take Table B (id) for that row and Update Table A (Unit field) with that value.

I would like to do this for each row.

When you suggested the For loop I think that was correct but I dont understand how to handle two recordsets at once...