Loop Update Field in tbl where criteria = fields in another tbl

H

Hugh self taught

Hi Guys & Gals,

I need to update tblEvtInput.EvtPoints with tblPointsStructure.PointsAwarded
where criteria in other fields in the tblEvtInput meet criteria in the
tblPointsStructure tbl.

I populate the tblEvtInput from results of a competition as a temporary
collection table. I'm not up to speed enough with using recordsets yet but
have copied various sample to get to the code below which must be incomplete
& obviously doesn't work yet. Below that I've listed the query used as well.

Can you point me in the right direction here.

Dim dbs As DAO.Database
Dim rsPointsUpdate As DAO.Recordset
Set dbs = CurrentDb
Set rsPointsUpdate = dbs.OpenRecordset("SELECT [qryEvtInputPtsUpdate].* FROM
(qryEvtInputPtsUpdate)")
With rsPointsUpdate
If .EOF = False And .BOF = False Then
..MoveFirst
Do While Not rsPointsUpdate.EOF
If tblEvtInput.EvtPlaced = tblPointsStructure.Position And
qryEvtInput.CountOfEvt = tblPointsStructure.InFinal Then
tblEvtInput.EvtPoints = tblPointsStructure.PointsAwarded
End If
..MoveNext
Loop
End If
End With
rsPointsUpdate.Close
Set rsPointsUpdate = Nothing
dbs.Close
Set dbs = Nothing


Query
SELECT tblEvtInput.EvtInput_Idx, tblEvtInput.EvtCompID,
tblEvtInput.EvtNumber, tblEvtInput.EvtStructID, tblEvtInput.EvtCplID,
tblEvtInput.EvtPlaced, tblEvtInput.EvtPoints, tblEvtInput.EvtFinal,
tblEvtInput.EvtTotalPts, tblPointsStructure.PtsStruct_Idx,
tblPointsStructure.Position, tblPointsStructure.InFinal,
tblPointsStructure.PointsAwarded
FROM tblEvtInput INNER JOIN tblPointsStructure ON
tblEvtInput.EvtPoints=tblPointsStructure.PointsAwarded;
 
H

Hugh self taught

Hi Alex,

Now I can't get the Update query to work either. The update query gives me a
text box for the field qryEvtInputNum.CountOfEvt which is a query to give me
count per event from the following sql

SELECT tblEvtInput.EvtNumber, Count(tblEvtInput.EvtNumber) AS CountOfEvt
FROM tblEvtInput
GROUP BY tblEvtInput.EvtNumber;

My Update query is

UPDATE tblEvtInput INNER JOIN tblPointsStructure ON
tblEvtInput.EvtPoints=tblPointsStructure.PointsAwarded SET
tblEvtInput.EvtPoints = [tblPointsStructure].[PointsAwarded]
WHERE (((tblPointsStructure.InFinal)=qryEvtInputNum.CountOfEvt) And
((tblPointsStructure.Position)=tblEvtInput.EvtPlaced));

Could it be because the field is a calculated value?

Alex Dybenko said:
Hi,
just make an update query, much more easy then writing such code

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



Hugh self taught said:
Hi Guys & Gals,

I need to update tblEvtInput.EvtPoints with
tblPointsStructure.PointsAwarded
where criteria in other fields in the tblEvtInput meet criteria in the
tblPointsStructure tbl.

I populate the tblEvtInput from results of a competition as a temporary
collection table. I'm not up to speed enough with using recordsets yet but
have copied various sample to get to the code below which must be
incomplete
& obviously doesn't work yet. Below that I've listed the query used as
well.

Can you point me in the right direction here.

Dim dbs As DAO.Database
Dim rsPointsUpdate As DAO.Recordset
Set dbs = CurrentDb
Set rsPointsUpdate = dbs.OpenRecordset("SELECT [qryEvtInputPtsUpdate].*
FROM
(qryEvtInputPtsUpdate)")
With rsPointsUpdate
If .EOF = False And .BOF = False Then
.MoveFirst
Do While Not rsPointsUpdate.EOF
If tblEvtInput.EvtPlaced = tblPointsStructure.Position And
qryEvtInput.CountOfEvt = tblPointsStructure.InFinal Then
tblEvtInput.EvtPoints = tblPointsStructure.PointsAwarded
End If
.MoveNext
Loop
End If
End With
rsPointsUpdate.Close
Set rsPointsUpdate = Nothing
dbs.Close
Set dbs = Nothing


Query
SELECT tblEvtInput.EvtInput_Idx, tblEvtInput.EvtCompID,
tblEvtInput.EvtNumber, tblEvtInput.EvtStructID, tblEvtInput.EvtCplID,
tblEvtInput.EvtPlaced, tblEvtInput.EvtPoints, tblEvtInput.EvtFinal,
tblEvtInput.EvtTotalPts, tblPointsStructure.PtsStruct_Idx,
tblPointsStructure.Position, tblPointsStructure.InFinal,
tblPointsStructure.PointsAwarded
FROM tblEvtInput INNER JOIN tblPointsStructure ON
tblEvtInput.EvtPoints=tblPointsStructure.PointsAwarded;
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
yes, count can be a reason that query becomes not updatable.
I would try then do via temporary table - put result of your qryEvtInputNum
into new table (using select into or insert into query) and then run same
update query but using this new table. then delete it (or delete records
from it)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hugh self taught said:
Hi Alex,

Now I can't get the Update query to work either. The update query gives me
a
text box for the field qryEvtInputNum.CountOfEvt which is a query to give
me
count per event from the following sql

SELECT tblEvtInput.EvtNumber, Count(tblEvtInput.EvtNumber) AS CountOfEvt
FROM tblEvtInput
GROUP BY tblEvtInput.EvtNumber;

My Update query is

UPDATE tblEvtInput INNER JOIN tblPointsStructure ON
tblEvtInput.EvtPoints=tblPointsStructure.PointsAwarded SET
tblEvtInput.EvtPoints = [tblPointsStructure].[PointsAwarded]
WHERE (((tblPointsStructure.InFinal)=qryEvtInputNum.CountOfEvt) And
((tblPointsStructure.Position)=tblEvtInput.EvtPlaced));

Could it be because the field is a calculated value?

Alex Dybenko said:
Hi,
just make an update query, much more easy then writing such code

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



Hugh self taught said:
Hi Guys & Gals,

I need to update tblEvtInput.EvtPoints with
tblPointsStructure.PointsAwarded
where criteria in other fields in the tblEvtInput meet criteria in the
tblPointsStructure tbl.

I populate the tblEvtInput from results of a competition as a temporary
collection table. I'm not up to speed enough with using recordsets yet
but
have copied various sample to get to the code below which must be
incomplete
& obviously doesn't work yet. Below that I've listed the query used as
well.

Can you point me in the right direction here.

Dim dbs As DAO.Database
Dim rsPointsUpdate As DAO.Recordset
Set dbs = CurrentDb
Set rsPointsUpdate = dbs.OpenRecordset("SELECT [qryEvtInputPtsUpdate].*
FROM
(qryEvtInputPtsUpdate)")
With rsPointsUpdate
If .EOF = False And .BOF = False Then
.MoveFirst
Do While Not rsPointsUpdate.EOF
If tblEvtInput.EvtPlaced = tblPointsStructure.Position And
qryEvtInput.CountOfEvt = tblPointsStructure.InFinal Then
tblEvtInput.EvtPoints = tblPointsStructure.PointsAwarded
End If
.MoveNext
Loop
End If
End With
rsPointsUpdate.Close
Set rsPointsUpdate = Nothing
dbs.Close
Set dbs = Nothing


Query
SELECT tblEvtInput.EvtInput_Idx, tblEvtInput.EvtCompID,
tblEvtInput.EvtNumber, tblEvtInput.EvtStructID, tblEvtInput.EvtCplID,
tblEvtInput.EvtPlaced, tblEvtInput.EvtPoints, tblEvtInput.EvtFinal,
tblEvtInput.EvtTotalPts, tblPointsStructure.PtsStruct_Idx,
tblPointsStructure.Position, tblPointsStructure.InFinal,
tblPointsStructure.PointsAwarded
FROM tblEvtInput INNER JOIN tblPointsStructure ON
tblEvtInput.EvtPoints=tblPointsStructure.PointsAwarded;
Click to expand...
Click to expand...
Click to expand...
 
Top