CODEWITHSUNDEEP
ms-accessvbams-access-2007lookup
Manpower Development Division
Manpower Development Division

Reputation: 11

Dlookup Function in MS Access 2007 vba

I have a textbox that has a name ISCO68Code and ISCO68Title both are texts.

When I encode in ISCO68Code, a caption must appear on ISCO68Title that corresponds in my other table, libISCO1968.

Below is my Code Builder:

Private Sub ISCO68Code_AfterUpdate()

ISCO68Title = DLookup("ISCO68Code", "libISCO1968", "[ISCO68Code]=" & ISCO68Code&)

End Sub

Sadly, I got an error message:

Compile error: Type-declaration character does not match declared data type.

Any help will do.

Upvotes: 1

Views: 281

Answers (2)

Gustav
Gustav

Reputation: 55816

You need the quotes telling that the variable is text:

Private Sub ISCO68Code_AfterUpdate()

    ISCO68Title = Nz(DLookup("ISCO68Code", "libISCO1968", "[ISCO68Code]='" & ISCO68Code & "'"))
    ' If the control is a Label, set its Caption:    
    ' ISCO68Title.Caption = Nz(DLookup("ISCO68Code", "libISCO1968", "[ISCO68Code]='" & ISCO68Code & "'"))

End Sub

But your function doesn't make much sense, as DLookup will return the same ISCO68Code as you look up ... except if you just will check if it exists.

Upvotes: 1

R3uK
R3uK

Reputation: 14537

Try this function :

Private Function ISCO68Code_AfterUpdate(ByVal ISCO68Code As String) As Variant

ISCO68Code_AfterUpdate = DLookup("ISCO68Code", "libISCO1968", "[ISCO68Code] = " & ISCO68Code)

End Function 

Private Sub ISCO68Code_AfterUpdate_test()
Dim ISCO68Title As Variant

ISCO68Title = ISCO68Code_AfterUpdate(ISCO68Code)

'MsgBox ISCO68Title
Debug.Print ISCO68Title

End Sub

Upvotes: 0

Related Questions