Cannot update field

[Rob]

Here's what I'm trying to do

1) open a recordset RS based on a SQL query on a single table T. The
RS should have 3 fields: T.pk (long, primary key), T.fld1 (a text
field), and calcFld, a numeric field which will be updated later. I
did this:

Set RS = CurrentDb.OpenRecordset("select pk, fld1, 0 as calcFld
from T")

2) Go through RS record by record and update calcFld based on a
user-defined function UDF

With RS
.Edit
Do While NOT .EOF
!calcFld = UDF( !fld1 ) <----------- Error occurs here
.MoveNext
Loop
.Update
End With

3) Find the 5 (say) records in RS with the greatest value in calcFld

Trouble is I cannot update the calcFld field (I get an error 3164).
The DataUpdatable property is false, but the other two fields are
updatable. I put in a 0 for the value just as a placeholder. That's
probably not the right way to do things...

How can I force that calcFld to be updatable?

Thanks in advance!

 

[Ofer Cohen]

Try this instead, move the edit inside the loop.
It supose to move record and then edit, and not the other way around

With RS
Do While NOT .EOF
.Edit
!calcFld = UDF( !fld1 ) <----------- Error occurs here
.Update
.MoveNext
Loop
End With

 

[Rob]

Thanks. I don't think this helps... the DataUpdatable property for
that particular field is still FALSE (but, curiously enough, the other
two have dataUpdatable = TRUE).

It isn't allowing me to even do the first update, so moving to the next
record before or after the .edit doesn't help. I assume it WILL help
though, once the bigger problem is cracked.

 

[Ofer Cohen]

Check if the function UDF return any value, mybe the error occur in the
function.
Step the code using the F8 key and see if you get an error in the function.

 

[Rob]

I'm pretty sure it's not the function. For testing purposes, I just
changed the line to

!calcFld = 1

That returned the same error. I'm pretty sure the problem is that it
is deeming that field to be non-updatable. I am just trying to figure
out why.

Could it be that it wants to tie the values in the recordset to the
table from which the data was generated? The whole point of the
recordset was to separate the two (I don't want, in the end, to change
the table data at all). If this is the case, then I can see why it
won't let me update the value -- there's no corresponding value in the
table to change!

Thanks again,
Rob

 

[John Spencer]

Assuming your table "T" has a field named CalcField, change

Set RS = CurrentDb.OpenRecordset("select pk, fld1, 0 as calcFld from T")
to
Set RS = CurrentDb.OpenRecordset("select pk, fld1, calcFld from T")

If you wish to force the value of CalcField to Zero do that in a separate
UPDate query or do it in the loop before you use your function to calculate
the value.

With RS
.Edit
Do While NOT .EOF
!calcFld = 0 'Why you would want to do this, I am not sure but
....
!calcFld = UDF( !fld1 ) <----------- Error occurs here
.MoveNext
Loop
.Update
End With

 

[Ofer Cohen]

Thanks John, I can't believe I missed the "0 as calcFld"
That answer the question why he can't update this field.

 

[Rob]

Thanks. Thing is that there is no calcFld in the table T. I'm pretty
sure that's the problem. I could change the table to have such a
field, but I'd really rather not, as that value is extremely temporary.

 

[Ofer Cohen]

Then create a query instead of running an update function on a field that
doesn't exist.

Select pk, fld1, UDF([fld1]) as calcFld From T

This query will run the function and return the calcFld.

 

[Rob]

Brilliant! Thanks, I think this will work.