Return value in field.....please help

[rupes_mahal]

hi...

i have 3 fields...

[ReferenceNo]
[PremiseCode]
[Supercedes]

I have to work out [Supercedes] field.
The code I have within [Supercedes] field is.....

Dim varA as Variant

varA = Me!PremiseCode

If(IsNull(varA)) then
Supercedes = 0
Else
Supercedes = Dlookup("[ReferenceNo]",
"PremRegisterTBL", "[Me!Premisecode] = [PremiseCode]")

End If

Me!Supercedes = Supercedes

End Sub

This code does not work.

What im basically trying to do is this:

Every record has an unqiue ReferenceNo. The PremiseCode could be the same in different records. Supercedes field should display a ReferenceNo from the [ReferenceNo] field when the PremiseCode entered in the present record = to a PremiseCode entered in the past records.

eg.
[ReferenceNo], [PremiseCode], [Supercedes]
M1111, AO1,
M1112, U01, M1100
M1113, A01, M1111

(commas used to split columns)

In last record Supercedes = M1111 because the PermiseCode entered A01 matches premisecode entered in record 1. SO Supercedes = M1111

I hope you guys understand what I am trying to do.

Thankyou in advance

ruby

[This message has been edited by rupes_mahal (edited 09-14-2001).]

 

[rupes_mahal]

Pat...

thankyou for gettign back to me..

You say that i should consider using DMAx(), could you please help me out here, as to how to use it within my problem...

thankyou in advance

Ruby