update throwing error

[shutzy]

could someone please corret this for me

strSql = "UPDATE tblOrders " & _
 "SET ClientDetailsID = [ClientWant] " & _
 "WHERE ClientDetailsID = [ClientDelete] " & _
CurrentDb.Execute strSql, dbFailOnError

the [ClientWant] and [ClientDelete] are both numbers. i have been shown how to do this but i still cannot understand it. i thought i would get enough examples to be able to choose the right one and edit the fields but this method is not working.

thanks

 

[AliyuKatsina]

Dear Shutzy,

From your code I do understand that you want to make [CleintDetailsID] = [ClientWants] if [ClientDetailsID] = [ClientDelete] in the tblOrders Table. Am I correct?

 

[shutzy]

ye thats right

 

[AliyuKatsina]

Here is a code that may do it.

Dim rsOrders as recordset

set rsOrders = currentDB.openrecordset("tblOrders")

with rsOrders
.MoveFirst

do untill .EOF
!ClientDetailsID = varID
!ClientDelete = varDel
!ClientWant = varWnt

if varID = varDel then
.Edit
!ClientDetailsID = varWnt
.Update
end if
.movenext
loop
End with

Don't forget to declare varID, varDel and varWnt as appropriate.

 

[shutzy]

what do you mean by?

declare varID, varDel and varWnt as appropriate.

does this declare it?

!ClientDetailsID = varID
!ClientDelete = varDel
!ClientWant = varWnt

 

[AliyuKatsina]

Hi,
What I meant was use the Dim statement to declare the variables eg.
Dim varWnt as long

Hope am clear enough.

 

[shutzy]

so it would be

Dim rsOrders as recordset
Dim varWnt as long
Dim varID as long
Dim varDel as long

set rsOrders = currentDB.openrecordset("tblOrders")

with rsOrders
.MoveFirst

do untill .EOF
!ClientDetailsID = varID
!ClientDelete = varDel
!ClientWant = varWnt

if varID = varDel then
.Edit
!ClientDetailsID = varWnt
.Update
end if
.movenext
loop
End with

with the 1st 4 rows being under options explicit

 

[AliyuKatsina]

Yes.
However the code should be an event proceedure or a module to be called when you want the table to be evaluated.

 

[shutzy]

how do i call a module or event proceedure?

 

[shutzy]

hi, the

do until.EOF

is not likeing the debug compile. it is highlighted in RED in the vba view.
it is on event like you said. any reasons why?

 

[BigHappyDaddy]

Put a <space> between the last 'l' of until and the period.

 Do Until .EOF

 

[toast]

You are missing a space between until and .eof.
So it should read

do until .eof

 

[AliyuKatsina]

Yes. It the missing space between l and dot.