Unique entry

[Chris]

I pasted this:

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me![Symbol ID].Value, "") & "'") > 0 Then
MsgBox "The value already exists!"
Cancel = True
End If
End Sub


Got this message:

---------------------------
Microsoft Office Access
---------------------------
The value already exists!
---------------------------
OK
---------------------------

And have to close out saying "Microsoft access may have encountered an
error while trying to save a record. If you close this object now the
changes you made will be lost. So you want to close the database
object anyways?"

Chris

 

[Ken Snell [MVP]]

Sounds like you're trying to close the form after you enter a value but
before you moved the focus from the control. Your very early post said you
were tabbing from the control. Are you closing the form using the "red X"
instead of tabbing from the control?

It will be helpful if you describe the sequence of things that you want to
do on this form.

 

[Chris]

If my last symbol keyed is a good one it all works fine, but if the
last symbol I key is a duplicate I am stuck!

I key the symbol - hit tab & get this message:

---------------------------
Microsoft Office Access
---------------------------
The value already exists!
---------------------------
OK
---------------------------

I hit ok. The symbol I typed is still there. I delete it and then
close out because I am done. It is my last entry & it does not want me
to exit because it knows I started a new record & did not complete it.

Chris

 

[Chris]

See above first.

Possibly this is just the way I will have to exit - & live with it -
since it knows this is now an uncompleted form?

Chris

 

[Ken Snell [MVP]]

OK - let's add one step to your code -- the Undo method for the bound
control:.

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me![Symbol ID].Value, "") & "'") > 0 Then
MsgBox "The value already exists!"
Cancel = True
Me![Symbol ID].Undo
End If
End Sub

That will eliminate the problem of you having to delete the entry that you'd
made.

Now, if you want to cancel the entry of the new record entirely, then we can
add one more step to undo the entire new record:

Private Sub Symbol_ID_BeforeUpdate(Cancel As Integer)
If DCount("*", "Borrowing", _
"[Symbol ID]='" & Nz(Me![Symbol ID].Value, "") & "'") > 0 Then
MsgBox "The value already exists!"
Cancel = True
Me![Symbol ID].Undo
Me.Undo
End If
End Sub

 

[Chris]

You are an absolute genius!!! It worked and did exactly what I need it
to!! Thank you so much for your help!

Chris Agostino

 

[Ken Snell [MVP]]

You're welcome... glad it worked ok.