Hi!
Did know what subject to put but the issue is this:

I would like to find from a field (strReceiptNumber) the Numbers which have not been used between the last and first values arranged ascending order. Then save them in a table which will be loaded on a subform datasheet.

More Explanations:::
Say i have receipt numbers 1,2,3,4........to 100, then there were receipts which were cancelled or not used on the receipt book, therefore between the 1-100 receipt numbers some are missing from the records and i need to use some code to logically find out which receipt numbers were used.

My thought::
What if i get using NZ the highest receipt number used(which i can) in table say tblYY, Then use some code(through your help) to create receipt numbers from 1 to the figure i got using NZ and save in a table say tblXX, Then use some code (through your help) to delete receipt numbers from tblXX which match a receipt number from tblYY THEN I REMAIN WITH RECEIPT NUMBERS WHICH HAVE NOT BEEN USED IN tblXX.

Now the code please....!!

Regards,
Joseph

My my... the only problem with this idea of re-using skipped numbers is the longer the application is in use, the longer this process will take place. Imagine what it will be like if you have 100k receipts in your database, and your program has to read through all 100k of those records to find the 5 that are missing?

That aside, it's not my place to tell you that whoever is giving you your specs may need a knock in the head. Anyway, there are several ways I'd approach the problem, depending upon how persistent I want the results. (i.e. How long do I need to keep this list of skipped numbers)

1. No persistence:
A. Define a list box, and set the row source type to value list
B. Define a recordset returning only the receipt number, ordered by that receipt number.
C. Declare a variable called "lngLastNum", and set its value to 0
D. Set up a do loop running through all the records.
E. In each iteration of the loop, compare the current receipt number against lnglastnum. If the difference is only 1, add 1 to lnglastnum and go to the next record. If not, for each value starting at lnglastnum+1, stopping at the current receipt number -1, add the integer value as a new item in my list box.
F. Close the recordset.
G. Final result - in my list box, I will have a list of all the skipped receipt numbers.

2. Persistence:
In this case, I want to keep these numbers in a table.
The process is the same as above, but there are a couple differences.
First, I need a table to store these numbers in. Prior to running the routine, I need to clear the table.
With each iteration, instead of adding it to the list box, I insert it as a record in the table.

Now, the persistence model doesn't makes sense to do this during a form open, especially in a multi-user environment. If you are keeping the table, you should only run this as a daily (or weekly) maintenance process.

I hope this helps.

- Sligo

Let me go with the PERSISTENCE MODEL done weekly as a maintenance process. Which sometimes may even not be done. Later it would be abandoned when records would be too high.

Now the code to loop and increment the "lnglastNum" then save the data in a single field table.


Regards,
Joseph

Go for it! Write your code and post it here. I'm interested to see what you come up with.

- Sligo

This is what i can come up with but i have not tested since the database am developing is somewhere i usually go only on Fridays.
[/code]
Dim rcd as DAO.Recordset
Dim dbs as CurrentDB
Dim rst as DAO.Recordset
Dim lngLastNum as long
lngLastNum=0

Set rcd= dbs.OpenRecordset("SELECT tblMembers.strReceiptNo FROM tblMembers ORDER BY tblMembers.strReceiptNo", dbForwardOnly)

rcd.MoveFirst
Do While NOT rcd.EOF
if rcd!strReceiptNo-lngLastNum=1 then
lngLastNum=lngLastNum+1
else
Set rst= dbs.OpenRecordset("tblUnusedReceiptNumbers", dbOpenDynaset)
rst.addnew
With rst
!strUnusedReceiptNumber=rcd!strReceiptNo
End With
End If
rcd.movenext
Loop
[/code]


Regards,
Joseph

Anybody???