Update Query

[Pete Provencher]

Using Access 2000:

I want to update a field, [Agent], with one of three names. In the first
record it would put the first name, in the the second record the second
name, in the third record the third name, and the start the cycle all over
again with the fourth and following records. As such:

Record Agent
1 Jones
2 Smith
3 Lee
4 Jones
5 Smith
6 Lee


Pete Provencher

 

[Guest]

Pete,

Below is a Sub that whould get you started. You need a reference to DAO 3.6

Public Sub AddName(
Dim strSQL As String, TheName As Strin
Dim db As DAO.Databas
Dim rs As DAO.Recordse
Dim i As Lon

strSQL = "select agent from table1
Set rs = CurrentDb.OpenRecordset(strSQL

' if you want a special record orde
' use a query instead of strSQL for the record se
'Example : Set rs = CurrentDb.OpenRecordset("qryAddNames"

i =

With r
If .BOF And .EOF The
MsgBox "no records found to update" & vbCrL
En
.MoveFirs
Do While Not .EO
Select Case i Mod
Case
TheName = "Jones
Case
TheName = "Smith
Case
TheName = "Lee
Case Els
MsgBox "error
End Selec

.Edi
!Agent = TheNam
.Updat
.MoveNex
i = i +
Loo
End Wit


rs.Clos
Set rs = Nothin
Set db = Nothin
End Su


HT

Stev

 

[John Vinson]

I want to update a field, [Agent], with one of three names. In the first
record it would put the first name, in the the second record the second
name, in the third record the third name, and the start the cycle all over
again with the fourth and following records. As such:

You're out of luck. A Table is an unordered "bag" of data; it HAS NO
ORDER, not in any usable way. There *is* no "first record" or "third
record".

If you have some field or fields within the table which will let you
sort the data into a particular order, you can use the DCount()
function to get the desired results:

UPDATE yourtable
SET AGENT = Choose(1 + Dcount("*", "[yourtable]", "[sortfield] <= " &
[sortfield]) MOD 3, "Smith", "Jones", "Lee");