DLookup problems 1

[bill420]

I am attempting to use DLookup to retrieve the file number (primary key) of any and all previous records that the hull numbers match in a table named “Vessel”. I have started with the following code, and created a new record with duplicate hull number to test with.

Private Sub Hull_Number_AfterUpdate()
Dim temp As Variant
temp = DLookup("[File_Number]", "[Vessel]", "[Hull_Number] = Forms![Hull_Number]")
MsgBox temp
End Sub

However, the result is always null even though the duplicate record exists in the table.
Having only a basic knowledge of Access, I need a little help in understanding the best way to approach this problem as I will need to check other fields in the table for duplicate entries as well.
Thanks
Bill

 

[UnicornRainbow]

temp = DLookup("[File_Number]", "[Vessel]", "[Hull_Number] = '" & Forms![Hull_Number] & "'")

 

[bill420]

Thanks UnicornRainbow for your fast reply, however I am now receiving a compile error, type mismatch, highlighting '" & Forms![Hull_Number] & "' in the code.
Not sure why.
Bill

 

[PHV]

If File_Number is defined as numeric in Vessel:
temp = DLookup("File_Number", "Vessel", "Hull_Number=" & Me!Hull_Number)

If defined as text:
temp = DLookup("File_Number", "Vessel", "Hull_Number='" & Me!Hull_Number & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bill420]

Thank you so much PHV, File_Number is defined as text and your solution worked great. I have an additional question, if there is more than one duplicate, what would be the best method for checking for all of them? This pulled up the first duplicate within the table but there are more.
Thanks again for all your help.
Bill

 

[PHV]

intCountOfDup = DCount("*", "Vessel", "Hull_Number='" & Me!Hull_Number & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886