Fastest way to add a value from the next record to the current record

[Remster]

The following is a greatly simplified account of the situation.

I have a table (FruitTable) and a read-only continuous form (FruitForm). Figure 1 below shows FruitTable. Figure 2 below shows the record source I want for FruitForm. What's the fastest way to create the record source from the table? I've tried a query with a SELECT Top 1 subquery, but that's far too slow, as are any of the functions I've come up with so far. (Sorry about the huge space between the two tables. I can't work out how to remove it.)

Figure 1: FruitTable

Fruit

Strawberries

Oranges

Bananas

Apples

Plums

Figure 2: Desired record source for FruitForm

Fruit	NextFruit
Strawberries	Oranges
Oranges	Bananas
Bananas	Apples
Apples	Plums
Plums

[isladogs]

Here's one approach involving two queries which are then combined as a union query.

Query1 - this gets all the records except the last where there is no NextFruit:

SELECT Table1.ID, Table1.Fruit, Table1_1.Fruit AS NextFruit
FROM Table1, Table1 AS Table1_1
WHERE (((Table1_1.ID)=[Table1].[ID]+1));

Query2 - this gets the last record using an unmatched query

SELECT Table1.ID, Table1.Fruit, '' AS NextFruit
FROM Table1 LEFT JOIN Query1 ON Table1.ID = Query1.ID
WHERE (((Query1.ID) Is Null));

Now union both to get all records

SELECT Table1.ID, Table1.Fruit, Table1_1.Fruit AS NextFruit
FROM Table1, Table1 AS Table1_1
WHERE (((Table1_1.ID)=[Table1].[ID]+1))
UNION SELECT Table1.ID, Table1.Fruit, '' AS NextFruit
FROM Table1 LEFT JOIN Query1 ON Table1.ID = Query1.ID
WHERE (((Query1.ID) Is Null));

See attached

[Remster]

Thanks for that. The only problem is that in my actual situation I can't assign consecutive, or even ascending or descending, ID numbers to the records. I'd have mentioned that if I'd thought it would be relevant - I didn't mean to waste your time.

I thought there might be some way in VBA of appending a column to the columns in the query, where the appended column was populated by navigating through the original query's recordset. I created a function that did something like this, but it made scrolling through the records in the form painfully slow.

[isladogs]

What type of field do you have for a primary key field?

[CJ_London]

think perhaps worth asking why you are doing this - it doesn't feel like a real world situation.

The problem you have is with databases there is no concept of first/last previous/next without setting an order. And if that is the only field you have then the only order you can specify is alphabetic - which is not the order of your data.

[Remster]

OK, perhaps it's only fair to show my hand.

My customer (internal!) wants a continuous form that keeps the current record at the top and highlights it. I've worked out how to do that, but I haven't been able to get rid of the annoying flickering caused by conditional formatting. The solution I've come up with is to display the current record in the form's Header section, and to set the permanent background colour of the Header section to the highlight colour. However, to avoid displaying the information in the current record twice (once in the Header section, once in the Detail section), I've been looking for a way to link the primary key of the current record, which isn't itself displayed, to the meaningful information in the next record.

As it happens, I've come up with a working function this afternoon, and I'd be very grateful for any constructive criticism, particularly about how it might be simplified and how I might be storing up memory problems for myself (although bear in mind that the maximum number of records will be about 1,000). I've attached what I've come up with, applied to fruit. In my non-fruit version, several of the values other than CurrentID are passed as arguments from the query, and the records have already been sorted alphabetically in a subquery.

[isladogs]

Well its certainly simpler than my 3 query solution but it still depends on an ID field which you implied wasn't possible in your database.
Have you tested scaling up by running this on a larger table with many records?

[Remster]

My database has an ID field, but it's not incremental or sequential. My actual situation is more like the attached. I might have given the impression that the order of fruits in my original post was important; it wasn't.

Edit: Yes, I've tried it with a subquery of 901 records, plus a third column.

[Remster]

What I'm less clear on than anything else is whether I need to do anything in the way of deleting the array once I've loaded the continuous form that has the next-value query as its record source.

[CJ_London]

thanks for the explanation, but still struggling to understand how it is supposed to work - what makes the current record? selecting from the rest? - which requires adding back the old current record and removing the new current record? something else? What happens if the user selects the the 5th record? What happens to the first 4 records?

[Remster]

I'll attach a demo in due course. It requires something I've been working on in relation to another thread I started recently, and I haven't had time to put it all together.