Cycle through Database

[Alan Thiessen]

'What is wrong with this code that it picks up 'Name' from record 1 and puts it into every subsequent record 'rather than moving to record 2 and picking up record 2 'Name' and put that in 'LinkID' of record 2 'etc.
Command226 is a button attached to an Access 97 Form

Private Sub
Command226_Click()
' The goal is to go through a record set, taking information from the 'Name' field
' and puting it into another field called LinkID
Dim db As Database
Dim rst As Recordset
Dim mystring As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("NCNames", dbOpenDynaset)

With rst
Do Until .EOF
If [RES_Y_OR_N] = -1 Then
mystring = Left([LAST_NAME], 10) + Left([FirstName], 5)
Else
mystring = Left([BUS_NAME], 15)
End If
.Edit
![LinkID] = mystring
.Update
.MoveNext
Loop
End With
End Sub

[Edited by Alan Thiessen on 05-28-2000 at 07:30 PM]

maybe this is closer:

'**********************************************************

Private Sub Command226_Click()

Dim db As Database, rst As Recordset, mystring As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("NCNames", dbOpenDynaset)

With rst
Do Until .EOF
If .Fields("RES_Y_OR_N") = -1 Then
mystring = Left(.Fields("LAST_NAME"), 10) + Left(.Fields("FirstName"), 5)
Else
mystring = Left(.Fields("BUS_NAME"), 15)
End If
.Edit
.Fields("LinkID") = mystring
.Update
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set db = Nothing

End Sub

'**********************************************************

anyway, if i understood your goals right, it would be much more efficient to run an 'update query'. something similar to this should work:

'**********************************************************

Private Sub Update()

Dim strSql As String

strSql = "UPDATE NCNames SET LinkID=IIF(RES_Y_OR_N=-1, LEFT(LAST_NAME, 10) & LEFT(FirstName, 5), LEFT(BUS_NAME, 15)"

CurrentDb.Execute strSql

End Sub

'**********************************************************

hope this helps

Sascha