Update loop not working

[iglobalusa]

Can anyone see why this code is not updating the field?

Private Sub txtstateno_AfterUpdate()
On Error GoTo Error_txtstateno_AfterUpdate
    'look for all records with the same stateno then update the same edited field
    strSQL = "Select * from labdata Where stateno = '" & Me.stateno & "'"
    Set DAO_DB = CurrentDb
    Set DAO_RS = DAO_DB.OpenRecordset(strSQL, dbOpenDynaset)
 
    With DAO_RS
        If Not .EOF And Not .BOF Then
            Do until.EOF
                'edit
                .Edit
                    !stateno = Me.stateno
                    !transfer = 0
                'commit the edit
                .Update
                'move to next record if not EOF
                .MoveNext
            Loop
            .Close  
     End With
    'clear recordset
    Set DAO_RS = Nothing
Exit_txtstateno_AfterUpdate:
     Exit Sub
Error_txtstateno_AfterUpdate:
     MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & _
     Err.Description, vbInformation, "Error:"
     Resume Exit_txtstateno_AfterUpdate
End Sub

Thanks for your help.

 

[Galaxiom]

It is probably working but you would not notice because you are creating a recordset that selects records that are already the same as the value you are updating to.

BTW. There is no point selecting all the fields in the recordset if you are only updating one.

The comments in your loop are pointless and detract from the readability of code. What is the point of a comment 'edit before a command .Edit?

 

[pbaldy]

Also, it would be more efficient to execute an update query:

DAO_DB.Execute "UPDATE labdata SET transfer = 0 WHERE stateno = '" & Me.stateno & "'"

 

[WayneRyan]

ig,

Instead of a recordset:

CurrentDb.Execute "Update labdata ' & _
                             "Set      transfer = 0 " & _
                             "Where stateno = '" & Me.stateno & "'"

Wayne

 

[iglobalusa]

Also, it would be more efficient to execute an update query:

DAO_DB.Execute "UPDATE labdata SET transfer = 0 WHERE stateno = '" & Me.stateno & "'"

Paul:

What I'm trying to do, and not very efficiently, is to allow the user to modify the stateno field on the current record on a form and have the code update all the statenos on hundreds of records with the new stateno that the user just edited. I'm realizing just now that it is only modifying the current record because it is using the new value of the stateno to search for other statenos whose value is the previous stateno. I should save the orginal value of the stateno (txtstateno_old) before editing it and use this old value to search for records with the same statenos prior to the edit.

So in your query I should change Me.stateno to txtstateno_old. Is this correct?

Thanks,
IG

 

[Galaxiom]

to allow the user to modify the stateno field on the current record on a form and have the code update all the statenos on hundreds of records with the new stateno that the user just edited.

This need indicates your data structure is the problem. The value you are updating should be stored once and the records related to that single record on stateno.

 

[iglobalusa]

ig,

Instead of a recordset:

CurrentDb.Execute "Update labdata ' & _
                             "Set      transfer = 0 " & _
                             "Where stateno = '" & Me.stateno & "'"

Wayne

Wayne:

Thanks for your similar suggestion. Please refer to my reply to pbaldy.

ig

 

[iglobalusa]

This need indicates your data structure is the problem. The value you are updating should be stored once and the records related to that single record on stateno.

Thanks for your suggestions.

ig

 

[pbaldy]

So in your query I should change Me.stateno to txtstateno_old. Is this correct?
IG

Yes, and add that field to the update query (though I would also question the structure).

 

[gemma-the-husky]

you could probably achieve this in the relationship pane - define this link, and set this one to "cascading updates" Then when you change a master value, all the linked values will change.

I agree though, that generally this requirement often indicates a normalisation mistake.