Help with VB Loop

[rotorque]

Hi

I wondered if you would be good enough to help me.

As learning exercise I have set-up a table with two fields Fld1 and Fld2 both text 50 characters.

I wanted to place four words in fld1 and using VB – BeforeUpdate on a form I wanted to take the first character of each word and place it in Fld2 separating each letter by a space. For example in fld1 I would place:

How Now Brown Cow

And expect to see in fld2

H N B C

The idea was to learn more about loops (creating codes for long naratives etc) and variables in VB (naturally I am new to VB)

In previous languages I have used values in numerical fields and strings can be incremented by adding them to themselves.

I have gotten a little punch drunk now looking at this an I'm not really sure where it's going any more.

Can you help please?

Thanks

Ro

Private Sub Fld1_BeforeUpdate(Cancel As Integer)

Dim varFld1 As String
Dim varFld2 As String
Dim varSpace As String
Dim varPos As Integer
Dim varFld1Len As Integer
Dim varNum As Integer

varSpace = " "
varNum = 1

'Value in variable
varFld1 = Fld1
'Overall length of text
varFld1Len = Len(varFld1) - 1

Do Until varNum >= varFld1Len
'placed the first character from fld1 into fld2
If varNum = 1 Then
varFld2 = Left(varFld1, varNum)
Else
'finds the next character
varFld2 = Mid(varFld1, varNum, 1)
'places the next charater in the string separated by a space. Does not seem to concatenate the value this way
varFld2 = [varFld2] & " " & [varFld2]
End If

varPos = InStr(varNum, varFld1, varSpace)
varNum = varPos + 1

Loop
Me.Fld2 = varFld2
End Sub

 

[pbaldy]

I've got to run, but I'd go at it a different way. You can use the Split function to split the 4 words into an array, then step through the array and use the Left function to grab the first character. It would be more dynamic in allowing more or fewer words. You should find info on Split in VBA help.

 

[rotorque]

Ok will have a look, but could I do it this way for any number of words - i.e 2 or 35 or 7 or 15 etc?

Many thanks

Ro

 

[pbaldy]

Yes, which is why I said:

It would be more dynamic in allowing more or fewer words.

 

[rapsr59]

This works!

Public Function fncReturnFirstLetters(str) As String
 
    Dim COUNTER As Long
    Dim Length As Long
    Dim NewString As String
    Dim FlagNextWord  As Integer
 
    'Initialize variables
    str = Trim(str) 'Remove leading and trailing spaces
    FlagNextWord = True 'Initialize for first character
    NewString = ""
    Length = Len(str) 'Set Length to Length of str
    COUNTER = 0 'Initialize
 
    'Loop adding first character of each word and space
    Do While COUNTER < Length
        COUNTER = COUNTER + 1
        If FlagNextWord Then
            'First letter of each word and space
            NewString = NewString & Mid(str, COUNTER, 1) & Chr(32)
            FlagNextWord = False
        Else
            'Space between each word - set flag
            FlagNextWord = Asc(Mid(str, COUNTER, 1)) = 32
        End If
    Loop
 
    NewString = Left(NewString, Len(NewString) - 1) 'Remove last space char
 
    fncReturnFirstLetters = NewString 'Return to calling procedure
 
End Function

Use this code to call fncReturnFirstLetters after entering sentence, phrase or words in fld1

     fld2 = fncReturnFirstLetters(fld1)

Hope this helps

Richard

 

[rapsr59]

If you want to make sure that all the characters come out capitalized (Upper Case) then use...

NewString = NewString & UCase(Mid(str, COUNTER, 1)) & Chr(32) 'Capitalize and add space

Have fun

Richard

 

[rotorque]

Thank you so much for taking the time to do this. This is really helpful.

However in looking in to this since I made the first post, I have discovered more fundermental gaps in my knowledge (not insummountable). I just launched into Access a couple of weeks ago with no previous exprience of it thinking that I would be able to master things as I went along. It seemed reasonable to me that all vb code worked directly off the field on the form, so for example with my fld1 all I had to do was to go to the event builder... and start coding under the heading....
Private Sub Fld1_BeforeUpdate(Cancel As Integer)

However, I see that your code starts...

Public Function fncReturnFirstLetters(str) As String. Is this held as a module?

In short where do I put this code on the field or in the modules area or..?

 

[pbaldy]

Looks like Richard is offline. While much is done directly from the appropriate events, you can also create functions that can be called from anywhere. That way you don't have to have the same code all over the place. His code would go in a standard module as opposed to a form module. You can then call it from anywhere in code, a query, form or report.

I still think Split() would be more efficient. If it slows down this afternoon maybe I'll do some testing.

 

[rapsr59]

Hi Rotorque,

Quote: In short where do I put this code on the field or in the modules area or..?

I hope you do not find this overkill.

Open A new form.

Create a List Control and name it fld1
Right click fld1 and select Properties.
Click on the Event Tab.
Double click the After Update event and access will fill in “[Event Procedure]” (without the quotes) for you.
Click on the triple eclipse (…). This will open the forms module. You will probably see “Option Compare Database” and “Option Explicit” You are now in the forms module where you write procedures known as subs and functions. Note that you have created a sub procedure called fld1_AfterUpdate. Type “fld2 = fncReturnFirstLetters(fld1)” (without the quotes) as shown below. The code in the forms module should look like this.

