Creating a logonname automatically

G

Guest

Ok Guys,

I've gotten some help already here, which was very helpfull. What am i doing
here:

* I'm taking txtSurName and txtLastName and trying to automatically create a
login name. (the first letter of the surname, followed by the lastname, one
word, lowercase --> if that one exists: take the first two letters of the
surname, followed by lastname, up to three letters of the surname max)

* It needs to check wheter or not a loginname exists, based on that, it will
add one more letter of the surname if a login should exist.

* If a login does not exist, it writes the value to the table Users, in the
field "logonnaam". That's it.

! The problem: it keeps jumping to the forelast Else: "Couldn't create the
new logon. Already existing logon name with the same first three letters and
last name", although the login name doesn't exists.

Any suggestions?


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim rec As Recordset
Dim newlogonname As String
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then ' you check if
the fields are filled
i = 1
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & newlogonname
& "'")) 'check if the new logon already exists in the users table
i = i + 1
If i = 4 Then Exit Do
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Loop
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close
Else
MsgBox "Couldn't create the new logon. Already existing logon name with
the same first three letters and last name", 48, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last name",
48, "Warning"
End If
End Sub
 
G

Guest

Here is a rewrite that should do it for you (barring any syntax errors).
Note 1 - Indenting your code makes it much easier to read.
Note 2 - What the heck is 48? Nobody knows all the number. That is why we
have constants. Use them. Again, readability!
Note 3- Notice I changed adding the new record from recorset processing to
SQL. It is much faster and, as you see, less coding.
Note 4 - I use the Execute method rather than the RunSQL method because it
is faster and you don't have to worry about setting warnings.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim newlogonname As String
Dim blnAddedUser As Boolean

'check if the fields are filled
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then
For i = 1 to 3
newlogonname = LCase(Left(txtSurName, i) & txtLastName)

'check if the new logon already exists in the users table
If IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & _
newlogonname & "'")) Then
CurrentDb.Execute("INSERT INTO Users (logonnaam) " & _
"VALUES (newlogonname);"), dbFailOnError
blnAddedUser = True
Exit For
End If
Next i

If Not blnAddedUser Then
MsgBox "Couldn't create the new logon. Already existing logon
name " & _
"with the same first three letters and last name", _
vbExclamation, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last
name", _
vbExclamatoin, "Warning"
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


Memento said:
Ok Guys,

I've gotten some help already here, which was very helpfull. What am i doing
here:

* I'm taking txtSurName and txtLastName and trying to automatically create a
login name. (the first letter of the surname, followed by the lastname, one
word, lowercase --> if that one exists: take the first two letters of the
surname, followed by lastname, up to three letters of the surname max)

* It needs to check wheter or not a loginname exists, based on that, it will
add one more letter of the surname if a login should exist.

* If a login does not exist, it writes the value to the table Users, in the
field "logonnaam". That's it.

! The problem: it keeps jumping to the forelast Else: "Couldn't create the
new logon. Already existing logon name with the same first three letters and
last name", although the login name doesn't exists.

Any suggestions?


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim rec As Recordset
Dim newlogonname As String
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then ' you check if
the fields are filled
i = 1
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & newlogonname
& "'")) 'check if the new logon already exists in the users table
i = i + 1
If i = 4 Then Exit Do
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Loop
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close
Else
MsgBox "Couldn't create the new logon. Already existing logon name with
the same first three letters and last name", 48, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last name",
48, "Warning"
End If
End Sub
Click to expand...
 
G

Guest

Okay guys,

I've already got working code, for which I thank you both. Klatuu is
absolutely right when to think about readability, which I am working on -
indenting, commenting, using constants...

Klatuu: your code causes a "Too few parameters. The suspected number is: 1"...

CurrentDb.Execute ("INSERT INTO Users (logonnaam) " & "VALUES
(newlogonname);"), dbFailOnError

Probably some typo somewhere, which I am overlooking...

Greetings,

Sven

Klatuu said:
Here is a rewrite that should do it for you (barring any syntax errors).
Note 1 - Indenting your code makes it much easier to read.
Note 2 - What the heck is 48? Nobody knows all the number. That is why we
have constants. Use them. Again, readability!
Note 3- Notice I changed adding the new record from recorset processing to
SQL. It is much faster and, as you see, less coding.
Note 4 - I use the Execute method rather than the RunSQL method because it
is faster and you don't have to worry about setting warnings.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim newlogonname As String
Dim blnAddedUser As Boolean

'check if the fields are filled
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then
For i = 1 to 3
newlogonname = LCase(Left(txtSurName, i) & txtLastName)

'check if the new logon already exists in the users table
If IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & _
newlogonname & "'")) Then
CurrentDb.Execute("INSERT INTO Users (logonnaam) " & _
"VALUES (newlogonname);"), dbFailOnError
blnAddedUser = True
Exit For
End If
Next i

If Not blnAddedUser Then
MsgBox "Couldn't create the new logon. Already existing logon
name " & _
"with the same first three letters and last name", _
vbExclamation, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last
name", _
vbExclamatoin, "Warning"
End If
End Sub

--
Dave Hargis, Microsoft Access MVP


Memento said:
Ok Guys,

I've gotten some help already here, which was very helpfull. What am i doing
here:

* I'm taking txtSurName and txtLastName and trying to automatically create a
login name. (the first letter of the surname, followed by the lastname, one
word, lowercase --> if that one exists: take the first two letters of the
surname, followed by lastname, up to three letters of the surname max)

* It needs to check wheter or not a loginname exists, based on that, it will
add one more letter of the surname if a login should exist.

