Accessing a specific record (1 Viewer)

[Dazza666]

Hi All,

I was wondering how to access a specific record where more than one dataset are associated with a record. e.g.

A landlord keeping a record of tenants has many tenants who exit his premises and return a couple of years later. The landlord has a client table and clientRecord table, the client table represents un changing information (name D.O.B)

the clientRecord table collects information regarding that current stay (rent collected, Date left)

if someone had say 5 seperate stays in vba how can I

a. Count the number of records (5)
b. reference the last, second from last and 1st records stored.

This process must be seamless to end users, so i can't introduce people to ID numbers etc, i need to do this programatically

thanks

 

[DJkarl]

The only way you can reference last / second to last etc... is if you have a date field or timestamp of somekind in your tables indicating when the records were added. If you don't have a date/time field to sort on there isn't really a way to indentify a sequence of records as they are stored randomly and can't be counted on to be in any specific order.

 

[Dazza666]

The only way you can reference last / second to last etc... is if you have a date field or timestamp of somekind in your tables indicating when the records were added. If you don't have a date/time field to sort on there isn't really a way to indentify a sequence of records as they are stored randomly and can't be counted on to be in any specific order.

hi thanks,

yeah I have a start date and end date record that can be used to order the data,

can you give me an example of how access these programatically,

I think the last record is easy, it's the one without an end date but not sure how to access the others in order without knowing specific dates?

 

[DJkarl]

hi thanks,

yeah I have a start date and end date record that can be used to order the data,

can you give me an example of how access these programatically,

I think the last record is easy, it's the one without an end date but not sure how to access the others in order without knowing specific dates?

I would use a SQL statement as the base for a recordset.

Dim rs as DAO.Recordset
 
Set rs = Currentdb.OpenRecordset("SELECT * FROM [yourtable] ORDER BY [DateField]")
 
Do something here with the recordset object
 
set rs = nothing