When record is not in list then go to next record

[JiTS]

Good morning!

I have 2 forms (frmRecordList and frmRecordDetails). The form frmRecordList contains 400+ records with a field 'Status' set on "Open".
Normally I choose a record in frmRecordList and frmRecordDetails shows the chosen record. In frmRecordDetails I am able to change a field 'Status' from "Open" into "Closed". When I close frmRecordDetails with the 'Status' "Open" frmRecordList pops up and the shows me the previous selected record at the top in the form. I use the following code which works perfectly:

txtID.SetFocus
DoCmd.FindRecord Me.OpenArgs

But if I close frmRecordDetails with the 'Status' "Closed" frmRecordList pops up with the first record at the top because the list only contains records with the 'Status' "Open".

My question:
If I close a record and it disappears from the list, how is it possible to choose automatically the next record with the 'Status' "Open". And how to handle if it was the last record?

 

[JiTS]

More details about my problem:

If the list in frmRecordList is from 1 to 200 (1, 2, 3, etc.) then my code (OpenArgs) works perfectly. The OpenArgs property contains the ID of the selected record.

In the picture (see attchment) you see a part of frmRecordList. As you can see is that not every ID is showed because they are already closed. Only ID's which are open will be showed. For example, if I choose ID '201', I change the status into "closed" and I go back to the list (frmRecordList) the 'cursor' goes to the top ID ('8') because ID '201' is out of the list. The cursor should go to the next available ID ('202').

My question:
If an ID is "closed" (and out of the list) how is it possible to go to the next available ID in the list?

 

[Banana]

Why not have your listbox base on a query retrieving only those that are open?

 

[JiTS]

Well I don't know... I just have choosen for this method (by using a continuos form).

I believe that this works but I can't trigger my problem.

Do you know the code to find the next record in a query without using FindRecord or FindNext?

 

[Banana]

But you don't have to. In your main form listbox, you only need to add code in the Afterupdate

Me.listbox.Requery

and set your listbox control source to the query returning only open records. When you return from the pop up form, it'll refresh itself with the latest query, eliminating the closed record from the list.

 

[JiTS]

Banana,

Sorry, but that's not the issue. The problem is to show the previous selected record in the list at the top. Or if the previous selected record is closed, the next one (which is open) should be on top of the list.

 

[Banana]

Ah, mea culpa.

I think this may give you the answer

 

[JiTS]

Hey Banana,

I think that's the solution... I've tried to apply it in my database but with no succes. Well, I am not a beginner in programming but the code which is used is a little too complicated for me. Maybe you can help me?