Record Number Function/Old FoxPro User

[cgdrake]

I'm trying to convert some Visual FoxPro applications to Access/VBA, and I find that I'm spoiled to some of the old xbase code. For example, VFP has a recno() function. I realize that Access does not have this for practical reasons, but unfortunately, I still need it.

My data sitch is this: I'm an engineer for a state DOT and deal with large amounts of pavement data. We number our overlay jobs in ascending data order. In their infinite wisdom, the original database designers (in a custom-built Sybase platform) would not let us have the overlay number, saying that we can order it by date. But people like and want the overlay number on reports.

I queried and ordered the pvmt_rehab table in desending order by date, but I can't get the overlay/rehab # in there. If I use the RunningSum method, but it will only number them top-down, and I want them numbered bottom-up. This is to match how the pavement structure is built--first overlay on bottom, second overlay next, third overlay on top, etc.

In VFP I could SCAN. . . ENDSCAN through the table, pull the overlays out for each pavement section into a cursor, order them by date, assign the recno() as the overlay number, then insert the overlay number into the main table. Crude but effective.

How can I do this in VBA/Access? I've searched but haven't come up with anything. Any other VFP folks out there that made the transition from the easy xbase code to VBA? I don't write a lot of full-blown applications, but mainly crunch complex data and do some somewhat complex reporting. VFP is wonderful for the data analysis but not so much for the reporting, so I was hoping Access would do the job.

Any help is appreciated.

 

[namliam]

I think the scan endscan is comparable to
Do While
Loop

Use a DAO.Recordset as your "cursor" which opens and orders your pavement records
Then edit the overlay numbers... Much the same way I guess...

If you search on DAO.Recordset you will find many examples on this forum, see where you can get from there. Post any questions you have here.

 

[cgdrake]

Thanks for the insight. So I would make a recordset, loop through it, then how would I update the original table? VFP would let me hold memory variables then I could do UPDATE SQL or INSERT to update the original table. Would I need two recordsets--one for the distinct pvmt_analysis_section_id to loop through, then a second one for the overlays for each analysis section?

 

[namliam]

I dont know how your data works so I cannot tell you how to do this...

Make sure to use DAO not ADO.... (IMHO) have a search on the forum and/or in the help to find out how to do Adds and Updates on recordsets or how to find columns.
Nah... Let me be a bit liberal
Add:
rs.Addnew
...
rs.Update

Edit/Update
rs.Edit
....
rs.Update

To assign a field that is allready in your table
rs!Fieldname = txtVariable

 

[cgdrake]

The data is a one-to-many relationship, one pavement section to many overlay projects (though some pavements are new construction only with no overlays). So I would need one recordset for the pavement section table, then I'd loop through that, have another recordset for pulling out the overlays for the particular section where the record pointer is at that time. Then I suppose I'd run the rs.Update to the overlay project table.

The backwards mailman delivers--thanks for the advice! I will see what I can do with it.

 

[namliam]

Good luck

Just a small reminder tho, you have to do addnew/update edit/update combo PER change and PER recordset!