Updating a recordset

[marcuscoker]

Hi I have the following problem

I have written a script that i want to loop through a recordset. Everytime the script finds a blank in the firstname column, to take the value of the previous firstname and populate it with that, but the script does not appear to be working can anybody help me?

Sub EditRecords()



Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("WHO ENTERED THE DUPLICATE")
rs.MoveFirst

Do While Not rs.EOF

'store last surname in variable

Dim sFirstName, SFirstNameStored
sFirstName = rs!Firstname
If sFirstName <> "" Then
SFirstNameStored = sFirstName

End If

If sFirstName = "" Then
sFirstName = SFirstNameStored
End If

rs.Edit
rs!Firstname = UCase$(rs!Firstname)
rs.Update



rs.MoveNext



Loop
rs.Close



End Sub

 

[allan57]

Try the following, your code was resetting the variables

Dim db As Database
Dim rs As Recordset
Dim sFirstName as string
Dim SFirstNameStored as string
Set db = CurrentDb
Set rs = db.OpenRecordset("WHO ENTERED THE DUPLICATE")
rs.MoveFirst

Do While Not rs.EOF

'store last surname in variable
sFirstName = rs!Firstname
If sFirstName <> "" Then
SFirstNameStored = sFirstName
End If

If sFirstName = "" Then
sFirstName = SFirstNameStored
End If

rs.Edit
rs!Firstname = UCase$(rs!Firstname)
rs.Update
rs.MoveNext

Loop

rs.Close

End Sub