On Apr 20, 4:38 am, Mikle <mikl...@gmail.comwrote:
Quote:
I need to update values in a table (where they are null or zero) with
values from another table for the corresponding PopulationMemberID.
>
Now I now that you can't update where there is a join.. but the join
is in the nested select.. so it should work right?
>
CurrentDb.Execute ("UPDATE Samples SET
samples.SampleMemberEmployees=(SELECT pop.PopMemberEmployees FROM
population as pop WHERE [Pop].[PopulationMemberID]=[samples].
[PopulationMemberID]) WHERE nz(sampleMemberEmployees,0)=0 AND
SurveyID=" & CurrentSurvey)
>
when the query is executed i get: :"Operation must use an updatable
query. (Error 3073)"
>
I worked around this by creating a vba function accepting the Id as a
parameter.. but its slow..
Try something like this (it should be sufficiently fast):
dim db as dao.database, r as dao.recordset, q as dao.recordset, s as
string
set db=currentdb
q=db.createquery("")
s="Select ...;"
set r=db.openrecordset(s, dbopensnapshot)
r.movefirst (Not really necessary)
do while not r.eof
s="Update Samples set SampleMembersEmployees = " & r!
PopMemberEmployees _
& " WHERE ..." (fill in correct statement here)
q.sql = s
q.execute
r.movenext
loop
close r
set r=nothing
set q=nothing
This should be fairly fast and quite a bit quicker than using the VBA
function.
-- Larry Engles
Access developer since day 1 of Access 1.0
