DLookUp

[ddrew]

Hi cant seem to find what Im trying to do, which I think is quite simple

I have a unique identifier (DogID), its the users reqposibility to put that number in, but I need the code to check the table to see if the number is already in use. I suspect I should be using the DLookUp function. But I dont know how to implemet it.

I have this in my Before Update on the field, but its clearly wrong

DLookUp([tbl_Profile]![DogID],[DogID])

 

[namliam]

You can use the DLookup for this check yes, though it's syntax should be something like:
DLookup(WhatToReturn, TableName, WhereClause)

WhatToReturn, can in this case be the same DogID
TableName, yes the 'real' tablename
WhereClause, SomeField = SomeValue

 

[ddrew]

Thanks for your help. This where I am now:

Private Sub DogID_BeforeUpdate(Cancel As Integer)
    DLookup "[DogID]", "tbl_Profile", "[DogID] = Form![DogID]"
    If Me.DogID = DogID Then
        MsgBox "This Dog ID is already in use"
        Me.DogID = Null
        Me.DogID.SetFocus
    End If
End Sub

But it comes up with the message everytime, even if the DogID isnt in use.

 

[namliam]

You need to put the result of the dlookup into a variable and keep the ID outside of the string... or simply check to have the DLookup returning a null value, which is what it does when it cant find your ID

Private Sub DogID_BeforeUpdate(Cancel As Integer)
    If Isnull(DLookup("[DogID]", "tbl_Profile", "[DogID] = " & Form![DogID])) then
        ' Add ID
    else
        ' ID already exists
    end if

 

[ddrew]

Thanks namliam, unfortunatly that produced a 2471 runtime error (I think it has something to do with some "", but I dont have any idea where the problem is!

 

[namliam]

The only reason that should fail is if your DogID isnt a real "ID" in the sence that it is not a number field.
In which case:

Private Sub DogID_BeforeUpdate(Cancel As Integer)
    If Isnull(DLookup("[DogID]", "tbl_Profile", "[DogID] = """ & Form![DogID] & """")) then
        ' Add ID
    else
        ' ID already exists
    end if

And yes, I did mean to put that may " there.

 

[RainLover]

If interested there is a setting in the table design that disallows duplicates.

You could use this which would not require any code.

 

[ddrew]

Thanks for your help. Your right, the ID can be whatever the user chooses, I would have said but I didn't realise that it made a difference.

Im not sure what you eant by:

"And yes, I did mean to put that may " there."

Got to be honest tIm a bit lost with this right now! I know what I want to do. I want to make sure that the DogID is not already in use and if it is, clear the data from the field and set the focus back on it.

Im not sure if I should be doing it on the before or after update.:banghead:

 

[namliam]

Try try and try each option see how it works and see if that works the way you want it to work....

I did put in the code """" which some people think that to be an error, however the """" is supposed to be there.

    If Isnull(DLookup("[DogID]", "tbl_Profile", "[DogID] = """ & Form![DogID] & """")) then
        ' Add ID
        msgbox "New" 
    else
        ' ID already exists
        Msgbox "Exists"
    end if

 

[RainLover]

Im not sure if I should be doing it on the before or after update.

Use the before update event. This allows you to make changes easier.

 

[ddrew]

Right so I have this in the BeforeUpdate:

Private Sub DogID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[DogID]", "tbl_Profile", "[DogID] = """ & Form![DogID] & """")) Then
        ' Add ID
        MsgBox "New"
    Else
        ' ID already exists
        MsgBox "Exists"
    End If
End Sub

The problem now is that I need to delete the contents of the textbox and it wont let me! Ive tried

Me.DogID = Null

Me.DogID = ""

Neither seems to work

 

[ddrew]

Me.Undo

Tried to make it to difficult!