Find next open number in table and open form on it

[Beast777]

I have a table of PC numbers PC/0001 - PC/9999. as a PC becomes obsolete the information on that PC is removed and the only record is the PC number. I need to click on an add pc button that will open up the next PC number that has no info. If having a PC number with no other info is a problem, I can remove all of those numbers. So if PC/0001, PC/0002 have info on them and PC/0003 is the next available one with no info, I would like the form to open PC/0003 so the user can input its info. Any help would be appreciated.

Beast

 

[afloyd]

you will need to create a function that uses recordset and do a loop to check for NULL values in the fields. For instance:

Dim rst as Recordset

rst = "some SQL string" that returns the fields you want to check for Null

rst.movelast

rst.movefirst (this populates the recordset)

DO

IF IS NULL <rst.field> Then

Use this value

exit loop

Else

rst.movenext

Loop Until rst.eof

please check the syntax, I was doing this without my references...


HTH

-Al

 

[Dave Eyley]

I did a Helpdesk db that required a new number each time so I created a table called 'LastRef'. In it was stored the last used number.
When a form calls for a new number do a DLookUp like -

Dim LastRefNo As Integer (could have been a string)

Since there's only 1 record in thew tablethere's no searching to do and it has to find the right value.

Last RefNo=DLookUp("[LastRefNo]","[LastRef]")
Last RefNo=LastRefNo+1

The LastRefNo from the table could be a field on the form with its visible property set to false so that when you've processed the data you could update the table Last RefNo from the field.

It worked for me...

 

[afloyd]

Dave's solution is much better than mine. Thanks Dave. I will file that away in my little box of tricks.