[FONT=Times New Roman][SIZE=3]Option Compare Database[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Option Explicit[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Private Sub fld1_AfterUpdate()[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        [COLOR=black]fld2 = fncReturnFirstLetters(fld1)[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

Next create the function fncReturnFirstLetters.

At the top of the Microsoft Visual Basice window you will see the option Insert. Click this option and select Procedure. In the Add Procedure dialog window Select Function. In the Name control type “fncReturnFirstLetters” (without the quotes). Click OK.

Your code will look like this.

[FONT=Times New Roman][SIZE=3]Option Compare Database[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Option Explicit[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Private Sub fld1_AfterUpdate()[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        [COLOR=black]fld2 = fncReturnFirstLetters(fld1)[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Public Function fncReturnFirstLetters()[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

Next edit the function so it looks like this.

[SIZE=3][FONT=Times New Roman]Public Function fncReturnFirstLetters(str) As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Dim COUNTER As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim Length As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim NewString As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim FlagNextWord  As Integer[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]'Initialize variables[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]str = Trim(str) 'Remove leading and trailing spaces[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FlagNextWord = True 'Initialize for first character[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]NewString = ""[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Length = Len(str) 'Set Length to Length of str[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]COUNTER = 0 'Initialize[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]'Loop adding first character of each word and space[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Do While COUNTER < Length[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    COUNTER = COUNTER + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    If FlagNextWord Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        'First letter of each word and space[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        NewString = NewString & Mid(str, COUNTER, 1) & Chr(32)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        FlagNextWord = False[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        'Space between each word - set flag[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        FlagNextWord = Asc(Mid(str, COUNTER, 1)) = 32[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]NewString = Left(NewString, Len(NewString) - 1) 'Remove last space char[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]fncReturnFirstLetters = NewString 'Return to calling procedure[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

Now close the Microsoft Visual Basic window.

Next Create a second List control and name it fld2

Save your form naming it whatever you like. Open the form and type in…

How Now Brown Cow! In fld1 and hit Enter and…

H N B C will appear in fld2.

I hope the above provides an adequate answer to all of your needs.


Richard

PS. Yes this function is dynamic. It allows as many or as few words as your controls will allow.

 

[rapsr59]

Hi rotorque, pbaldy and all

I was intrigued with pbaldy’s post concerning the Split function. What made it so intriguing was the fact that I had never heard of it. So I decided to look in the help files for help on the Split function. I did not find an example, which for me, made it a monumental task to figure out how to extract each word from the Array. Finally, after “Playing” with it for several hours and encountering error # 9 “Subscript out of range”, I clicked on the help button and I was led to the answer.

“You referenced a nonexistent collection member.”
“Try using the For Each …Next construct instead of specifying index elements.”

So, this is what I came up with using the Split function. Actually I like this better than my originally post. Thanks to pbaldy!

[FONT=Times New Roman][SIZE=3]Public Function fncReturnFirstLetters(str) As String[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman]         Dim ArrayWords As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Dim T As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Dim strLetterAccum As string[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         Initialize variables[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         T = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         ' Remove leading and trailing spaces and fill array[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         ArrayWords = Split(Trim(str), " " )[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]        ' Loop adding the first character of each word in the array[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         For Each element In ArrayWords[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                     ' Add first character and a space[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                     strLetterAccum = StrLetterAccum & Left(ArrayWords(T), 1) & " "[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                     T = T + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Next[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         ' Remove trailing space and return to calling procedure [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         fncReturnFirstLetters[COLOR=black] = Trim(strLetterAccum) [/COLOR][/FONT][/SIZE]
 
[COLOR=black][SIZE=3][FONT=Times New Roman]End Function[/FONT][/SIZE][/COLOR]

Thanks to pbaldy for the inspiration and the learning exercise. After all "There is more than one way to skin the cat!"

Richard

 

[rotorque]

This is really excellent. I had a look at this and could not get my head around arrays i.e.; how they are defined and accessed. I'd really like to clarify this.
As I read the above code - the value is collected in the "fncReturnFirstLetters" but I can't understand how this is put into the accummulator.

 

[pbaldy]

Glad it helped out. We're all always learning, as you found a way to step through the array I'd never seen. I do this type of thing (this was a sample to break out email addresses from a string delimited with semi-colons):

  Dim astrAddy()    As String
  Dim X             As Integer

  astrAddy = Split(Me.txtEmailAddys, ";")

  For X = LBound(astrAddy) To UBound(astrAddy)
    Debug.Print astrAddy(X)
  Next X

rotorque, I'm not sure what you mean by "accumulator". You can call that function from anywhere. If you just wanted to display the value on a report, you could have a textbox with a control source of:

=fncReturnFirstLetters(FieldName)

 

[rotorque]

Glad it helped out. We're all always learning, as you found a way to step through the array I'd never seen. I do this type of thing (this was a sample to break out email addresses from a string delimited with semi-colons):

  Dim astrAddy()    As String
  Dim X             As Integer
 
  astrAddy = Split(Me.txtEmailAddys, ";")
 
  For X = LBound(astrAddy) To UBound(astrAddy)
    Debug.Print astrAddy(X)
  Next X

rotorque, I'm not sure what you mean by "accumulator". You can call that function from anywhere. If you just wanted to display the value on a report, you could have a textbox with a control source of:

=fncReturnFirstLetters(FieldName)

Sorry pbaldy, "accumulator" just illustrates my lack of familiarity with this product and I'm harking back to a previous life developing with older products. I was trying to ask about arrays! I will continue to read on with interest.

Thanks


Ro