* If a login does not exist, it writes the value to the table Users, in the
field "logonnaam". That's it.

! The problem: it keeps jumping to the forelast Else: "Couldn't create the
new logon. Already existing logon name with the same first three letters and
last name", although the login name doesn't exists.

Any suggestions?


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim rec As Recordset
Dim newlogonname As String
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then ' you check if
the fields are filled
i = 1
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & newlogonname
& "'")) 'check if the new logon already exists in the users table
i = i + 1
If i = 4 Then Exit Do
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Loop
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close
Else
MsgBox "Couldn't create the new logon. Already existing logon name with
the same first three letters and last name", 48, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last name",
48, "Warning"
End If
End Sub
Click to expand...
Click to expand...
 
G

Guest

Paolo,

I've got your code working now indeed... One more 'small' issue here :)

It writes the value of the new logonname into a new record, not in the
record where it should be created... But i'll manage that one.

Regards,

Sven

Paolo said:
Hi Memento,
In the code I forgot a not, so try this

Do While NOT IsNull(DLookup("logonnaam", "Users", "logonnaam = '" &
newlogonname
& "'")) 'check if the new logon already exists in the users table

Sorry for the mistake, Paolo

Memento said:
Ok Guys,

I've gotten some help already here, which was very helpfull. What am i doing
here:

* I'm taking txtSurName and txtLastName and trying to automatically create a
login name. (the first letter of the surname, followed by the lastname, one
word, lowercase --> if that one exists: take the first two letters of the
surname, followed by lastname, up to three letters of the surname max)

* It needs to check wheter or not a loginname exists, based on that, it will
add one more letter of the surname if a login should exist.

* If a login does not exist, it writes the value to the table Users, in the
field "logonnaam". That's it.

! The problem: it keeps jumping to the forelast Else: "Couldn't create the
new logon. Already existing logon name with the same first three letters and
last name", although the login name doesn't exists.

Any suggestions?


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim rec As Recordset
Dim newlogonname As String
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then ' you check if
the fields are filled
i = 1
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & newlogonname
& "'")) 'check if the new logon already exists in the users table
i = i + 1
If i = 4 Then Exit Do
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Loop
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close
Else
MsgBox "Couldn't create the new logon. Already existing logon name with
the same first three letters and last name", 48, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last name",
48, "Warning"
End If
End Sub
Click to expand...
Click to expand...
 
G

Guest

Hi Memento,
In the code I forgot a not, so try this

Do While NOT IsNull(DLookup("logonnaam", "Users", "logonnaam = '" &
newlogonname
& "'")) 'check if the new logon already exists in the users table

Sorry for the mistake, Paolo
 
G

Guest

Hi Sven,

For saving the new logonname in the same record of the associated name and
surname instead of doing this
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close

Do that (Let's say Fld_surname and Fld_lastname are the fields in the table
users where you want to save surname and the lastname)

If i < 4 Then
''You open the recordset on the right record
Set rec = CurrentDb.OpenRecordset("select * from users where
Fld_surname=""" & txtsurname & """ and Fld_Lastname=""" & txtlastname & """"
, dbOpenDynaset)
''You add the value of the new logon
rec.Edit
rec![logonnaam] = newlogonname
rec.Update
rec.Close

HTH Paolo

Memento said:
Paolo,

I've got your code working now indeed... One more 'small' issue here :)

It writes the value of the new logonname into a new record, not in the
record where it should be created... But i'll manage that one.

Regards,

Sven

Paolo said:
Hi Memento,
In the code I forgot a not, so try this

Do While NOT IsNull(DLookup("logonnaam", "Users", "logonnaam = '" &
newlogonname
& "'")) 'check if the new logon already exists in the users table

Sorry for the mistake, Paolo

Memento said:
Ok Guys,

I've gotten some help already here, which was very helpfull. What am i doing
here:

* I'm taking txtSurName and txtLastName and trying to automatically create a
login name. (the first letter of the surname, followed by the lastname, one
word, lowercase --> if that one exists: take the first two letters of the
surname, followed by lastname, up to three letters of the surname max)

* It needs to check wheter or not a loginname exists, based on that, it will
add one more letter of the surname if a login should exist.

* If a login does not exist, it writes the value to the table Users, in the
field "logonnaam". That's it.

! The problem: it keeps jumping to the forelast Else: "Couldn't create the
new logon. Already existing logon name with the same first three letters and
last name", although the login name doesn't exists.

Any suggestions?


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim rec As Recordset
Dim newlogonname As String
If Not IsNull(txtSurName) And Not IsNull(txtLastName) Then ' you check if
the fields are filled
i = 1
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam = '" & newlogonname
& "'")) 'check if the new logon already exists in the users table
i = i + 1
If i = 4 Then Exit Do
newlogonname = LCase(Left(txtSurName, i) & txtLastName)
Loop
If i < 4 Then
Set rec = CurrentDb.OpenRecordset("select * from users", dbOpenDynaset)
rec.AddNew
rec![logonnaam] = newlogonname
rec.Update
rec.Close
Else
MsgBox "Couldn't create the new logon. Already existing logon name with
the same first three letters and last name", 48, "Warning"
End If
Else
MsgBox "Please, fill the fields with the given name and the last name",
48, "Warning"
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a logonname part 3 3
Code writes to subsequent record instead of current record 3
Programmatically creating email, checking if exists 1
Concatenating & trim contents of 2 textboxes and write in another 5
Access Passing the value to a table 1
How do you save a field? 8
recordset issue 5
Dynamically changing a subform's recordset using txt box 1

Top