Creating a logonname automatically

[Memento]

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

 

[Klatuu]

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

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

 

[Memento]

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

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

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

 

[Memento]

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

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

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

 

[Paolo]

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

 

[Paolo]

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

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

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

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