Dlookup

[MartinO]

My database has table [MCR Table] with primary key field [REA Number] plus several other fields. When entering a new record using [MCR_Input_Form], the REA Number is the first thing entered and gives an error message if that value already exists in the MCR Table. Unfortunately this means that all fields are usually filled and the enter button pressed before the error message appears. This is frustrating and time consuming to the person inputting (my boss) who asked whether I could modify the form so that once the REA number field has been tabbed out of, the REA Number could be checked before proceeding with all the periphery data.

Using the ‘on exit’ event for the REA box I’ve tried the Dlookup code as follows

If IsNull(DLookup("[REA Number]", "[MCR Table]", "[REA Number] = "" & [Forms]![MCR_Input _Form].[REA Number] & """)) Then
Msgbox "Already Exists"

End If

Unfortunately the message ‘already exists’ appears whether the REA Number exists or not .

I can’t see where I’m going wrong. Can any one shed some light please?

 

[Rich]

If Not IsNull(DLookup etc

 

[MartinO]

Tried that but now no message appears whether the number exists or not - but if you've got any other ideas I'm willing to try

 

[Rich]

Try moving the code to the before update event of the control

 

[MartinO]

Unfortunately has the same effect as before.

 

[Mile-O]

Are you sure you've got your quotation marks right?

If Not IsNull(DLookup("[REA Number]", "[MCR Table]", "[REA Number] = """ & [Forms]![MCR_Input _Form]![REA Number] & """")) Then
    Msgbox "Already Exists" 
End If

 

[MartinO]

that seems to work for the first record but if I the tab back to try another number it seems to disregard the code. I've tried this 'on exit' event , 'before update' event, as Rich suggested, and also 'on current' - all seem to have the same effect.

I don't know if this would have an effect on dlookup but the [REA Number] field is a text field (has to be to accomodate the number, which is in fact a drawing number - format N0000000)

 

[MartinO]

Just to close this one off. I've now attached the code to a button on the form and it works. Seems it just didn't like being part of a contol box. But thanks to all contributors for their help.

 

[edp1959]

Look at your quotation marks and you shouldn't have brackets around "MCR Table". Your code should look like this and it will work.

If Not IsNull(DLookup("[REA Number]", "MCR Table", "[REA Number] = '" & [Forms]![MCR_Input _Form]![REA Number] & "'")) Then
Msgbox "Already Exists"
End If