last thing

[Jimmy Warpup]

Thanks for your patience. Now the database warns me, but it doesn't delete
the redundant entry. an example may be the best way to illustrate

If the word donner (french for "to give") is already in the database and I
try to re-enter it a nice little message box comes up and says "that word
donner has already been entered."
but then it populates "French_word" field in the form with the word "Donner"
and moves to the next field, English_Translation. I want it to delete the
entery and remain in the "french_word" field or better yet show the origial
entry for donner.

here's the code:

Private Sub French_Word_BeforeUpdate(Cancel As Integer)

A = IsNull(DLookup("[French_Word]", "[Words]", "[French_Word] = """ &
Me![French_Word] & """"))

Select Case A
Case False
MsgBox "The word " & Me!French_Word & " has already been
entered."
Exit Sub
Case Else
Exit Sub
End Select

End Sub

again, i'm over my head. I don't do this very often, any help would be
appriciated?

thx,

Jimmy

 

[Marshall Barton]

Thanks for your patience. Now the database warns me, but it doesn't delete
the redundant entry. an example may be the best way to illustrate

If the word donner (french for "to give") is already in the database and I
try to re-enter it a nice little message box comes up and says "that word
donner has already been entered."
but then it populates "French_word" field in the form with the word "Donner"
and moves to the next field, English_Translation. I want it to delete the
entery and remain in the "french_word" field or better yet show the origial
entry for donner.

here's the code:

Private Sub French_Word_BeforeUpdate(Cancel As Integer)

A = IsNull(DLookup("[French_Word]", "[Words]", "[French_Word] = """ &
Me![French_Word] & """"))

Select Case A
Case False
MsgBox "The word " & Me!French_Word & " has already been
entered."
Exit Sub
Case Else
Exit Sub
End Select

End Sub

FYI, many folks will just ignore your posts because they are
spread out over many threads and do not maintain any
continuity of progress. Please reply to your existing
thread instead of posting as if everything is a new
question. I wasted a lot of time trying to find your other
threads just trying to find some background information that
might have been posted previously.

As far as I can tell, you have never said what the form's
record source query looks like so I have no idea whether you
need to find the specified word in the form's recordset or
modify the form's record source SQL statement or whatever it
takes to display the exiting record.

Without that background info, I suggest that your code
should look more like:


Private Sub French_Word_BeforeUpdate(Cancel As Integer)
Dim A As Variant
Dim strFrWord As String
A = IsNull(DLookup("[French_Word]", "[Words]", _
"[French_Word] = """ &Me![French_Word] & """"))

If Not A Then
MsgBox "The word " & Me!French_Word & " has already been
entered."
strFrWord = Me.French_Word
Me.French_Word.Undo
Cancel = True
' locate/load existing record using strFrWord
. . .
End If

End Sub

 

[Mike Painter]

Place an index on the field, something you want anyway, and don't allow
duplicates.
Access will not make a duplicate entry. If you want a pretty warning, you
can intercept the one Access gives automatically.