Table is not updating...

S

SF

Hi,

I have 3 tables:
tblProjects
Pr_ObjectID PK
Pr_Date Date/time

tblContracts
Ck_ContractID PK
Ck_Date Date/Time
......

and tblMileStones
MileID Autonumber
Ms_Pr_ObjectID Number relate to Pr_ObjectID in tblProjects
Ms_Ck_ObjectID Number relate to Ck_ContractID in tblContracts
MileStones...

tblContract will use/share the milestone enter during registering project.
In my Contract entry form, I try to update the Ms_Ck_ObjectID with the value
of new Ck_ContractID (varObjectID). Only the first row is updated.

How can I move thru the recordset that match the criteria and updating the
Ms_Ck_ObjectID field?


dim dbs as dao.database
dim rstMileStone as dao.recordset
Dim strCriteria As String
Dim ctl As Control
Set ctl = Me.lstRelateProposal

set dbs = currentdb
Set rstMileStone = dbs.OpenRecordset("tblProjectMileStones",
dbOpenDynaset)

For Each varItm In ctl.ItemsSelected
strCriteria = "Ms_Pr_ObjectID = " & ctl.ItemData(varItm)
With rstMileStone
'Do While Not .EOF
.FindFirst strCriteria
.Edit
![Ms_Ck_ObjectID] = varObjectID
![Owner] = CurrentUser()
![CreatedBy] = CurrentUser()
![DateUpdate] = Now()
![DateCreated] = Now()
'.MoveNext
.Update
.MoveNext

Next varItm
rstMileStone.Close
DoCmd.Hourglass False
 
P

pietlinden

Use a query to set up your update. Just build it with the QBE grid.
Why make things so hard for yourself?

Or execute the update from within your code But Execute requires an
action query
(Update, Delete, DDL)


'--fake code, but you get the idea...
strSQL="UPDATE MyTable SET MyField=10, OtherField='Some text value',
SomeDateField=Now WHERE ....

dbengine(0)(0).Execute strSQL, dbFailOnError
 
Top