?
Solved

Need help with input mask written in VB code

Posted on 2003-03-05
4
Medium Priority
?
391 Views
Last Modified: 2012-06-21
I am using forms in my DB to update my records. The data is retrieved by these forms through an SQL query in the record source. Data is saved using a VB command button. Ultimately i want the record data that is saved to be converted from whatever format it is in to upper case for the first letter and lower case for the rest of the letters in the word. However, some fields may have two or more words in which i would like the first letter of each word in the field to be upper case. I would also like to "mc" and "mac" (such as in names) to be presented in the same manner only the first letter after these two or three characters should be upper case regardless of whether there is a space or not. e.g. McDonalds & MacDonald. This thread (http://www.experts-exchange.com/Databases/MS_Access/Q_11611458.html) sums up what i want pretty much, but because of the initial query used here i'm not sure if i could use it with my DB.

Preferably i would like to have a function that is called when a command button is clicked and then goes through applying the function/input mask to each of the text boxes on my form before executing the save record line of code.

AndrewMiller's code looks right or on the right track, but i'm unsure how to implement it.

Any help would be greatly appreciated! Thanks!

HTH

Andrew Miller

Option Compare Database
Option Explicit

Function ProperName(sName As Variant)

Dim iCount As Integer
Dim sReturn As String
Dim sPrevChar As String
Dim bFlag As Boolean

If IsNull(sName) Then sName = ""

bFlag = False
For iCount = 1 To Len(sName)
   If Mid(sName, iCount, 3) = "MAC" And (sPrevChar = "" Or sPrevChar = " ") Then
       sReturn = sReturn & "Mac"
       iCount = iCount + 2
       sPrevChar = Mid(sName, iCount, 3)
       bFlag = True
   ElseIf Mid(sName, iCount, 2) = "MC" And (sPrevChar = "" Or sPrevChar = " ") Then
       sReturn = sReturn & "Mc"
       iCount = iCount + 1
       sPrevChar = Mid(sName, iCount, 2)
       bFlag = True
   ElseIf sPrevChar = " " And Mid(sName, iCount, 1) = " " Then
       'do nothing
   ElseIf bFlag Or sPrevChar = "" Or sPrevChar = " " Then
       sReturn = sReturn & UCase(Mid(sName, iCount, 1))
       sPrevChar = Mid(sName, iCount, 1)
       bFlag = False
   Else
       sReturn = sReturn & LCase(Mid(sName, iCount, 1))
       sPrevChar = Mid(sName, iCount, 1)
       bFlag = False
   End If
Next
   
ProperName = sReturn

End Function
0
Comment
Question by:marzi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Alan Warren earned 60 total points
ID: 8077886
Function Proper(x)
'   Ganked from sample database NeatCode.mdb

'  Capitalize first letter of every word in a field.
'  Use in an event procedure in AfterUpdate of control;
'  for example, [Last Name] = Proper([Last Name]).
'  Names such as O'Brien and Wilson-Smythe are properly capitalized,
'  but MacDonald is changed to Macdonald, and van Buren to Van Buren.
'  Note: For this function to work correctly, you must specify
'  Option Compare Database in the Declarations section of this module.




Dim temp$, C$, OldC$, i As Integer
  If IsNull(x) Then
    Exit Function
  Else
    temp$ = CStr(LCase(x))
    '  Initialize OldC$ to a single space because first
    '  letter needs to be capitalized but has no preceding letter.
    OldC$ = " "
    For i = 1 To Len(temp$)
      C$ = Mid$(temp$, i, 1)
      If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
        Mid$(temp$, i, 1) = UCase$(C$)
      End If
      OldC$ = C$
    Next i
    Proper = temp$
  End If
End Function



hth
Alan
0
 

Author Comment

by:marzi
ID: 8084785
My form is made up of text boxes that display each field. Without clicking a 'save' button, a record can not be changed. Because of the way my db works i need be able to apply this mask to each text box before the record is saved. I need help with one of the following:
1) a method of applying the mask to each text box after clicking save (preferable).
2) a method of applying the mask to each text on a BeforeUpdate procedure.

I appreciate the help.

Thanks
0
 

Author Comment

by:marzi
ID: 8084952
My form is made up of text boxes that display each field. Without clicking a 'save' button, a record can not be changed. Because of the way my db works i need be able to apply this mask to each text box before the record is saved. I need help with one of the following:
1) a method of applying the mask to each text box after clicking save (preferable).
2) a method of applying the mask to each text on a BeforeUpdate procedure.

I appreciate the help.

Thanks
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8085332
on the afterupdate event for each textbox that contains string data


eg...
Private Sub Text1_AfterUpdate
 Text1.value = ProperName(Text1.value)

End Sub

hth
Alan
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question