Code help needed 4

[MA04]

Hi all,

I have a code which checks in a textbox called: Name_input if it is null if so it exits procedure if entry has been made runs it. The procedure puts the entry into proper case.

I want the procdure to have an added condition to the isnull one, i want it to check if entry is already in proper case format is well, if it is then exit sub.

so something like:
if isNull(textbox) and textbox = Block 2 (below) then
exit sub
else
run Block 1 (below)

Here is the code i have so far:

Private Sub Name_input_Exit(cancel As Integer)
Dim Temp$, C$, OldC$, i As Integer
Dim prompt

[COLOR=Green]'condition for code to run at present[/color Green]
If IsNull(Me.Name_input) Then
    Exit Sub
Else

[COLOR=blue]'Block1
'----I want to be able to carry this block out if textbox entry is not null and <> block 2 below------[/color blue]

    prompt = MsgBox("Do you want the Name in proper case", vbYesNo, "Proper Case")
        If prompt = vbYes Then

[COLOR=red]'Block2
'--------------------I want to compare this region (Proper case) with textbox entry-----------[/color red]
            Temp$ = CStr(LCase(Me.Name_input))
  [COLOR=Green]          '  Initialize OldC$ to a single space because first
            '  letter needs to be capitalized but has no preceding letter.[/color Green]
            OldC$ = " "
                For i = 1 To Len(Temp$)
                    C$ = Mid$(Temp$, i, 1)
                        If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
                            Mid$(Temp$, i, 1) = UCase$(C$)
                        End If
                        OldC$ = C$
                Next i
[COLOR=red]'---------------------------------Block2 End--------------------------------------------------[/color red]
            [COLOR=Green]    'set name input textbox to proper case[/color Green]
                Me.Name_input = Temp$
        Else
            Exit Sub
        End If
[COLOR=blue]'--------------------------------Block1 End-----------------------------------------------------[/color blue]
End If
End Sub

Any help appreciated, thanks in advance,
M-.

 

[HarleyQuinn]

How about something like:

Option Compare Database

Private Declare Function GetThreadLocale Lib "KERNEL32" () As Long
Private Declare Function CompareString Lib "kernel32.dll" Alias "CompareStringA" (ByVal Locale As Long, ByVal dwCmpFlags As Long, ByVal lpString1 As String, ByVal cchCount1 As Long, ByVal lpString2 As String, ByVal cchCount2 As Long) As Long
Const CSTR_LESS_THAN = 1
Const CSTR_EQUAL = 2
Const CSTR_GREATER_THAN = 3
Const LOCALE_SYSTEM_DEFAULT = &H400
Const LOCALE_USER_DEFAULT = &H800
Const NORM_IGNORECASE = &H1
Const NORM_IGNOREKANATYPE = &H10000
Const NORM_IGNORENONSPACE = &H2
Const NORM_IGNORESYMBOLS = &H4
Const NORM_IGNOREWIDTH = &H20000
Const SORT_STRINGSORT = &H1000
Private Sub Command2_Click()
Dim hTL As Long
Dim Str1 As String
Dim Str2 As String
Dim prompt As String


If IsNull(Text0.Value) Then
Exit Sub
Else
    
    [green]'get the calling thread's current locale[/green]
    hTL = GetThreadLocale()
        
    Str1 = Text0.Value
    Str2 = StrConv(Text0.Value, vbProperCase)
    [green]'compare[/green]
    Select Case CompareString(hTL, NORM_IGNOREWIDTH, Str1, Len(Str1), Str2, Len(Str2))
        Case CSTR_EQUAL
            Exit Sub
        Case Else
        prompt = MsgBox("Do you want the Name in proper case", vbYesNo, "Proper Case")
            If prompt = vbYes Then
            Text0.Value = StrConv(Text0.Value, vbProperCase)
            Else
            Exit Sub
            End If
    End Select
End If
End Sub

Just replace Text0.Value with the name of your TextBox and see if it does what you want it to do.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Remou]

Hi
Perhaps you can use something from this:

Dim strName
Dim strNameProper
Dim Result
strName = "Mr Joe Bloggs"

strNameProper = StrConv(strName, vbProperCase)
Result = StrComp(strName, strNameProper, vbBinaryCompare)
Debug.Print Result

Result:
string1 is less than string2: -1
string1 is equal to string2: 0
string1 is greater than string2: 1
string1 or string2 is Null: Null

 

[HarleyQuinn]

Remou - forgot about StrComp .
Yours is much simpler and easier to understand

