Using Recordset to Automatically Add Missing Data

[Robjenj]

Hi guys! I am trying to use a DAO recordset in an Access2007 client file to loop through a series of records using vba to find in a record where a specific field is null, and if so set the field equal to the record ID.

I have tried:-
Dim db As Database
Dim rs As DAO.Recordset
'Open QRY_FirstAccNo as a recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT TBL_Accession.AccessionID, TBL_Accession.FirstAccNo FROM TBL_Accession ", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveLast
Do While Not rs.BOF
If Nz(FirstAccNo, 0) = 0 Then
1. If rs![FirstAccNo] = 0 Then
rs![FirstAccNo] = rs![AccessionID]
End If
End If
rs.MovePrevious
Loop
End If
rs.Update
rs.Close
Set rs = Nothing

But it keeps telling me at point 1. above that the expression refers to an object that is closed. The dataset that I think I am creating should look something like:-

TBL_Accession:-
AccessionID (autonumber) FirstAccNo
21349 --------------------21349
21350 --------------------21350
21351
21352
21353
I need to get the routine to fill in the blanks, which should be equal to the AccessionID

I would be very grateful for any help anyone could offer.

 

[Beetle]

If we ignore the fact that you're storing the same data in two different fields , then the next question is why are you using a record set loop when a simple update query would do (and be more efficient)?

Dim strSQL As String

strSQL = "Update TBL_Accession Set FirstAccNo = AccessionID " _
       & "Where FirstAccNo Is Null;

CurrentDb.Execute strSQL, dbFailOnError

 

[Robjenj]

Many thanks Beetle! Reason for two apparently identical sets of data is that first column is used to identify occasions of a wildlife animal being brought into a wildlife hospital, whereas the second column provides an ID by which the animal is then known throughout maybe several re-visits before it is released or dies, so once first admitted, on subsequent visits the Accession number and FirstAccNo will differ.
Answer to Q2 is because I had never thought of it! Beautifully simple. Thank you again, and I'll give it a go.