Substring Issue, Need VBA Code Help

  • Thread starter Thread starter FA
  • Start date Start date
F

FA

I have an issue that i am currently working on and may be someone
have done something like this in the past or may be you how to handle
such situation.
I have a form called frmSystem which has two textboxes in my .mdb.
txtSYS_NME, and txtSYS_CODE . The data for txtSYS_NME is coming from
sql server DB which takes the data from excel sheet on a daily basis.
txtSYS_NME holds a string. sample data for it may be "People Soft
Managment Tool"
If thats the data in the txtSYS_NME i want to autopopulate txtSYS_CODE
on a Form Load so that it takes the first substring of each word from
txtSYS_NME and populate the textbox txtSYS_CODE. so for instance if the
txtSYS_NME.Value = " People Soft Managment Tool" Then i want to
autopopulate the textbox txtSYS_CODE with PSMT.
 
Hi, FA.

The following code will do as you wish

Dim i As Integer
Dim arrMyArray() As String
Dim strWS As String
strWS = "" ' Initialize working string

' Split function creates an array of substrings
' A space is the default delimiter
arrMyArray = Split(Me![SourceTextbox])

' Build new string
For i = 0 To UBound(arrMyArray)
strWS = strWS & Left(arrMyArray(i), 1)
Next i

' Assign to the control
Me![TargetTextbox] = strWS

You'll want to add error-handling. You can either call the code as an
event, or create a function that returns the working string.

Function MyAcronym() As String
...same code as before, except replace the last line with
MyAcronym = strWS
End Function

Then set the textbox' ControlSource to:

= MyAcronym

Hope that helps.
Sprinks
 
Back
Top