CODEWITHSUNDEEP
ms-accessvba
Tim
Tim

Reputation: 105

Looping through a all items in a field and updating another field using VBA in access

I have two fields, one is just the number of the other one, e.g. Field 1 = "12AB" and Field 2 is "12". I'm trying to make Field 2 auto-update but I'm not sure how.

I'm trying to get:

ClassName ClassYear
12AB      12
13BU      13
15BE      15

But instead the whole fields update to the number in the last one:

ClassName ClassYear
12AB      15
13BU      15
15BE      15

The code I currently have is

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Set db = CurrentDb
  Set rst = db.OpenRecordset("MasterTable")

  With rst
  Do Until .EOF
    .Edit
    If (Left(ClassName.Text, 1) = "1") Then
      !ClassYear = Left(ClassName.Text, 2)
    Else
      !ClassYear = Left(ClassName.Text, 1)
    End If
    .Update
    .MoveNext
  Loop

  .Close
  End With

Help is much appreciated! Thank you :)

Upvotes: 0

Views: 210

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

How about something on the lines of:

 sSQL = "UPDATE MasterTable SET ClassYear=Left(ClassName,2) " _
      & "WHERE Left(ClassName,1)='1'"
 CurrentDB.Execute sSQL, dbFailOnError

 sSQL = "UPDATE MasterTable SET ClassYear=Left(ClassName,1) " _
      & "WHERE Left(ClassName,1)<>'1'"
 CurrentDB.Execute sSQL, dbFailOnError

Upvotes: 3

Related Questions