Temporarily missing record

[Guest]

I have posted earlier questions about a database in which the PK is assigned
according to code, so that the first record is RPT 05-01, then RPT 05-02,
etc. I have code in a called procedure (named AssignNumber) that assigns
this number. I call the procedure from the form's Current event when it is a
new record. This lets the user see the identifying number from the beginning.
In order to allow for multiple users starting a record at the same time, as
soon as the PK value is inserted in the text box bound to the PK field, I
copy it to an unbound text box that the user sees, and delete it from the
bound text box (all in the On Current event if it is a new record). When it
is time to save the record, the called procedure (AssignNumber) assigns a
value to the PK field, and compares that value to the unbound control. If
they are different the user is notified.
It all seems to work, but there is one thing I would like to clean up: If
User1 starts a record, then before it is saved User2 starts a record, both
will see the same identifying number (say RPT 05-04) at first. User1
completes the record, and it is saved as 05-04. User2 then completes the
record, but since 05-04 is already taken, the record is assigned the value
05-05, and User2 is notified of this by way of a message box. Everything
works as I would hope, except that neither user can see the other record at
first. For instance, User2 will see 05-05, but when navigating backward
through the records will next encounter 05-03. 05-04 (which was the record
created by User1, and which by now has been saved) cannot be seen. If User2
closes and reopens the database, 05-04 will show up in its proper place.
How can the new record created by one user be seen by the other user without
closing and reopening the database? I had thought Refresh might do it, but
if so I can't discover when to run that bit of code. This is not all that
big a deal, but it is bugging me.

 

[MacDermott]

I think Requery is what you need.

 

[Guest]

Thanks for the reply. It is indeed what I needed, but it took me a while to
figure out where to put it. If I put it in the form's After Update event
then it kept shooting me back to the first record. Similarly if I put it
into the Previous Record command button (I prefer to add my own navigation
buttons, since they can be labeled and generally made easier for users to
operate than the built-in buttons). I finally decided that the only place I
really need it is in the New Record command button, where it seems to prevent
the message that I can't go to the record in a case where two users are in
the database at the same time. The Next Record button is disabled at a new
record, so no need to put it there.