Disclaimer
I’ve been reading related posts and websites for supportwhenever time has allowed during the last week. I’ve seen much talk of DMAX+1and using autonumber, and not using autonumber, and SQL, all of which has nothelped. Basically, if you’re kind enoughto reply imagine you’re doing so in lovely big colourful crayons in an outrageouslyoutsized font and with the sort of language that benefits the psychologicallysubnormal.Essentially, if you’rereplying and it feels like you’re expounding the bleeding obvious - then you’reprobably pitching it just right.
What I’m trying toachieve:
To automatically populate a form field called RefNo in atable called tblMain (yes, that’s right) with the next lowest number availablefrom a range which begins at 1700 and ends at 1799. As you might imagine frommy disclaimer, this field in this table sits within the most basic databaseknown to man.
However, sequences of these numbers, such as 1700:1710 and1780:1790 are unavailable (ie already allocated), as are isolated others suchas 1728, 1750 and 1777.
The end goal of this is therefore that when I populate thenext form, its RefNo field would automatically populate with: 1711 (as 1700 to1710 are already in use).
There is little risk of two people entering data at the sametime so please discount that from your thinking with whatever help you canoffer up. If this can’t be done by a novice like me. Would it be possible tocreate a second table with a list of these RefNos, maybe, and somehow transposethe next lowest one into my main table? Just a thought. No idea if that’sfeasible.
Thanks in advance.