Is there a way to stop the creation of a new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I am trying to do is that a user will actually choose a value in a field
called "WON", once that value is chosen in an existing record, I do not want
the user to be able to have the ability to create another record. If someone
knows a way to do this I would greatly appreciate it.

Thanks in advance
 
John said:
What I am trying to do is that a user will actually choose a value in
a field called "WON", once that value is chosen in an existing
record, I do not want the user to be able to have the ability to
create another record. If someone knows a way to do this I would
greatly appreciate it.

Thanks in advance
Click to expand...

There are several ways you might go about this. The simplest I can
think of, though not necessarily the most efficient nor the most
user-friendly, is to use code in the form's BeforeInsert event to see if
a record already exists that has that value in the field, and cancel the
event if so. Along these lines:

'----- start of example code -----
Private Sub Form_BeforeInsert(Cancel As Integer)

If Not IsNull(DLookup("ID", "MyTable", "[MyField] = 'WON'")) Then
MsgBox "You can't add a record now -- the contest is over!"
Cancel = True
End If

End Sub
'----- end of example code -----

The main flaw in this approach is that it allows users to move to a new
record even if they won't actually be able to add one. They don't find
out they can't until they start to enter something.

More complicated, but a bit more user-friendly, is to check for the
ability to add records in several places: the Open or Load event, the
AfterUpdate event, and the AfterDelConfirm event. In each case, you
would check to see if it is okay to add a record or not (by using the
DLookup logic above or its equivalent), and set the form's
AllowAdditions property according to what you find out.
 
Back
Top