Preventing duplicate data entrry

[FJM]

I have a data entry form where the next unique sequential number is automatically generated when the operator clicks onto and opens up a combo box. I want to prevent the record i.e the sequential number being duplicated. At the moment if two people access the same record at once, the first to complete wins the race and the loser receives the warning message that access is unable to save etc etc.
I have looked through similar threads and "get the feel" for what is needed but I cannot find suitable code for what I need.


Can anyone help me with this at all

[Missinglinq]

I suspect that you're assigning the number too early in the process of Record creation! In a multi-user environment, the best approach is to do this type of assignment is as the last thing in the Form_Before Update event. This fires during the last nanosecond before the Record is Saved. I've used this approach for years without ever having the problem you mention.

Linq ;0)>

[FJM]

I don't know if what I am about to say will make sense based on your reply.
The scenario I am trying to eliminate is this:=
- operator A opens up a new record and they are automatically taken to a combo so they can choose a report type. As soon as they click on an entry and hit the enter button the form then automatically inserts a record number via the Before Insert of the form using the following code:-
Private Sub Form_BeforeInsert(Cancel As Integer)
[Report No] = Nz(DMax("[Report No]", "[NCR Register 2000]") + 1, 1)
End Sub
- Operator B goes in at this point and following the same procedure can then populate many field before hitting the enter button which then tells them they cannot save the record because of a duplication error.
What I am in effect trying to achieve is when the first operator goes in, chooses the combo, populates it and is presented with the next sequential number , that the record is updated instantly so any further operators cannot waste their time by filling in fields before being told they cannot.
Hope that makes sense !!!!!
FJM

[Missinglinq]

The Form_BeforeInsert event is the earliest possible time in the creation of a New Record to do this task, and, as I said, it really needs to be in the Form_BeforeUpdate event, the last possible moment before saving the Record!

If you simply must do things in this fashion, you'll have to explicitly save the Record immediately after assigning the unique number:

Private Sub Form_BeforeInsert(Cancel As Integer)
 Me.[Report No] = Nz(DMax("[Report No]", "[NCR Register 2000]") + 1, 1)
 DoCmd.RunCommand acCmdSaveRecord
End Sub

Linq ;0)>

[FJM]

Marvelous !!!!!!!
Seems to work a treat- will know definitely tomorrow - if so, many,many thanks
Doesn't half help if you ask the right people things
FJM