-
'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
