Dlookup Syntax

[CharlieT302]

Hi,

The formula below is designed to see if the value of a form field exists in a table. The Field in question (Care_ID) is a text field in the underlying table. The name of the form control that references it has the same name as the field and there are no other fields with a conflicting name.

If DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='" & Me.[Care_ID] & "'")

The rest of the formula simple deletes the record from the form as long as the value is not found.

I receive the error:
Run Time 13
Type Mismatch

It "IS" text field. Why the Type Mismatch?

Thanks

 

[dhookom]

What's the remainder of at least the one line of code?

If DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='" & Me.[Care_ID] & "'") [COLOR=#A40000][highlight #FCE94F]...[/highlight][/color]

What do you get if you type this into the debug window (press Ctrl+G):

?DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='SomeValue'")

What do you see when you set a breakpoint in the code and step through it to the line?

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

If Me.Del_Mgr.Value = 0 Then
GoTo Cont_Save
Else
If DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='" & Me.[Care_ID] & "'") Then

Me.Del_Mgr.Value = 0
MsgBox "Some Message" _
GoTo Exit_sub
Else
GoTo Cont_Save
End If
End If


Cont_Save:
DoCmd.RunCommand acCmdSaveRecord
GoTo Exit_sub

err_Del:
MsgBox Err.Number & " " & Err.Description
GoTo Exit_sub

Exit_sub:

Notes: The Me.Del_Mgr.Value is a simple check box. THe code only is run if the box is checked (-1). When checked, the formula looks for the record's field value in another table. IF it exists, it can not be marked for deletion and the user is informed of this via the Msgbox. The actual deletion is a separate process.

The table in which the dlookup is searching may have many occurrences of the searched value, but the field IS a text field.

 

[CharlieT302]

By the way, the answer to your question:

When I run the line:
?DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='SomeValue'")

It comes up with a Null value, which I do not understand because I know the value displayed on the form exists in the specified table.

 

[CharlieT302]

Correction,

The formula in the Immediate Window did not come up Null. I just ran the formula again using a known value and that value did, in fact, come up(the first time I ran it, I was in such a hurry I left the 'someValue' in there by mistake).

 

[CharlieT302]

Never mind. I resolved it...most of it.

I ran a message box to pull the field value from the form as well as search for it in the table. Both message boxes located and displayed the value appropriately.

I then modified the formula:
If Not IsNull(DLookup("[Care_ID]", "tbl_Manager", "[Care_ID]='" & Me.[Care_ID] & "'")) ...

It works fine. The Type Mismatch error must be a misnomer. Sometimes the displayed error messages are not what they seem.

Anyway,

Thanks

 

[dhookom]

Your dlookup should have been followed by = ... or wrapped in IsNull(). As it was, your code was the equivalent of

If "some text" Then

Duane
Hook'D on Access
MS Access MVP