Capitalize Every First Letter Of Each Word - W/O VB Script? 1

[scottdev]

I have no experience with VB. Isn't there a way to just set the format property in the table field similar to the way it can be set to capitalize all letters (">&quot - but have it capitalize the first letter of each word in a particular field? If not I guess I need to learn some VB and where and how to place the code in Access so I can save my fingers and wrists from all the data I'm trying to enter.

 

[Cleis]

Hi:

Try this.

StrConv([Your Field Name},3) This will convert the first letter of every word to a Cap.

StrConv([Your Field Name,1) This will convert all letters to lower case.

StrConv([Your Field Name,2) This will convert all letters to Upper Case.

I hope this Helps!!!


Rich

Chicago

 

[scottdev]

Thanks for the tip.

Pardon me for sounding - well - uneducated, but does that code go in the format field for the field I'm trying to modify? I added StrConv([Title},3) - then Access converted it to s"tr"c\on"v([Title},3)". I saved the table, but no capital letters at all.

 

[Lightning]

Scottdev

Sorry, but you're going to need some VB code to do this. But the good news is that the code is very easy to write.

1. Open the form in Design view

2. Select the textbox that you want to capitalise and open its Property Box.

3. Scroll through the property list until you come to the "After Update" property. Click once with the mouse in the "After Update" property field. When the ellipse (...) is displayed, click on it. A dialog box will open with three options: "Expression Builder", "Macro Builder" and "Code Builder". Double click on Code Builder to open the code window.

4. When the code window opens, it will display the following:

Private Sub YourFieldName_AfterUpdate

End Sub

5. Between the Private Sub and the End Sub statements, type the following (substituting the correct field name for YourFieldName):

Private Sub YourFieldName_AfterUpdate
YourFieldName = StrConv(YourFieldName, VBProperCase) <--Type this line
End Sub

6. Close the code window and open the form in form view. Every new word you type into the field should now be capitalised after you tab out of the field (NOTE: This will NOT capitalise any existing data).

You should note that this function will not work for names such as McDonald or O'Connor. You would need to type them as Mc Donald and O' Connor respectively.

HTH
Lightning

 

[scottdev]

My fingers thank you! Saved a lot of time. Guess I'm going to need to get ramped-up on VB.

If I want Access to automatically add an item entered in a field that's not in the field selection list already (from a linked table with a drop-down list), would I use VB for that?

 

[Lightning]

Try this code. It should be put into the combo box's &quot;Not In List&quot; event.

Private Sub Company_NotInList(NewData As String, Response As Integer)
'This event procedure is called from the
'ComboBox's NotInList event

'The procedure adds the new data to the
'underlying table upon confirmation from the user

'Does user want to add new value to list?
If MsgBox(&quot;Add &quot; & NewData & &quot; to list?&quot;, 33, &quot;Company Name&quot = 1 Then

'Inform Event Procedure we're handling the error
Response = DATA_ERRADDED

'Declare the database and Table
Dim NewCompany As String
Dim db As Database
Dim TB As Recordset
Set db = CurrentDb

NewCompany = NewData
'Open the required table:
Set TB = db.OpenRecordset(&quot;tblCompany&quot;, DB_OPEN_TABLE)

'Prepare Table for new record being added
TB.AddNew

'Write Data to fields
TB(&quot;Company&quot = NewCompany
TB.Update
TB.Close


Else
'Cancel the event returning the user to
'the combo box
DoCmd.CancelEvent

'inform the event procedure to ignore
'errormessage
Response = DATA_ERRCONTINUE
End If

End Sub

Of course, you will need to change the Table and Field names to your table and field names.

Basically, this code runs when the data entered into the combo box does not match anything in the list. It then pops up a message asking the user if they want to add a new entry, and gives them the option to cancel if they have made a keying error or decide not to add the entry. If the user answers Yes, it opens the appropriate table in the background, adds the new entry to the field, updates the table and closes it again. The user doesn't see any of this of course, they just see the new entry appear in the list.

HTH
Lightning