that didn't work

[Jimmy Warpup]

Hey,

I posted the following question:


I'm creating a database to help teach foreign language. I've made the form
and it's really cool, but if I input a redundant value it doesn't tell me
until the end of the form. there're like 50 items on the form, so it's a
big waste of time when an entry is recreated.

I'd like to make it so that when you tab out of the first field (called
"French_word") it checks that entry against the existing database. If it is
I want to display that entry. if it isn't i just want it to move on.

I'm in way over my head here. this whole project was a bit out of range for
me, this is just way out of my league. any help would be appreciated.

Jimmy

John Vincen was kind enough to offer the following code:

Private Sub French_word_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[French_word]", "[tablename]", _
"[French_word] = """ & Me![French_word] & """") Then
Cancel = True
MsgBox "The word " & Me!French_word & " already exists", vbokonly
End If
End Sub

It didn't work, i must have input it wrong, but i think it gave me the
information I needed but I'm not familiar enough with if then statemnts so i
decided to use case.

i came up with this code (it's a work in progress so it does some funny
things):

Private Sub French_word_BeforeUpdate(Cancel As Integer)

MsgBox "begining"
'troubleshooting step 1 make sure sub is being run at right time
MsgBox IsNull(DLookup("[French_Word]", "[Words]", "[French_word] = """ &
Me![French_Word] & """"))
'troubleshooting step 2 to see what the line returned
Select Case Me.French_Word
Case IsNull(DLookup("[French_word]", "[Words]", "[French_word] = """ &
Me![French_Word] & """")) = False
MsgBox "first case"
Exit Sub
Case IsNull(DLookup("[French_word]", "[Words]", "[French_word] = """ &
Me![French_Word] & """")) = True
MsgBox "not isnul"
Case Else
MsgBox "No workie workie " & Me!French_Word & " " &
DLookup("[French_word]", "[Words]", "[French_word] = """ & Me![French_Word]
& """")
Exit Sub
End Select
End Sub

Weather there is an existing record or not only the "else" case ever runs.
the second troubleshooting step returns false if there is a redundant record
and true if there is not.

also, i want it to delete the entry and go back to the "french_word" field
in the form when the msgbox is closed

any more help?

 

[Gina Whipp]

Jimmy,

Put John's code in the After_Update event.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm