dlookup looks to the wrong place

[Jimmy Warpup]

this line
DLookup("[French_Word]", "[words]", "[French_Word] = """ & Me![French_Word]
& """")
outputs any word in the "french_word" field of the "words" table where that
value is the same as the value entered into the form.

this line
DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")

refrences the "English translation" field on the "words" form. I need the
field from the table.

I don't want to re-write the code, it's a learning thing. I only want to
know how to fix the second line to refrence the table not the field.

here's the context.

Private Sub French_word_BeforeUpdate(Cancel As Integer)
'B = DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")
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.
It means " & B
Cancel = True
Exit Sub
Case Else
Exit Sub
End Select

End Sub

 

[John W. Vinson]

this line
DLookup("[French_Word]", "[words]", "[French_Word] = """ & Me![French_Word]
& """")
outputs any word in the "french_word" field of the "words" table where that
value is the same as the value entered into the form.
Exactly.

this line
DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")

refrences the "English translation" field on the "words" form. I need the
field from the table.

No. It doesn't.

The first argument to DLookUp is the name of the field that's being looked up.
The second argument is the name of a Table or Query which contains that field.
The third argument is an optional text string which evaluates to a valid SQL
WHERE clause without the WHERE, identifying which record in the table or query
should be looked up.

A DLookUp will not (cannot!) reference a control on a Form. It can only look
in a table.

I don't want to re-write the code, it's a learning thing. I only want to
know how to fix the second line to refrence the table not the field.

Not sure what you're seeing... but that's what it's doing, if it's doing
anything at all.

here's the context.

Private Sub French_word_BeforeUpdate(Cancel As Integer)
'B = DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")
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.
It means " & B
Cancel = True
Exit Sub
Case Else
Exit Sub
End Select

End Sub

This code has the dlookup of English_Translation commented out (the line
begins with a single apostrophe, the comment character), so it won't do
anything at all with it.


Try simplifying the logic a bit:

Private Sub French_word_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[French_Word]", "[words]", "[French_Word] = """ _
& Me![French_Word] & """")) Then
MsgBox "The word " & Me!French_Word & _
" has already been entered. It means " & _
DLookup("[English_Translation]", "[words]", "[French_Word] = """ & _
Me![French_Word] & """")
Cancel = True
End If
End Sub

 

[Jimmy Warpup]

quite right.

I was using a word that already existed in the db to test with, donner, to
give. but i had put that word in for testing in the first place and hadn't
bothered to put in a translation... hence the null value... so when I put
something into the field in the form i'm guessing it populated the field in
the db, it was the first record that met the criteria and that's what
dlookup was returning.

so basically it was an ID - 10 t error... sorry to waste your time....

thx John, you were a real help.

here's what I ended up with if you're curious.

Private Sub French_word_BeforeUpdate(Cancel As Integer)

Dim A As String
Dim B As String
Dim C As String

A = IsNull(DLookup("[French_Word]", "[Dictionary]", "[French_Word] = """ &
Me![French_Word] & """"))
'true if there is no entry for french word that matches curent entry
B = IsNull(DLookup("[thisone]", "[Dictionary]", "[French_Word] = """ &
Me![French_Word] & """"))
'true if an english translation exists

Select Case A
Case False
'if there is a redundant value
Select Case B
Case False
' if a definition exists
C = DLookup("[thisone]", "Dictionary", "[French_Word] =
""" & Me![French_Word] & """")
MsgBox "The word " & Me!French_Word & " has already been
entered. It means " & "'" & C & "'"
Cancel = True
Exit Sub

Case True
MsgBox "The word " & Me!French_Word & " has already been
entered. No definition is recorded."
Cancel = True
Exit Sub
Case Else
MsgBox "Invalid Entry."
Cancel = True
Exit Sub
End Select
Case Else
Exit Sub
End Select

End Sub

this line
DLookup("[French_Word]", "[words]", "[French_Word] = """ &
Me![French_Word]
& """")
outputs any word in the "french_word" field of the "words" table where
that
value is the same as the value entered into the form.
Exactly.

this line
DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")

refrences the "English translation" field on the "words" form. I need the
field from the table.

No. It doesn't.

The first argument to DLookUp is the name of the field that's being looked
up.
The second argument is the name of a Table or Query which contains that
field.
The third argument is an optional text string which evaluates to a valid
SQL
WHERE clause without the WHERE, identifying which record in the table or
query
should be looked up.

A DLookUp will not (cannot!) reference a control on a Form. It can only
look
in a table.

I don't want to re-write the code, it's a learning thing. I only want to
know how to fix the second line to refrence the table not the field.

Not sure what you're seeing... but that's what it's doing, if it's doing
anything at all.

here's the context.

Private Sub French_word_BeforeUpdate(Cancel As Integer)
'B = DLookup("[English_Translation]", "[words]", "[French_Word] = """ &
Me![French_Word] & """")
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.
It means " & B
Cancel = True
Exit Sub
Case Else
Exit Sub
End Select

End Sub

This code has the dlookup of English_Translation commented out (the line
begins with a single apostrophe, the comment character), so it won't do
anything at all with it.


Try simplifying the logic a bit:

Private Sub French_word_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[French_Word]", "[words]", "[French_Word] = """ _
& Me![French_Word] & """")) Then
MsgBox "The word " & Me!French_Word & _
" has already been entered. It means " & _
DLookup("[English_Translation]", "[words]", "[French_Word] = """ & _
Me![French_Word] & """")
Cancel = True
End If
End Sub