asked on

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......

PatHartman

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.

SweetingA

ASKER

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

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.

SweetingA

ASKER

Unfortunately i can not do that in a short time and this is quick fix for thousands of historical records

SweetingA

ASKER

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

Gustav Brock

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

SweetingA

ASKER

worked perfectly thanks

Gustav Brock

You are welcome!

/gustav