looking up fields in a table to compare to a textbox on a form

[72185]

Well right now I am generating a "username" and then checking to see
if the "username" is unique or not. I have a table set up with 1000s
of usernames so I am trying to make sure that the same one does not
get used over. So with that in mind I am letting the end user
generate their name and then I want it to go through the table and see
if its unique and if it is unique to send a message saying "its
unique" and if not then to add a "A" or "B" to the end of it depending
how many times the name has been used. I am having trouble looking up
the name in the table. I just keep getting the same name I have in
the textbox although its a unique name. Here is what I have so far.

strfirstname = [Forms]![ADD_EDIT_RECORD_FRM]![FIRSTNAME]
strmiddlename = [Forms]![ADD_EDIT_RECORD_FRM]![MIDDLENAME]
strlastname = [Forms]![ADD_EDIT_RECORD_FRM]![LASTNAME]

[Forms]![ADD_EDIT_RECORD_FRM]![usernametextbox] = Left(strfirstname,
1) & Left(strmiddlename, 1) & Left(strlastname, 6)
strusername = usernametextbox.Value
'MsgBox "Username: " & strusername, vbOKOnly, "Invalid Search
Criterion!"

Dim strusernameRef As String
Dim strSearch As String

'Check txtSearch for Null value.

If IsNull(Me![usernametextbox]) Or (Me![usernametextbox]) = ""
Then
MsgBox "Please enter First Name, Middle Initial and Last
Name!", vbOKOnly, "Invalid Search Criterion!"
Me![usernametextbox].SetFocus
End If

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID


Me.USERNAME = DLookup("[username]", "[IMPORT]",
"[usernametextbox]='" & [Forms]![ADD_EDIT_RECORD_FRM]!
[usernametextbox] & "'")
DLookup("CatID", "IMPORTegories", "[USERNAMEgFieldName] = '"
&Me.Item & "'")

DoCmd.ShowAllRecords
DoCmd.GoToControl ("usernametextbox")
DoCmd.FindRecord Me!USERNAME


strSearch = usernametextbox.Text
intAsciiValue = 65
usernametextbox.SetFocus
strusernameRef = USERNAME
usernametextbox.SetFocus


Do Until strusernameRef <> strSearch
If strusernameRef = strSearch Then
MsgBox "Username: " & strSearch & " Is Already In
Use. A New Username Will be Provided", , "Choose another Name!"
usernametextbox.SetFocus
usernametextbox = ""

strusernameRef = strSearch & Chr(intAsciiValue)
MsgBox "New username name is: " & strusernameRef, ,
"Choose another Name!"
usernametextbox = strusernameRef
intAsciiValue = intAsciiValue + 1
'If value not found sets focus back to txtSearch and shows
msgbox
Else
MsgBox strusernameRef & " - Username Is Unique.", ,
"Username Unique!"
PERSONELNUM.SetFocus
End If
Loop
End Sub


Any help is appreciated!

 

[Bob Quintal]

Comments interspersed with the code.

Well right now I am generating a "username" and then checking
to see if the "username" is unique or not. I have a table set
up with 1000s of usernames so I am trying to make sure that
the same one does not get used over. So with that in mind I
am letting the end user generate their name and then I want it
to go through the table and see if its unique and if it is
unique to send a message saying "its unique" and if not then
to add a "A" or "B" to the end of it depending how many times
the name has been used. I am having trouble looking up the
name in the table. I just keep getting the same name I have
in the textbox although its a unique name. Here is what I
have so far.

What a dog's breakfast.

Is this in the form's code module or in a separate module?

strfirstname = [Forms]![ADD_EDIT_RECORD_FRM]![FIRSTNAME]
strmiddlename = [Forms]![ADD_EDIT_RECORD_FRM]![MIDDLENAME]
strlastname = [Forms]![ADD_EDIT_RECORD_FRM]![LASTNAME]

[Forms]![ADD_EDIT_RECORD_FRM]![usernametextbox] =
Left(strfirstname, 1) & Left(strmiddlename, 1) &
Left(strlastname, 6) strusername = usernametextbox.Value

You do not need to put the value of each textbox int variables
before combining. If you are in the Form's module, replace the
Forms![ADD_EDIT_RECORD_FRM] with the abbreviation Me!

Me![usernametextbox] = _
Left(Me!firstname, 1) _
& Left(Me!middlename, 1)
& Left(Me!lastname, 6)

'MsgBox "Username: " & strusername, vbOKOnly, "Invalid Search
Criterion!"

What is the line above doing here?

Dim strusernameRef As String
Dim strSearch As String

'Check txtSearch for Null value.

If IsNull(Me![usernametextbox]) Or (Me![usernametextbox])
= ""
Then
MsgBox "Please enter First Name, Middle Initial and
Last
Name!", vbOKOnly, "Invalid Search Criterion!"
Me![usernametextbox].SetFocus

