Query using VBA code

[pepok4]

Hi all,


I have very little progamming experience with Access. I have field "User" which is filled with "firstname" and "lastname" (format looks like: firstname.username). Next I have second field "Nickname". I would like to fill "Nickname" automatically when I go to next record. Nickname contains 6 characters (all uppercase). Format of Nickname is: first character from "firstname" and first five characters from "lastname" (behind dot).For example:

For example:
1st user
Field User: Peter.Smith
Field Nickname: PSMITH

2nd user
Field User: Thomas.Sikoooo
Field Nickname: TSIKOO

3rd user
...
etc.

I have this query, which work correct for me, but I do not want to run an update query.

UPDATE Table1 SET Table1.NickName = UCase(Left(Left([User],InStr(1,[User],".")-1),1) & Left(Right([User],Len([User])-InStr(1,[User],".")),5));

How I can convert this query to VBA code. I want, than update was automatically executing, when I'm going to next record in form. Necessary condition to run code: Field "user" must be filled!

Many thanks!

[ssanfu]

Having the first name and last name in the same field is not good practice. It violates normalization rules.
How do you do you handle names like:
O'Brian (apostrophe)
Van Horn (space in name)
Del La Cruz (spaces in name)

I would use the form before update:

Private Sub Form_BeforeUpdate()

   Dim TestArray() As String

   TestArray = Split(Me.User, ".")
   Me.NickName = Left(TestArray(0), 1) & Left(TestArray(1), 5)

End Sub

[pepok4]

Hi ssanfu,
good tip, but my database will be used only with people, which has not apostrophe or spaces in name. I do not have to solve these cases.

I do not why, but when I copy your code and use as form before update, it does not work. :-(

[June7]

An alternative is to not save the value but to just calculate whenever needed. Can be in query or on form or on report.

Why doesn't work - error message, wrong results, nothing happens?

[ssanfu]

I do not why, but when I copy your code and use as form before update, it does not work

Did go back to the form in design view,
open the properties dialog box for the form,
click on the events tab,
selected [Event Procedure] in the Before Update and clicked on the 3 dots?

This will connect the code to the event.

[pepok4]

An alternative is to not save the value but to just calculate whenever needed. Can be in query or on form or on report.
Why doesn't work - error message, wrong results, nothing happens?

Nothing happens.

Did go back to the form in design view,
open the properties dialog box for the form,
click on the events tab,
selected [Event Procedure] in the Before Update and clicked on the 3 dots?

This will connect the code to the event.

I make this steps, but it doesn't work. Nothing happens - when I switch between records. I try change my file to other version of MS Access, but it is the same.

[June7]

Again, why do you need to save this data? It can simply be calculated whenever needed.

However, I tried the procedure and I get an error message: "The expression Before Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name." The BeforeUpdate sub declaration has an argument:

Private Sub Form_BeforeUpdate(Cancel As Integer)
...
End Sub

[ssanfu]

The BeforeUpdate sub declaration has an argument:

Sometimes I hate cut and paste...


Should have been (as June pointed out ):

Private Sub Form_BeforeUpdate(Cancel As Integer)

   Dim TestArray() As String

   TestArray = Split(Me.User, ".")
   Me.NickName = Left(TestArray(0), 1) & Left(TestArray(1), 5)

End Sub

------------------------
You really should change this >> Having the first name and last name in the same field is not good practice. It violates normalization rules.

[June7]

And if you want the result in all upper case:

UCase(Left(TestArray(0), 1) & Left(TestArray(1), 5))

[pepok4]

Private Sub Form_BeforeUpdate(Cancel As Integer)
...
End Sub

I don't understand it. I tested it with argument "Cancel As Integer", but no change in field NickName. NickName is still empty, when I switch between records.

Does it work correct in your Access?

[pepok4]

------------------------
You really should change this >> Having the first name and last name in the same field is not good practice. It violates normalization rules.

I have the following code in a control source to automatically pick up the username of the person logged on to the PC.

Private Sub Form_Current()
    If IsNull(Me.User) Then
              Me.User = Environ("Username")
    End If
End Sub

Username of logged person is in this format: Peter.Smith
I want create specific NickName for this person, which contains from first character of FirstName and 5 character of LastName.

[June7]

The 'nickname' can be calculated from the username whenever it is needed. There is no requirement to save the nickname to table.

The code in the posted db works, after fixing the procedure declaration.

[qa12dx]

The 'nickname' can be calculated from the username whenever it is needed. There is no requirement to save the nickname to table..

that is the best option.
but if u really want to populate the feild then you can use 'after update' event in USER textbox to set value -item - control nickname. epxr - UCase(Left([User],1) & Mid([User],InStr([User],"."),100))

for the mid 2nd value i didn't use len([abc]) because why run another query calculating field lenght. i just put 100 and access will just go upto the last char

[pepok4]

The code in the posted db works, after fixing the procedure declaration.

I find how it works.

I had to modify code, but now it works as I wanted. I added button "Save" and then I used event procedure on click:

Private Sub Save_Click()
   Dim TestArray() As String

   If IsNull(Me.NickName) Then
        TestArray = Split(Me.User, ".")
        Me.NickName = UCase(Left(TestArray(0), 1) & Left(TestArray(1), 5))
   End If 
End Sub

Many thanks all for yours advices!!!