check for duplicate values in a table (1 Viewer)

[chewy]

I want to fill in an Ip address and check to see if iit is already listed. could you tell me how to use the dlookup code? The table is called Assets. the field is called IpAddress.

Any help for a beginner at this?

 

[WayneRyan]

chewy,

Result = DLookup("fieldname2", "tablename", "[fieldname] = 'xx'")

Result is the string containing the contents of fieldname2 in
the table tablename where fieldname contains 'xx'.

If 'xx' is not unique then the contents of Result are of dubious
value.

By the way, the help files have good examples of these
types of functions and it doesn't hurt to experiment.

Wayne

 

[chewy]

but how do I tell it to check all of the ip addresses already entered? What is xx supposed to represent?

 

[WayneRyan]

chewy,

On an AfterUpdate on your control IPAddress:

' ******************************************
Dim Result As String

Result = DLookup("[UserName]", "Assets", "[IPAddress] = '" & _
Me.IPAdress & ''")

If Not IsNull(Result) Then
MsgBox("Duplicate, already assigned to " & Result)
Exit Sub
End If
' *******************************************

Assumes that the table Assets has a field called UserName.
If the value of your control IPAddress is in the table Assets
in the field IPAddress, then Result will have the username and
its a duplicate. If Result is null, there is no match, and take
no action.

hth,
Wayne