Searching a Field to see if a duplicate exists

[tecman]

Hello All,
I am trying in a form using visual basic to search for a new userid
(created by concatenating the first letter of the first name and last
4 of the last name +1). I want to be able to search the userid [UID]
field in the table for duplicates. here is some of my code so far. the
*(added for reference) line gives the error "Run time error 2001",
"You have canceled the previous operation" If you have a user named
Bob Tester, the userid will return BTEST1. I will eventually modify
the code to add 1 to the end if the userid already exists. Thanks in
advance for your help.

Private Sub Command6_Click()

Dim UIDNEW As String
Dim fname As String
Dim lname As String
Dim D As Integer

D = 1
fname = Me.first
lname = Me.last

UIDNEW = Mid(fname, 1, 1) & Mid(lname, 1, 4) & D

* If Not IsNull(DLookup("[UID]", "Table1", "UID=" & UIDNEW)) Then
MsgBox "This Key already exists"
Else
Me.uidbox = UIDNEW
Me.uidbox.Requery
End If
End Sub

 

[Marshall Barton]

I am trying in a form using visual basic to search for a new userid
(created by concatenating the first letter of the first name and last
4 of the last name +1). I want to be able to search the userid [UID]
field in the table for duplicates. here is some of my code so far. the
*(added for reference) line gives the error "Run time error 2001",
"You have canceled the previous operation" If you have a user named
Bob Tester, the userid will return BTEST1. I will eventually modify
the code to add 1 to the end if the userid already exists. Thanks in
advance for your help.

Private Sub Command6_Click()

Dim UIDNEW As String
Dim fname As String
Dim lname As String
Dim D As Integer

D = 1
fname = Me.first
lname = Me.last

UIDNEW = Mid(fname, 1, 1) & Mid(lname, 1, 4) & D

* If Not IsNull(DLookup("[UID]", "Table1", "UID=" & UIDNEW)) Then
MsgBox "This Key already exists"
Else
Me.uidbox = UIDNEW
Me.uidbox.Requery
End If
End Sub

Mid is kind of clumsy here and you need quotes around text
values:

UIDNEW = Left(fname, 1) & Left(lname, 4) & D
If Not IsNull(DLookup("UID", "Table1", "UID='" & UIDNEW
& "' ")) Then

I have no idea why you are getting that error, but the
Requery is not doing anything.

 

[Guest]

Tecman's description is ambiguous; the *words* say last 4 of the last name,
but the BTEST example uses first 4. Depending on which is right, either
Left(lname,4) or Right(lname,4) is what's needed.

Also, it seems to me that the D, AKA 1, ought not to be added until AFTER
the comparison with existing keys ... yes? No? If I'm understanding it
right, the 1 is only supposed to be added if you run into a duplicate
situation.

And in that regard, what happens if a SECOND duplicate is encountered? For
example, you have Rston, then the second instance becomes Rston1, but how are
you going to handle the next RSton? Presumably it would need to be Rston2,
but the code doesn't presently handle that situation. At that point it gets
ugly, because you have to find a way to compare your newkey to only the left
part of your existing keys, count how many matches there are, and tack on the
next sequential number (=count of matches) to resolve the situation. And if
you have keys of less than five characters (see next paragraph), that adds
another complication. And what happens if/when you get into double digits?
Will BTEST10 be a problem? I can think of 2-3 ways to handle all this, but
for sure it needs to be handled.

And finally, is it going to cause any problems if the last name has less
than four characters -- e.g., Poe, or Ray, or Wu, or Ky -- thereby producing
a key less than five characters in length? If no, fine, if yes, some kind of
"padding" process will be needed.

I am trying in a form using visual basic to search for a new userid
(created by concatenating the first letter of the first name and last
4 of the last name +1). I want to be able to search the userid [UID]
field in the table for duplicates. here is some of my code so far. the
*(added for reference) line gives the error "Run time error 2001",
"You have canceled the previous operation" If you have a user named
Bob Tester, the userid will return BTEST1. I will eventually modify
the code to add 1 to the end if the userid already exists. Thanks in
advance for your help.

Private Sub Command6_Click()

Dim UIDNEW As String
Dim fname As String
Dim lname As String
Dim D As Integer

D = 1
fname = Me.first
lname = Me.last

UIDNEW = Mid(fname, 1, 1) & Mid(lname, 1, 4) & D

* If Not IsNull(DLookup("[UID]", "Table1", "UID=" & UIDNEW)) Then
MsgBox "This Key already exists"
Else
Me.uidbox = UIDNEW
Me.uidbox.Requery
End If
End Sub

Mid is kind of clumsy here and you need quotes around text
values:

UIDNEW = Left(fname, 1) & Left(lname, 4) & D
If Not IsNull(DLookup("UID", "Table1", "UID='" & UIDNEW
& "' ")) Then

I have no idea why you are getting that error, but the
Requery is not doing anything.

 

[tecman]

I am trying in a form using visual basic to search for a new userid
(created by concatenating the first letter of the first name and last
4 of the last name +1). I want to be able to search the userid [UID]
field in the table for duplicates. here is some of my code so far. the
*(added for reference) line gives the error "Run time error 2001",
"You have canceled the previous operation" If you have a user named
Bob Tester, the userid will return BTEST1. I will eventually modify
the code to add 1 to the end if the userid already exists. Thanks in
advance for your help.

Private Sub Command6_Click()

Dim UIDNEW As String
Dim fname As String
Dim lname As String
Dim D As Integer

D = 1
fname = Me.first
lname = Me.last

UIDNEW = Mid(fname, 1, 1) & Mid(lname, 1, 4) & D

* If Not IsNull(DLookup("[UID]", "Table1", "UID=" & UIDNEW)) Then
MsgBox "This Key already exists"
Else
Me.uidbox = UIDNEW
Me.uidbox.Requery
End If
End Sub

Mid is kind of clumsy here and you need quotes around text
values:

UIDNEW = Left(fname, 1) & Left(lname, 4) & D
If Not IsNull(DLookup("UID", "Table1", "UID='" & UIDNEW
& "' ")) Then

I have no idea why you are getting that error, but the
Requery is not doing anything.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks Marsh, your code modification worked perfectly