ProperCase in Form (Access 2000)

Topic

New Reply

I have been reading on changing already typed fields into ProperCase, I read the thread below:
If using StrConv function in a query expression, use the vbProperCase constant’s numerical value rather than named constant, e.g.:

StrConv([FLD_NAME],3)
The Object Browser will provide the numerical equivalent for named constants, e.g. Const vbProperCase = 3.

for an update query, but don’t know what the 3 is for…(such a VBA baby)…not sure about the numerical input here or why. Is there any EASY way to convert typed text in a form field to make it Proper case in Access (know how in Excel)…but in Access that doesn’t require a lot of VBA…i have trouble placing functions…
thanks so very much
Nannette

Reply | Quote

Replies

Don’t think there’s any way to do this w/o using VBA – there’s no symbol you can place in Format property on control’s property sheet that will convert text to proper case the way > symbol converts text to upper case or < to lower case. You can use textbox's After Update event to convert text entered to proper case. Example:

Private Sub CustomerID_AfterUpdate()
Me.CustomerID = StrConv(Me.CustomerID, vbProperCase)
End Sub

That's about as simple as it's gonna get in this case.

HTH

Reply | Quote

I keep getting this same error…with the strConv…it is the same thread I read a while ago, and its not working. My field is Last Name, take note of the error given. Do you have any advice on this message for me.

Reply | Quote

A routine like that doesn’t cause any problems on my A2k installation. Your form might be corrupted, but you could try making sure that the control name you’re working with is different from the name of the underlying field and see if that makes a difference. I tried it both ways on my machine without an error either way. BTW, you do have at least O2k SR1 installed, don’t you?

Reply | Quote

You might check for missing references. In the Visual Basic Editor, select Tools/References… If you see an entry beginning with MISSING, uncheck the corresponding check box and click OK; the problem may have vanished then. Otherwise, I wouldn’t know – code like that works OK for me.

Reply | Quote

I use the following code with no problems. (A2K SR1)
Notes:
txtLastName is the control – LastName is the underlying field
It intercepts a Null value (for when the textbox has been cleared out)

Private Sub TxtLastName_AfterUpdate()
     On Error Resume Next
     Dim strLName As String
     If IsNull(Me![TxtLastName]) Then
         Exit Sub
     Else
         strLName = Me![TxtLastName].Text
         Me![LastName] = StrConv(strLName, vbProperCase)
     End If
End Sub

Reply | Quote

Alrighty then…a programmers worst nightmare…i had a mispelling. The database fieldname i was working on had a space in it…I removed the space, made sure the code also had no space…and the method you gave me here worked just fine. Thank you and thank you again……

Private Sub CustomerID_AfterUpdate()
Me.CustomerID = StrConv(Me.CustomerID, vbProperCase)
End Sub

NMPadgett

Reply | Quote

This is a snippet of code that I use for converting to Proper. I didn’t write the code, I downloaded it a long time ago from http://www.mvps.org. Dev Ashish posted it, though his name was not in the documented code. I hope this helps.

‘******************* Code Begin ****************
Function Proper(X)
‘ 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$ “z”) Then
Mid$(Temp$, i, 1) = UCase$(C$)
End If
OldC$ = C$
Next i
Proper = Temp$
End If
End Function
‘******************* Code End ****************

Ken

Reply | Quote