Elaborating on top of Base Captialize First Letter In Field
We write code to capitalize each new word in a name.
So user enters (complete) name into control “name” and routine capitalizes it:
oName.Text = < capitalized name >
BUT if to the user it does not seems good the format inserted, she/he must be able to edit it. And of course process cannot repeat itself (no another capitalization!).
What event(s) should be assigned to the routine?
Experimented here with Before update but of course process repeats itself after edition.
Thanks in advance.
the macro is set to capitalise both inserts and updates.
if you don’t wish to capitalise updated records then disable line 7 and enable line 8.
Capitalise.odb (12.5 KB)
My question is: suppose user inserts
“louis-claude de saint-martin”
code adjusts it to
“Louis-Claude De Saint-Martin”
But user wants it as
“Louis-Claude de Saint-Martin”
So user must be able to edit it before INSERT (saving to DB) record.
But user wants it as
“Blanquette de veau”
@Villeroy,
downloaded your attachment, looked at your macro and appreciate your contribution, it’s neat and tidy.
while lying in bed last night I did recall that Calc functions could be executed from basic but could not remember how.
my macro knowledge is pretty much limited to Base forms, beyond forms I improvise.
@CRDF : Use something like IF len(V(i)) > 2 THEN so only strings with 3 and more characters will be upper cased.
Alternative: Write some words, which shouldn’t be upper cased, in an array…
Capitalizing works fine. But after seeing the result, user may not want it as it is. So she/he must be able to edit it — without repeating the routine of course.
I’m trying to do it via 2 routines:
- Check if the TextBox is empty ("") so it sets a global flag.
- If flag = False (default situation) routine for capitalization runs.
But was not yet able to figure out what Events set to them.
So far…
REM: “to edit” in the sense of allowing user tweak the “capitalized” name.
Option Explicit
REM FLAG TO ALLOW FOR EDITING NOME
Global editar As Boolean
REM
Sub CapitalizeName(Evt As Object)
Dim TBNome As Object
Dim nome As String, nome_adj As String
Dim i As Integer
Dim nw As Boolean : nw = True ' flag for "new word"
'Globalscope.BasicLibraries.LoadLibrary("MRILib")
On Error GoTo Erro
REM CHECK FOR CAPITALIZATION
If Not editar Then
TBNome = Evt.Source
nome = TBNome.Text
For i = 1 To Len(nome)
If nw Then
nome_adj = nome_adj & UCase(Mid(nome, i, 1))
nw = False
Else
If Mid(nome, i, 1) = " " Or Mid(nome, i, 1) = "-" Or Mid(nome, i, 1) = "'" Then
nw = True
End If
nome_adj = nome_adj & LCase(Mid(nome, i, 1))
End If
Next i
TBNome.Text = nome_adj
editar = True
End If ' of edit(?) conditional
Exit Sub
Erro: MsgBox "ERRO " & Error & Chr(10) & Chr(13) & "na linha " & Erl
End Sub
REM ROUTINE TO ALLOW FOR EDITING Nome
Sub AllowENome(Evt As Object)
Dim TBNome As Object
'Globalscope.BasicLibraries.LoadLibrary("MRILib")
On Error GoTo Erro
TBNome = Evt.Source
If TBNome.Text <> "" Then
editar = True
print editar
End If
Exit Sub
Erro: MsgBox "ERRO " & Error & Chr(10) & Chr(13) & "na linha " & Erl
End Sub
as I said in my first post:
you can disable capitalisation when updating records by editing the macro to disable line 7 and enable line 8.
.
when adding a new record
input your names/words then hit either tab, return or down arrow
the record is saved and capitalised
if not happy then hit (shift + tab) or up arrow, the curser returns to the text box and you are free to edit the contents to your liking, no further capitalisation will occur.
.
bear in mind that when adding a new record the first character of the first word and subsequent characters which immediately follow a space are always capitalised.
if you wish to use hyphens then you could add a space before and after so “louis - claude de saint - martin” becomes “Louis - Claude De Saint - Martin”, not ideal I know.
.
all things considered it may be best if the user simply inputs their data exactly as required.
My routine does it all right with capitalization in names separated by " ", “-”, “’”.
But for an easy operation, the user must be able to adjust what she/he sees.
Problem: is there a way to find Events so this can be accomplished?
I was just elaborating over the thread Base Captialize First Letter In Field
IMHO no, as you already found you may create a loop:
What you can do is a bit more complex: Add a Message-Box inside the macro, where you present the transformed string, so user can make further changes.
Apologies for late answer. I’m out of town and when back will check it!
Thanks for now…
No doubt the solution!
When user wants to adjust, she/he do not hit F12. Very practical indeed 
Capitalise_3.odb (13.3 KB)
I penned this on Nov 21 but have held back until I received a response to my previous contribution.
.
during testing I noticed a glitch:
when an existing record was selected e.g.
“Olivia Newton john”
I hit F12 and the record was capitalised i.e.
“Olivia Newton John”
but the UNDO button was not activated, hence I amended the sub:
Sub GetWords(oEv as object)
'fired by 'Text Box 1' on key released
if oEv.keycode <> 779 then exit sub 'function key 'F12'
dim oControl as object
dim sTxt1$, sTxt2$
oControl = oEv.source
sTxt1 = oControl.text
sTxt2 = SplitString(sTxt1)
if sTxt1 <> sTxt2 then
oControl.text = sTxt2
oControl.model.boundfield.updatestring(sTxt2)
end if
End Sub