Pass a control to a sub...(nice Capitalization Code too..) 2

[snayjay]

I am making a form to enter User Name information and I'm using some Capitalization code that I got from this site years ago. (quite handy too... kudos to the author... I have no clue who it was - wasn't me though) Anyway, usually I don't have any problem using this code. But that's because normally I write the code after every field_onExit event that I wish to capitalize. So normally it's written several times on every form. But I'm learning more and more on vba coding and recently have learned to write my own sub routines. But passing this controlname to my sub is driving me crazy. It's always passing the value of the control and not the controlname. I've seen this same problem posted on other posts here, and I've tried all their recommendations but still no luck. Here is what I'm working with:

Sub capcode(MyControl As Control, MyString As String)
    Dim Temp$, c$, OldC$, I As Integer
    If IsNull(MyString) Then
        Exit Sub
    Else
        Temp$ = CStr(LCase(MyString))
        [COLOR=green]' Initialize OldC$ to a single space because first[/color]
        [COLOR=green]' letter needs to be capitalized but has no preceding letter.[/color]
        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
    End If
    With MyControl
        .Value = Temp$
    End With
End Sub

I call the routine like this...

Private Sub FN_BeforeUpdate(Cancel As Integer)
    Dim pnx As String [COLOR=green]'name to pass[/color]
    pnx = FN.Value
    Dim ctl As Control [COLOR=green]'control to pass[/color]
    Set ctl = Forms!NMEMfrm!FN
    capcode ctl, pnx
End Sub

I've changed the call line from:
Call capcode(ctl,pnx)
to
capcode ctl, pnx
because of another post I read saying that it's helps to keep the routine from reading the value vs. the control name.

Still doesn't work... any help would be greatly appreciated.

~Snay

 

[snayjay]

Sorry forgot to leave the error information...

Run-time Error '2115'
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field

Then it debugs on the line

    With MyControl
        [COLOR=black yellow].Value = Temp$[/color]
    End With

But if you hover over MyControl is shows it's passing the value ("John") and not the Control Name (FN)

Any help would be appreciated...

~Snay

 

[RoyVidar]

I don't think you can alter a control value in the before update event of the control - I think that would create a loop? This event is meant for validating purposes. Either you allow the entry, in which case you do nothing, or you do [tt]Cancel = True[/tt] to disallow the entry - then the cursor is placed in the control again, and you can try a new entry.

This, I think, is the reason for the message.

For passing control/value, I think the sub declaration takes precedence. For instance using

[tt]Private Sub FN_BeforeUpdate(Cancel As Integer)
capcode Me!FN, Me!FN
End Sub[/tt]

Would pass the control as the first arguement, cause that what the sub expects as first arguement, then the value of the control as the next arguement, because the sub expects a string. The .Value property of controls are the default property.

Using

Call capcode(ctl,pnx)
vs

capcode ctl, pnx

do exactly the same, which to chose, in my eyes are a matter of preference. For more info, have a read at CajunCenturions faq faq702-5309.

I'm surely misunderstanding this, but in a relevant event, wouldn't

Me!FN.Value = UCase(Me!FN.Value)

do the trick?

To the sub, a string can never be Null, but can be Zero Length String (zls), so this test

[tt]If Len(MyString) = 0 Then/tt]

will test for content. If you want to ensure the content isn't just a couple of spaces, you could

[tt]If Trim(MyString) = "" Then[/tt]

Roy-Vidar

 

[snayjay]

I'm going to check out what you said in just a sec, but I thought I'd answer "why the long code vs. the Ucase code" while I was thinking about it.

Reason is this code Capitalizes the first letter of each word in a string. Where UCase would capitalize the whole word. So with this code.. "the quick brown fox" turns to "The Quick Brown Fox"... but I like using this code for Names, because you always have a Van Buren or Mc Carty. The only thing that sucks is you wouldn't necessarily want a space like in 'Mc Carty' vs. 'McCarty'. So I usually put a checkbox on my forms to override the auto-cap code; in case I get one of those names.

Anyway, now I'll look at what you said to try a bit more.

~Snay

 

[RoyVidar]

I obviously didn't look very close at the functionality...

Some of us had a go at similar here thread702-1323150 - which will only capitalize the first letter of each sentence, but leave the rest.

Roy-Vidar

 

[snayjay]

Awesome... I followed the link to the FAQ you suggested and found the reference to...

MySubroutine a, (b) => a is passed By Reference and b is passed By Value

So I tried that and it worked. Plus I changed it to the OnExit event because of what you said about the BeforeUpdate Event, although most of what you said flew over my head faster than an F22; I did catch on to the fact that it probably wasn't the best place for that code

 

[misterstick]

er, you've duplicated an existing function, use StrConv() and ProperCase.

regards,




mr s. <

 

[snayjay]

Wow... that's awesome, didn't know about that function (obviously). Like I said, I pulled that code from this website years ago when I first registered. Been using it ever since. Guess it's time to replace it. Thanks again!

~Snay