Creating a logonname part 3

[Guest]

Hello Guys,

I'm trying to automatically create a logonname out of a filled in sur- and
lastname. I'm running into a small problem here. This is the full code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim txtLogonname 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
txtLogonname = LCase(Left(txtSurName, i) & txtLastName)

'check if the new logon already exists in the users table
If IsNull(DLookup("Logonnaam", "Users", "Logonnaam = '" & _
txtLogonname & "'")) Then
CurrentDb.Execute ("INSERT INTO Users (Logonnaam) " & "
VALUES (txtLogonname);"), dbFailOnError
blnAddedUser = True
Exit For
End If
Next i
If Not blnAddedUser Then
MsgBox "Couldn't create the new logon. Already existing
logonname " & _
"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
lastname", _
vbExclamatoin, "Warning"
End If
End Sub

The line that gives me errors:

CurrentDb.Execute ("INSERT INTO Users (Logonnaam) " & " VALUES
(txtLogonname);"), dbFailOnError

On this line it gives me the error: "To few parameters. The expected number
is: 1".

Any ideas here?

Thanks in advance!

With regards,

Sven

 

[Guest]

CurrentDb.Execute ("INSERT INTO Users (Logonnaam) " & " VALUES
(txtLogonname);"),

Maybe check your spelling (Logonname)

 

[John W. Vinson]

On this line it gives me the error: "To few parameters. The expected number
is: 1".

You're passing SQL the *name* of the new logon name, rather than its value;
and you need quotemarks included in the string. Try

CurrentDb.Execute ("INSERT INTO Users ([Logonnaam]) " _
& "VALUES (""" & txtLogonname & """);"), dbFailOnError

Thus if txtLogonname contains the text string JVINS your query string will be

INSERT INTO Users([Logonnaam]) VALUES ("JVINS");

Note that to include a " mark in a text string delimited by " you must use two
consecutive ".

John W. Vinson [MVP]

 

[George Nicholson]

Single & double quotes are messed up & txtLogonname has to stand alone:

CurrentDb.Execute "INSERT INTO Users (Logonnaam) VALUES ('" & txtLogonname &
"');," dbFailOnError

I find the following to be easier to read, maintain & debug:
strSQL = "INSERT INTO Users (Logonnaam) VALUES ('" & txtLogonname & "');"
CurrentDb.Execute strSQL, dbFailOnError

If you put a breakpoint on the Execute line and then type ?strSQL in the
Immediate window when the code pauses on that breakpoint, glaring problems
in the SQL construct you are asking to be executed become apparent (like
variable names appearing rather than their values...).

HTH,