Question Update Field in Table using ADODB Recordset

[npatil]

Hello All,

I have the foll code: I would like to update the "Count" Field based on value of "Field2". I am getting an error for the rs_cluster.EditMode. Please let me now if my If block makes sense or is it only a syntax errro ?


Private Sub cmdCluster_Click()
Dim rs_cluster As ADODB.Recordset
Dim cn_cluster As ADODB.Connection
Set rs_cluster = New ADODB.Recordset
Set cn_cluster = CurrentProject.Connection
rs_cluster.ActiveConnection = cn_cluster
rs_cluster.LockType = adLockOptimistic
rs_cluster.CursorType = adOpenDynamic
rs_cluster.Source = "SELECT TOP 30 tblfirst.Count, tblfirst.Field2, tblfirst.Field3,tblfirst.Field13,tblfirst.Field15,tblfirst.Field15_2, tblfirst.Field23, tblfirst.Field31, tblfirst.Field35, tblfirst.Field36, tblfirst.Field41, tblfirst.Field46, tblfirst.Field51, tblfirst.Field55, tblfirst.Field57, tblfirst.Field58, tblfirst.Field59, tblfirst.Field60, tblfirst.Field61, tblfirst.Field62, tblfirst.Field64, tblfirst.Field65, tblfirst.Field67, tblfirst.Field73, tblfirst.Field82, tblfirst.Field91, tblfirst.Field97, tblfirst.Field108, tblfirst.Field110, tblfirst.Field112, tblfirst.Field117_2, tblfirst.Field119, tblfirst.Field124, tblfirst.Field128, tblfirst.Field129 INTO tblfirstFilter FROM tblfirst"

rs_cluster.Open

Do While Not rs_cluster.EOF
rs_cluster.EditMode
If (rs_cluster!Field2 = 1540362) Then
rs_cluster!Count = 0
rs_cluster.Update
End If
rs_cluster.MoveNext
Loop

 

[Beetle]

Well. you don't say what error you're getting, but a few things stand out.

1) You're creating a make table query and then trying to use it as the source for a record set you want to loop through???? That's not going to work.

2) You're using an Access Reserved word (Count) as a field name. Probably not the issue here, but it will cause you problems at some point.

3)You're using .EditMode incorrectly. This is just a property that returns the editing status of the current record. You don't use it to actually edit a record.

4) You don't really have a table with ~129 fields and field names like Field3, Field97, Field124. Do you?

 

[npatil]

Thanks for the suggestions, yes I figured it out. However, this was only an example and what I would really like to have is to index (ie add a value to "Count") every record where the values of "Field2" are the same. So for example, if I have 3 records of Field2 = "0353085" then I would like to have each of the "Count" field to have a value say "0". Similarly I would like to increment value for all other clusters based on value of Field2.

I am not able to figure out how to reference rs_cluster!Field2 current value with the next one?