?
Solved

Need help with input mask written in VB code

Posted on 2003-03-05
4
Medium Priority
?
398 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
  • 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

593 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