Logic within recordsets

I have a nice neat piece of code which was kindly scripted for me by one of the experts which looks for blank batch numbes in a table (starting at the bottom of the table) and copies the next batch number down into the blank record (see attached code).

This works beutifully but its only a part of the solution.

I need to add a small piece of logic......

If the machine number on the blank batch number record is not equal to the machine number on the next record down then instead of copying the batch number upwards i would like find the next non blank batch number above and copy that in.

If neither machine matches (which is very rare) then i would like to put in batch number X000001

Not sure if this makes sense or not but i will inclde a small example.....

Before current code run......

DateStamp     Machine    Batch
01/01/13            M2             B12
02/01/13            M1
03/01/13            M1
03/01/13            M1             B17
03/01/13            M6             B09
04/01/13            M6        
05/01/13            M6            
05/01/13            M7             N10

Current Result..................

DateStamp     Machine    Batch
01/01/13            M2             B12
02/01/13            M1             B17   OK
03/01/13            M1             B17   OK
03/01/13            M1             B17
03/01/13            M6             B09
04/01/13            M6             B10   NOK
05/01/13            M6             B10   NOK
05/01/13            M7             B10

Desired Result....................

DateStamp     Machine    Batch
01/01/13            M2             B12
02/01/13            M1             B17   OK
03/01/13            M1             B17   OK
03/01/13            M1             B17
03/01/13            M6             B09
04/01/13            M6             B09  OK
05/01/13            M6             B09  OK
05/01/13            M7             B10

Current code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlstr As String

sqlstr = "SELECT * FROM tbl_AS400_OPCP07 ORDER BY Machine, DateStamp, Batch"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)


While rs.BOF = False
   If Not IsNull(rs!Batch) Then
     vBatch = rs!Batch.Value
     vMachine = rs!Machine.Value
       rs!Batch.Value = vBatch
       rs!Machine.Value = vMachine
   End If

I know its a little complex but your asistance would be much appreciated.

Thankyou in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
why not just use an update query, it will be a lot faster and simple
try this query, create a backup of the table first before running the query

Update tbl_AS400_OPCP07 as A
Inner Join
(Select B.Machine, B.Batch
  From tbl_AS400_OPCP07 as B
  Where B.Batch Is not null) as C
On A.Machine=C.Machine
Set A.Batch=C.Batch
Where A.Batch is null


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SweetingAAuthor Commented:
Thats very clever, thankyou very much but can you explain in words what its doing as i can't quite follow it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.