Harleyquinn

---------------------------------
For tsunami relief donations

 

[MA04]

Thanks guys for the help,

The first solution works exactly how i wanted it to the second will mean less code so i may employ that one.

M-.

 

[TheAceMan1]

How are ya MA04 . . . . .

[blue]I want the procdure to have an added condition to the isnull one, i want it to [purple]check if entry is already in proper case[/purple] format is well, if it is then exit sub.[/blue]

This is ambiguous [blue]as far operations are concerned[/blue]. If the code converts to Proper Case [blue]wether in proper format or not[/blue], it should be of no consequence . . .

Have a look at the [purple]StrConv[/purple] function, it converts to many formats (including proper case). If you can use it, your code would reduce to something like:

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   If Trim(Me!Name_input & "") <> "" Then
      Msg = "Do you want the Name in proper case?"
      Style = vbQuestion + vbYesNo
      Title = "ProperCase"
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         Me!Name_input = StrConv(Me!Name_input, vbProperCase)
      End If
      
   End If[/blue]

See Ya! . . . . . .

 

[ZmrAbdulla]

TheAceman1
I am curios... How to check if the text is already in propercase? If it is in propercase then no need messagebox popup, am I right?

Zameer Abdulla
Visit Me (New Look & style)

 

[MA04]

Hi Ace,

Thanks for the explanation i did not realise i could do that with StrConv, which makes life so much simpler. The reason why i want to have the condition to check if the textbox is already in proper case is because it runs more smoothley, if the user has already entered it in proper case and is tabbing through the form i do not want the user to get message MsgBox("Do you want the Name in proper case") if it does not lead to anything. (i.e. already in proper case). I think this is what ZmrAbdulla ment also?

M-.

 

[HarleyQuinn]

Zameer,

TheAceman1's code (as you know) is intended to bypass the checking of the case.
Please see mine and Remou's code for two different solutions to check if the string is propercase before you pop up the messagebox.

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[PHV]

If StrComp(Me.Name_input, StrConv(Me.Name_input, vbProperCase), vbBinaryCompare) <> 0 Then
MsgBox ...
...
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HarleyQuinn]

And now PHV's...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[MA04]

This is an alternative modified from Remou's

Dim Msg As String, Style As Integer, Title As String, Result, strNameProper
   
   strNameProper = StrConv(Me!Name_input, vbProperCase)
   If Trim(Me!Name_input & "") <> "" And Result <> StrComp(Me!Name_input, strNameProper, vbBinaryCompare) Then
      Msg = "Do you want the Name in proper case?"
      Style = vbQuestion + vbYesNo
      Title = "ProperCase"
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         Me!Name_input = StrConv(Me!Name_input, vbProperCase)
      End If
      
   End If

M-.

 

[MA04]

I don't think i have ever had replies with so many alternative solution to the same problem thanks guys,

M-.

 

[ZmrAbdulla]

This is why I like this forum!!

[COLOR=green]'Answer in minutes[/color]
Tek-Tips Answer = DateDiff("n", [QuestionDate], [AnswerDate]
[COLOR=green]'After years[/color]
Other Forums Answer = DateDiff("y", [QuestionDate], [AnswerDate])

Zameer Abdulla
Visit Me (New Look & style)

 

[TheAceMan1]

MA04 . . . . .

I fully understand why you want to check case first. Prompting the user in this case is simply something I wouldn't do! To have a consistent case column in view to me is desirable. So I would have no prompt, which would reduce the code to:

[blue]   If Trim(Me!Name_input & "") <> "" Then
      Me!Name_input = StrConv(Me!Name_input, vbProperCase)
   End If[/blue]

Now . . .

[blue]The reason why i want to have the condition to check if the textbox is already in proper case is [blue]because it runs more smoothley[/blue][/blue]

Consider that during conversion to proper case and in comparison for proper case, the string in question is parsed. If you note in all examples, one of the first things done is conversion to propercase!. So you have a parsed string on the conversion. Then a character by charcter parsing for comparison. The comparing before finally deciding an actual conversion, takes longer and gives you a more pronounced pause in your operations.

But ......... if you must!

See Ya! . . . . . .

 

[MA04]

Hi Ace

I orignally had code at one line as i had a function for propercase which i called, but problem arose when say name is not your usual for instance Mr. McDonald, Mr. Michael Wilson-Smithy would become Mr. Mcdonald and Mr. Michael Wilson-smithy. I had to give user option of ignoring proper case if need be.

M-.