Access 2019 - Using more than one word in a Form text field - First letter to be uppercase (1 Viewer)

[Bluesman]

I hope someone can help me, I'm not a coder but many moons ago learnt Access from college course. I'm currently trying to refresh my knowledge from that time ago and started building a database (Access 2019). The problem I have is to do with a form field data entry for text 'Supplier:', it must allow more than one word i.e. two or three words.
I want each the first letter of each word to start with an uppercase and then continue with lowercase using a space to break for the following word and so on to change after tab to the next , I've looked at input masks but I don't think it will do what I want.

Any ideas on a piece of code, and where do I put it?

Many thanks

Mike

 

[theDBguy]

Hi. Welcome back to AWF!

You can look into the StrConv() function using the vbProperCase argument.

 

[arnelgp]

use Change event of the textbox:

Private Sub Text0_Change()
Dim sText As String
sText = Me.Text0.Text & ""
If Len(sText) <> 0 Then
    sText = UCase(Left$(sText, 1)) & Mid$(sText, 2)
    With Me.Text0
        .Value = sText
        .SelStart = Len(sText)
    End With
End If
End Sub

 

[MarkK]

Combining @theDBguy's correct suggestion, with @arnelgp's excellent code, you get your solution...

Private Sub Text0_Change()
    With Me.Text0
        If Len(.Text) Then
            .Value = StrConv(.Text, vbProperCase)
            .SelStart = Len(.Text)
        End If
    End With
End Sub

 

[MarkK]

Actually, if you refactor as follows....

Private Sub Text0_Change()
    Dim start As Integer
    With Me.Text0
        start = .SelStart
        .Value = StrConv(.Text, vbProperCase)
        .SelStart = start
    End With
End Sub

... you can also insert text in the middle of the string.

 

[Bluesman]

Actually, if you refactor as follows....

Private Sub Text0_Change()
    Dim start As Integer
    With Me.Text0
        start = .SelStart
        .Value = StrConv(.Text, vbProperCase)
        .SelStart = start
    End With
End Sub

... you can also insert text in the middle of the string.

use Change event of the textbox:

Private Sub Text0_Change()
Dim sText As String
sText = Me.Text0.Text & ""
If Len(sText) <> 0 Then
    sText = UCase(Left$(sText, 1)) & Mid$(sText, 2)
    With Me.Text0
        .Value = sText
        .SelStart = Len(sText)
    End With
End If
End Sub

Sorry guys I couldn't get it to work! I used the 'Change' event in my field named 'Supplier', have I got to edit any part of the code?

 

[theDBguy]

Sorry guys I couldn't get it to work! I used the 'Change' event in my field named 'Supplier', have I got to edit any part of the code?

Just a suggestion, try using the AfterUpdate event.

Me.TextboxName = StrConv(Me.TextboxName, vbProperCase)

 

[MarkK]

Yes, you need to edit the name of the control...

Private Sub YourControlNameHere_Change()

 

[Pat Hartman]

theDBguy rocks

 

[Bluesman]

Yes, you need to edit the name of the control...

Private Sub YourControlNameHere_Change()

I've done all you guys have told me, tried all edits but it still doesn't work, I would say the fault is with me as I'm not a coder so my full understanding is lacking in this area, I'll leave it for now but thank you all very much for your time and effort.

 

[theDBguy]

I've done all you guys have told me, tried all edits but it still doesn't work, I would say the fault is with me as I'm not a coder so my full understanding is lacking in this area, I'll leave it for now but thank you all very much for your time and effort.

Hi. Sorry to hear that. If you need more help, consider posting a sample db with test data, so we can show you how to do it. Cheers!

 

[MarkK]

Think about it. "Your Control Name Here." How can you edit the code so it works. What is the name of -->>YOUR<<-- control?

 

[Bluesman]

Many thanks, attached is a sample form of my problem, along with the samples of your code that I have edited.

 

[arnelgp]

see code of the form.

 

[Bluesman]

Hi, the 'Text Box Problem' example works fine, so I did a copy & paste of the code into my database project 'Supplier' field - event - On Change, when I started entering the data nothing happened, so I opened the code and the attached Error message appeared! I don't know why because it works fine in the example? Is there anything else I should have done?

 

[arnelgp]

check the Name of the textbox in design view of your form.
remove all your code from the change event of that textbox.
and on the property->event->change, choose Code Builder.

 

[Pat Hartman]

On Change,

This event runs multiple times. It runs once for each character entered therefore, it is not the correct event for this problem. Use the AfterUpdate event as theDBguy suggested.