Code writes to subsequent record instead of current record

[Memento]

Hello Guys,

I'm having trouble with a form and some code. The goal is to automatically
create a logonname out of a filled-in sur- and lastname. The problem is that
the code works perfectly, but it writes to a next record, instead of the
current record that i'm creating at that moment. The 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

How can i get this code to write the logonname to the current record, where
it should be, and not to a new subsequent record?

Thanks in advance,

Sven

 

[Stefan Hoffmann]

hi,

How can i get this code to write the logonname to the current record, where
it should be, and not to a new subsequent record?

Your INSERT INTO statement creates this new record. Just write your
value to the current field:

Me![Logonnaam] = txtLogonname



mfG
--> stefan <--

 

[Memento]

Yes, that's what I tried earlier, Stefan, but it simply doesn't work. With my
code it keeps writing the value of txtLogonname to a subsequent new record.
This makes that it writes (for example) the value "Stefan" and the value
"Hoffman" to a record, but it writes "shoffmann" to a subsequent record...

hi,

How can i get this code to write the logonname to the current record, where
it should be, and not to a new subsequent record?

Your INSERT INTO statement creates this new record. Just write your
value to the current field:

Me![Logonnaam] = txtLogonname



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Yes, that's what I tried earlier, Stefan, but it simply doesn't work. With my
code it keeps writing the value of txtLogonname to a subsequent new record.
This makes that it writes (for example) the value "Stefan" and the value
"Hoffman" to a record, but it writes "shoffmann" to a subsequent record...

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim i As Integer
Dim txtLogonname As String
Dim blnAddUser As Boolean

Cancel = True ' To control whether the record will be saved.
blnAddUser = False

If Not IsNull(txtSurName) And _
Not IsNull(txtLastName) Then
For i = 1 To 3
txtLogonname = LCase(Left(txtSurName, i) & txtLastName)
If DCount("*", _
"Users", _
"Logonnaam = '" & txtLogonname & "'") = 0 Then_
blnAddUser = True
Exit For
End If
Next i
End If

If blnAddUser Then
' Is this form based on your [Users] table?
Me![Logonnaam] = txtLogonname
Cancel = False
End If

End Sub


mfG
--> stefan <--