Using Recordsets

Example.....
This is best described with an example as its a little complex to explain

Typical Table....

Date          Batch      Machine   Qty
01/01/13     M10           D5         1000
02/01/13     M09           P5          850
03/01/13                                      60
04/01/13                                       70
04/01/13      M12        K21           125
05/01/13      M17         K5             111
06/01/13                                         80
07/01/13      M18         J6              113


I would like to seach through all records in a table, starting at last record and going backwards.  When i see blanks in Batch, i would like to populate with data from previous record.  Results below......

Date          Batch      Machine   Qty
01/01/13     M10           D5         1000
02/01/13     M09           P5          850
03/01/13      M12        K21           60                                
04/01/13      M12        K21           70
04/01/13      M12        K21           125
05/01/13      M17         K5             111
06/01/13      M18         J6                80
07/01/13      M18         J6              113

Apologies if its confusing......
SweetingAAsked:
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.

PatHartmanCommented:
Sorry that isn't the way relational databases work.  Tables/queries are unordered sets of data.  When you run a query against a table, the records are returned in an order convenient to the database engine.  Only if you include an order by clause will you ever be able to predict the row order and only if you are ordering by a unique identifier will the recordset order be repeatable from one run to the next.  Opening a table directly either via the GUI or with DAO/ADO actually runs a query so the same statement applies.

If you have an autonumber primary key.  That will maintain data entry order and if the records happen to have been imported in the order you need, then you can create a query that orders by the autonumber, descending.  Then using DAO or ADO, create a code loop that updates records as you requested.  If you don't have a unique identifier that can sort the rows into the order specified, you will never be able to fill in the blanks.

This file probably came from Excel, so you could go back to the source to fix it up and reimport it.  Since Excel is NOT a relational database, it maintains record order (unless you sort) so you can write the same (OK, almost the same) code in Excel that you would have written in Access to fill in the blanks and then reimport the file.
0
SweetingAAuthor Commented:
The data is in the order i need it as i moved it to a query, sorted it how i wanted it and then made it back into a table in the correct order.

i really don't want to do this in excel, they are records that i link to in AS400 and i don't want to have to handle them twice
0
Gustav BrockCIOCommented:
Open the recordset with:

Select * From tblYourTable Order By Date, Batch, Machine, Qty Desc

rs.MoveLast
While rs.BOF = False
   If Not IsNull(rs!Batch) Then
     vBatch = rs!Batch.Value
     vMachine = rs!Machine.Value
   Else
     rs.Edit
       rs!Batch.Value = vBatch
       rs!Machine.Value = vMachine
     rs.Update
   End If
   rs.MovePrevious
Wend
rs.Close

Air code, not tested.

/gustav
0

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
I'm just curious...

Why are the values blank to begin with?
You can set up the data entry so that these fields cannot be left empty.
0
SweetingAAuthor Commented:
Unfortunately i can not do that in a short time and this is quick fix for thousands of historical records
0
SweetingAAuthor Commented:
Tried this....but get error on set rs line "Error 3061, too few parameters, expected 1"

Private Sub Form_Load()

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

sqlstr = "SELECT * FROM tbl_AS400_OPCP04 ORDER BY Date, Batch, Machine"

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

rs.MoveLast

While rs.BOF = False
   If Not IsNull(rs!Batch) Then
     vBatch = rs!Batch.Value
     vMachine = rs!Machine.Value
   Else
     rs.Edit
       rs!Batch.Value = vBatch
       rs!Machine.Value = vMachine
     rs.Update
   End If
   rs.MovePrevious
Wend
rs.Close

End Sub
0
Gustav BrockCIOCommented:
Then either Date, Batch, or Machine doesn't match the actual fieldnames.
Or it doesn't like Date? Try with:

SELECT * FROM tbl_AS400_OPCP04 ORDER BY [Date], [Batch], [Machine]

or:

SELECT * FROM tbl_AS400_OPCP04 ORDER BY tbl_AS400_OPCP04.Date, tbl_AS400_OPCP04.Batch, tbl_AS400_OPCP04.Machine

/gustav
0
SweetingAAuthor Commented:
worked perfectly thanks
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.