Unless you put an Exit Sub here, the rest of the code will
attempt to run with the invalid name.

End If

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID


Me.USERNAME = DLookup("[username]", "[IMPORT]",
"[usernametextbox]='" & [Forms]![ADD_EDIT_RECORD_FRM]!
[usernametextbox] & "'")

The line above should be
strUsername = Dlookup( _
"Username", _
"Import", _
"username = '" & Me.usernametextbox & "'")

DLookup("CatID", "IMPORTegories", "[USERNAMEgFieldName] =
'"
&Me.Item & "'")

Huh?????????????

From here down is garbage.
All you want to do is test if the Dlookup returned the username,
and if it did, ask the user to pick another or add your suffix.

if strUsername = strUserName then
msgbox "Username is assigned. Please note the suffix
added"
else
.... Msgbox "your username is " & strusername
end if

DoCmd.ShowAllRecords
DoCmd.GoToControl ("usernametextbox")
DoCmd.FindRecord Me!USERNAME

These are absolutely useless in this context

 

[72185]

Comments interspersed with the code.

Well right now I am generating a "username" and then checking
to see if the "username" is unique or not. I have a table set
up with 1000s of usernames so I am trying to make sure that
the same one does not get used over. So with that in mind I
am letting the end user generate their name and then I want it
to go through the table and see if its unique and if it is
unique to send a message saying "its unique" and if not then
to add a "A" or "B" to the end of it depending how many times
the name has been used. I am having trouble looking up the
name in the table. I just keep getting the same name I have
in the textbox although its a unique name. Here is what I
have so far.

What a dog's breakfast.

Is this in the form's code module or in a separate module?

strfirstname = [Forms]![ADD_EDIT_RECORD_FRM]![FIRSTNAME]
strmiddlename = [Forms]![ADD_EDIT_RECORD_FRM]![MIDDLENAME]
strlastname = [Forms]![ADD_EDIT_RECORD_FRM]![LASTNAME]

[Forms]![ADD_EDIT_RECORD_FRM]![usernametextbox] =
Left(strfirstname, 1) & Left(strmiddlename, 1) &
Left(strlastname, 6) strusername = usernametextbox.Value

You do not need to put the value of each textbox int variables
before combining. If you are in the Form's module, replace the
Forms![ADD_EDIT_RECORD_FRM] with the abbreviation Me!

Me![usernametextbox] = _
Left(Me!firstname, 1) _
& Left(Me!middlename, 1)
& Left(Me!lastname, 6)

'MsgBox "Username: " & strusername, vbOKOnly, "Invalid Search
Criterion!"

What is the line above doing here?

Dim strusernameRef As String
Dim strSearch As String

'Check txtSearch for Null value.

If IsNull(Me![usernametextbox]) Or (Me![usernametextbox])
= ""
Then
MsgBox "Please enter First Name, Middle Initial and
Last
Name!", vbOKOnly, "Invalid Search Criterion!"
Me![usernametextbox].SetFocus

Unless you put an Exit Sub here, the rest of the code will
attempt to run with the invalid name.

End If

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

Me.USERNAME = DLookup("[username]", "[IMPORT]",
"[usernametextbox]='" & [Forms]![ADD_EDIT_RECORD_FRM]!
[usernametextbox] & "'")

The line above should be
strUsername = Dlookup( _
"Username", _
"Import", _
"username = '" & Me.usernametextbox & "'")

DLookup("CatID", "IMPORTegories", "[USERNAMEgFieldName] =
'"
&Me.Item & "'")

Huh?????????????

From here down is garbage.
All you want to do is test if the Dlookup returned the username,
and if it did, ask the user to pick another or add your suffix.

if strUsername = strUserName then
msgbox "Username is assigned. Please note the suffix
added"
else
... Msgbox "your username is " & strusername
end if

DoCmd.ShowAllRecords
DoCmd.GoToControl ("usernametextbox")
DoCmd.FindRecord Me!USERNAME

These are absolutely useless in this context

strSearch = usernametextbox.Text
intAsciiValue = 65
usernametextbox.SetFocus
strusernameRef = USERNAME
usernametextbox.SetFocus

Do Until strusernameRef <> strSearch
If strusernameRef = strSearch Then
MsgBox "Username: " & strSearch & " Is Already
In
Use. A New Username Will be Provided", , "Choose another
Name!"
usernametextbox.SetFocus
usernametextbox = ""

strusernameRef = strSearch &
Chr(intAsciiValue) MsgBox "New username name
is: " & strusernameRef, ,
"Choose another Name!"
usernametextbox = strusernameRef
intAsciiValue = intAsciiValue + 1
'If value not found sets focus back to txtSearch
and shows
msgbox
Else
MsgBox strusernameRef & " - Username Is
Unique.", ,
"Username Unique!"
PERSONELNUM.SetFocus
End If
Loop
End Sub

