Trying to Loop through records in a Table and Update Field (1 Viewer)

[ronwoj67]

Trying to loop through the table "tbl247Stasging" and see if the field "EmployeeID is Null. If it is, I want to edit and update it with the current value of IDNUM, then increase the IDNUM value by 1 and continue looping until the end of the file.

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Private Sub UpdateNullEmpID()
  
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tbl247Staging")
Dim IDNum As Long
IDNum = 1
Dim i As Integer

    For i = 0 To rs.RecordCount - 1
         If rs.Fields.EmployeeID.Value = Null Then
            rs.Edit
            rs.Fields.EmployeeID.Value = IDNum
            rs.Update
            IDNum = IDNum + 1
            Debug.Print rs.Fields.EmployeeID.Value
            DoCmd.RefreshRecord
         Else
         End If
         rs.MoveNext
    Next i
   
rs.Close
Set rs = Nothing
  
End Sub

 

[BeeJayEff]

... and what happens when you run that code ?

 

[jdraw]

Perhaps

 If IsNull(rs.Fields("EmployeeID").....

Note: Before you attempt to update your recordset, you should set up a SELECT query to make certain you are selecting the proper records for processing.

 

[pbaldy]

Two thoughts. You can't test for Null that way, try this (also used a shorter way of referring to the field):

If IsNull(rs!EmployeeID) Then

Also, you can't count on the record count being correct without a MoveLast. I'd use:

Do While Not rs.EOF
...
rs.MoveNext
Loop

 

[ronwoj67]

... and what happens when you run that code ?

I get: Compile error: Methond or data member not found (it stops on "rs.Fields.EmloyeeID"

 

[pbaldy]

Did you notice the other suggestions?

 

[apr pillai]

Besides implementing the above suggestions, remove the Docmd.RefreshRecord command from the code. There is no such command in Access2007, not sure about later versions. If it works with newer versions of Access then there is a chance that the record pointer will reset to the first record, every time you execute that command.

 

[pbaldy]

RefreshRecord was added in 2010. It would refresh the form, so while I doubt it's necessary here, it shouldn't affect the recordset pointer.