I See Paolo,
I've changed it according to your instructions, but it keeps giving me the
same error "Syntaxis error in string. In query expression
newlogonname="logonname
The code:
Do While IsNull(DLookup("logonnaam", "Users", "logonnaam=""" & newlogonname)
& """")
What i noticed is that in the error he doesn't show up the leading ". Could
it be something is wrong with that?
To be absolutely clear: The 3 textboxes on the form are called:
txtSurName contains the surname
txtLastName contains the lastname
txtLogonName should contain the logonname, lol
Than there's the table with:
Voornaam contains the surname
Naam contains the lastname
logonnaam contains the logon name
The full code now reads:
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
Thanks in advance Paolo,
Regards
Paolo said:
Hiya,
the dlookup statement is for checking if the logonname already exist in the
users table.
I suppose that in your users table the field where you save the logonname is
called logonnaam and so the dlookup must be this:
DLookup("logonnaam", "Users", "logonnaam=""" & newlogonname) & """"
'cause the first parameter is the field whose value is returned, the second
is the table containing the data and the third (optional) is a condition to
restrict the range of data returned. If no data correspond to the condition
null is returned.
So that my dlookup search the table users where the logonnaam =
newlogonname. If found it, it returns the value (meaning that the logonname
already exist) otherwise returns null and that means that the newlogonname
doesn't exist.
HTH Paolo
Memento said:
Hello, Paolo, you're code helped me on the way, so i've adapted the code like
this:
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("newlogonname", "Users", "newlogonname="" " &
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
Unfortunately i'm getting an error here:
Do While IsNull(DLookup("newlogonname", "Users", "newlogonname="" " &
newlogonname) & """")
Where it says: "Syntaxis error in string. In query expression
newlogonname="...user logonname"
I can't see where this is going wrong, i've checked the DLookup syntaxis,
and this looks okay...
Any suggestions?
With kind regards,
Memento
:
Hi Memento,
you can put a button on your form with this code on the on click event
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("logonname","users","logonname=""" & 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![logonname]=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"
endif
else
msgbox "Please, fill the fields with the given name and the last
name",48,"Warning"
endif
HTH Paolo
:
Hello Guys,
I having difficulties getting this to work. I have 2 textboxes on a form
(txtSurName & txtLastName), from which i would like to automatically create a
logon-name. the rule is to take the first letter of the surname, followed by
the complete lastname, all bound together, convert to lowercase, without
spaces. Because 2 logon names can easily be the same, i need to check if a
logon name doesn't already exists in the table "users", in the field
"logonname". the rule is then to take the first TWO letters of the surname,
followed by the lastname, lowercase, no spaces, etc... with a max of 3
letters of the surname, incase that one should also exist.
The layout of my table (simplified):
Surname LastName LogonName
Marc Shuttleworth mshuttleworth
David Stevens dstevens
Bill Gates bgates
Benny Gates begates
You'll get the point i guess.
Any suggestions on how to get this done. I've started coding this one, but
at this moment i'm lost in a forest (lol) of properties and methods...
I'm using Access 2007, in case that should be important.
Already thanks guys!
With regards,
Sven