Any help is appreciated!

Thanx for your help with my program I am somewhat new to Access VBA.
Most of the things you didn't know why they were there were eithers
noted out or just stuff from earlier that I was trying out and didn't
want to delete just in case I needed it later. So far though that
works like I want it too. Although I am going to add a few more
things to the last If... Then statement. I am trying to make it
automatically generate the username with an 'A' or 'B' and so one to
the end of it. Depending on whether the name with a 'A' or 'B' has
already been used. If you have any insight I wouldn't mind a second
opinion.

 

[Bob Quintal]

Thanx for your help with my program I am somewhat new to Access
VBA. Most of the things you didn't know why they were there were
eithers noted out or just stuff from earlier that I was trying out
and didn't want to delete just in case I needed it later. So far
though that works like I want it too. Although I am going to add
a few more things to the last If... Then statement. I am trying
to make it automatically generate the username with an 'A' or 'B'
and so one to the end of it. Depending on whether the name with a
'A' or 'B' has already been used. If you have any insight I
wouldn't mind a second opinion.

Do you have room in your username to add a character? usually they are
limited to 8, 16, or 24 characters, dependent on the system. If your
name is already at maximum, it will fail.

That point aside, once you know that the username already exists, you
can add the a, or relace the last letter.

Then you need to repeat the Dlookup() with teh modified name.If it
fails again, you have to increment the last letter.

get the last letter of the name into a variable, then increment it by
one ascii code.

iNameLen = len(username)
if iNameLen >1 then
cLastChar = mid(username(inamelen-1,1)
'test for Z, and do something
if clastchar = "z" then
'probably bail out of automation and
'ask user to generate a username differently
else
clastchar = chr(asc(clastchar)+1)
mid(username(inamelen-1,1) = cLastChar
end if

and test again for an existing username.

 

[72185]

Do you have room in your username to add a character? usually they are
limited to 8, 16, or 24 characters, dependent on the system. If your
name is already at maximum, it will fail.

That point aside, once you know that the username already exists, you
can add the a, or relace the last letter.

Then you need to repeat the Dlookup() with teh modified name.If it
fails again, you have to increment the last letter.

get the last letter of the name into a variable, then increment it by
one ascii code.

iNameLen = len(username)
if iNameLen >1 then
cLastChar = mid(username(inamelen-1,1)
'test for Z, and do something
if clastchar = "z" then
'probably bail out of automation and
'ask user to generate a username differently
else
clastchar = chr(asc(clastchar)+1)
mid(username(inamelen-1,1) = cLastChar
end if

and test again for an existing username.

Well actually while waiting to see if I got a response I figured out
how to get the name generated and everything set up in the right
form. Although your help was taken into consideration and gave me an
a couple of ideas. I am also doing the Lookup on account numbers and
while using the same formula just with the name changes and
everything. It is either spitting back the right number if it is a
duplicate or it is coming up and showing an error message that says
"Null values are being used incorrectly" and not allowing me to
proceed. I haven't got a chance to work on it long but this is what
I have so far. If anyone has any lead way of which direction I should
take this all help is appreciated! Thanks agian!


Private Sub personelnumtextbox_AfterUpdate()
Dim strpersonelnum As String

strpersonelnum = personelnumtextbox.Value

strpersonelnum = DLookup("personelnum", "Import", "personelnum = '" &
Me.personelnumtextbox & "'")

If strpersonelnum = strpersonelnum Then
MsgBox "Personel Number has already been assigned to another
user"
personelnumtextbox.SetFocus
End If
End Sub

 

[72185]

Well actually while waiting to see if I got a response I figured out
how to get the name generated and everything set up in the right
form. Although your help was taken into consideration and gave me an
a couple of ideas. I am also doing the Lookup on account numbers and
while using the same formula just with the name changes and
everything. It is either spitting back the right number if it is a
duplicate or it is coming up and showing an error message that says
"Null values are being used incorrectly" and not allowing me to
proceed. I haven't got a chance to work on it long but this is what
I have so far. If anyone has any lead way of which direction I should
take this all help is appreciated! Thanks agian!

Private Sub personelnumtextbox_AfterUpdate()
Dim strpersonelnum As String

strpersonelnum = personelnumtextbox.Value

strpersonelnum = DLookup("personelnum", "Import", "personelnum = '" &
Me.personelnumtextbox & "'")

If strpersonelnum = strpersonelnum Then
MsgBox "Personel Number has already been assigned to another
user"
personelnumtextbox.SetFocus
End If
End Sub- Hide quoted text -

- Show quoted text -

Nevermind it has been figured out. It was something I was
overlooking. Sorry for the inconvience.