That still does not satisfy Tara's requirement of making the
number available to the user during the process of data entry.
I fail to see the issue. Just because you collect the data to create
the record in an unbound form does not mean you don't show the user
the sequence number once you've created the full record from the
data entered in the unbound form.
That this precludes aborting
the insertion of a new row while maintaining sequentiality is the
fundamental paradox here which is impossible to obviate
satisfactorily as far as I can see. If you take that requirement
out of the equation then Roger's method handles things very simply
and reliably.
I don't understand why it wouldn't work. The requirements in the
original post are:
1. Each time a Registration # is entered it is to increase by 1 and
it needs to show the user the Registration # they are currently
working on (in a form).
2. Complicating matters is the possibility of multiple users
entering data at the same time.
Using an Autonumber that is created only after the initial
information is entered in the unbound ADD NEW form insures that you
don't have any abandoned sequence numbers.
Once the data for the stub record is collected, you insert the new
record and load the new record in your full data editing form.
Autonumbers are quite immune to multi-user problems (they weren't
before Jet 3.x), and since you're inserting you record via a SQL
INSERT, the amount of time the back end table is locked is as tiny
as possible with Jet.
Now, if you read #1 as saying that the user needs to see the next
registration number *before* the record is created, that's
different. I don't read #1 as implying that at all, and I also don't
see why it should be a requirement.
Certainly the way I do this is that I use the unbound form to nudge
the user into avoiding entering duplicates, since after entering the
stub data, I look up all records with similar data and present them
in a subform as possible duplicates (and providing a mechanism to
abandon the add and go to the correct existing record instead). That
list of possible dupes could display the registration numbers for
the existing records.
But the details would depend on what the requirements of the
application are. I don't see any reason that a lot of cases where a
sequence is needed could not be kept as clean as possible (i.e., no
lost Autonumbers because the record was abandoned before it was ever
saved) with the unbound ADD NEW RECORD form.
You have all been extremely helpful! Looks like I'll have to come up with a solution based upon the wonderful information you have given me and possibly some of my own creativeness. The former I'm sure will be useful, the latter...we'll see