Function for data of the previous record

[bitbuster]

Hello,

I want to create a function in VBA to display the value of the previous record of a field (except when it is the first record, offcourse). How can I do this so the function can be used in a query (via expression builder) and doesn't take too long to calculate (so don't let the function run to the entire dataset for each record)?

An example might make it clear what I mean:

Query1
Field1 Expression (function to be created)

5       0
6       5
9       6
10      9
4       10

Thanks in advance

 

[Darrylles]

Hi bit,

Have a look at 'bookmark' in Access or VB help.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[bitbuster]

Darrylle,

Bookmarks are good when you use a form. I want a function that can be called from the expressionbuilder and can be used in a query. This query will be used for further calculations.

I found something else but it requires creating a new field in the table and filling it up via ADO or DAO by using the 'movenext' and 'moveprevious' options. It works fine and is useful for a fixed table, but it is not as flexible as a function.

Regards,

Bit

 

[Darrylles]

Hi,

You use ADO or DAO to create VB code when referring to multiple records. Functions are created using VB code.

You will HAVE to either permanently record the record number (via an additional field) OR retrieve the whole recordset.

Place the code in a module as a global function, then call it from within your query.

When you talked about 'previous record', I assumed you meant via a form because you said 'display'.

I don't understand your comparison between 'function' and 'fixed table', as you want a function to provide previous record info. from a fixed table.

Regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[bitbuster]

Darrylle,

What I meant is that my solution requires creating an extra field in the table where you want to apply the function. This way of working is ok when that table is the only one where you apply the function. That's what I meant with a 'fixed table'.
The disadvantage is that you have to create this extra field in every table where you want to use the function and that is not always possible (e.g. when linking to an SQL database where I only have read access).
Another disadvantage is that running through the entire recordset for each record of the recordset slows down the calculation too much in large databases.

What I would like to do is have a function that does the following:

Is there a record before me?
    If no: the result is 0
    If Yes: goto the previous record, and return the value of the specified field as result of the function.

Regards,

Bit

 

[Darrylles]

Hi,

Possibly you could create a VBA function that uses a duplicate query of the one that calls it - but THAT query has some filter so that it returns only records either side of the current one?

This sounds logically possible, but it really depends on ordered fields etc.

e.g. &quot;SELECT * FROM mytable WHERE pk < &quot; & 'varPK' & &quot;;&quot;
(simplified of course)
varPK being the current query record's pk value.

This VBA would return true if a record was retrieved.

Can't think of any other logical way of doing this.

Regards,

Darrylle





&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk