Validation Rule to Capitalize Only First Letter

[wes9659]

I know that the following Validation Rule would require all Entered Last Names to be in Uppercase letters (i.e. REAGAN) StrComp(UCase([LastName]),[LastName],0) = 0



But how would Validation Rule look to only capitalize the first letter only? (i..e. Reagan)

[ItsMe]

StrConv("REAGAN", vbProperCase)

[wes9659]

I tried the code as a validation rule and it did not work received errors. What would be the proper way from the main data table to force it to convert to proper text even if I did not force a validation rule? Even tired wild card. How would I just get Access 2010 to change input atomically? Or is there another way to force compliance of using proper text. Novice here baby steps please

[ItsMe]

All I did was show an example of the StrConv() function. I use it in VBA. It may work in a query too, I do not know.

I am not sure what a validation rule in a table is. Aside from limiting the number of characters in a text field I do not do much at the table level. I manage constraints with forms and VBA.

If a user types into a bound textbox control on a form, you can use an after update event to convert the text inside the text control and this will be displayed in the relative field within the table. You can apply VBA to unbound controls and forms too. I am just explaining how to directly affect change to a field in a table.

You can include VBA behind a form via the VBA editor. To create a new sub procedure for a click event....

While in Design View of the form,

click to highlight the textbox control
Go to the Property Sheet and locate the On After Update event within the "Event" tab
Click the ellipses (...) next to the desired event
Select the "Code Builder" option

The cursor will be placed in the middle of your new After Update event
Paste the following as a placeholder for you future code
'Type code below this line and before the End Sub line

Here is your After Update VBA code for a textbox named, "txtFirstName"

Me.txtFirstName.Value = StrConv(Me.txtFirstName.Value, vbProperCase)

As for the validation thing at the table layer.... I am not familiar with the strComp() function but you can replace the UCase() function with the StrConv() function so I would interpret and change your original code to
(i.e. REAGAN) StrComp(StrConv([LastName],3),[LastName],0) = 0

Notice I changed the vbpropercase to 3

This may be needed if using the function somewhere constants are not recognized for the integer they represent.

I believe the following is correct
vbUpperCase = 1
vbLowerCase = 2
vbProperCase = 3

[wes9659]

I went to Design View and selected property sheet. Under Event Table Tab I chose After Update than hit ....... to right chose Code Builder and entered

Private Sub LastName_AfterUpdate()
Me.LastName = StrConv(Me.LastName, vbProperCase)
End Sub

Now when someone enters all lower case or all upper case it automatically converts it over to Proper case in corresponding table as well as associated form. Thanks all for the help