Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37

    Unanswered: [RESOLVED] Changing lowercase to uppercase

    Greetings:

    I use the following vba code in an access 2000 database to change lowercase text to uppercase. It is applied to the “Lost Focus” event on each textbox. Is there a simpler way to achieve the same results w/o having to repeat the code so many times?
    Thanks in advance

    Mickey

    'Change lower to uppercase
    Dim upc As String
    upc = UCase(Location)
    Location = upc

    Resolution:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control
    On Error Resume Next

    For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Then
    ctrl.Value = UCase(ctrl.Value)
    End If
    Next


    End Sub

    Thanks Missinglinq
    Last edited by mshelley1; 01-24-07 at 22:19.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Do you want this for just for some controls on one form, some controls on more than form, or all controls?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    All controls on a single form.

    Thank you

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Hey, Mickey!

    Place this in the code module of your form and it'll loop thru all the textboxes and change the data to upper case when the record's saved.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctrl as Control
    
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then
            ctrl.Value = UCase(ctrl.Value)
        End If
    Next
    
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    Thank you so much for your help, however I am having a problem with this code. It seems that the .value is not an option in ctrl.Value. Any clue. I am using Access2000. sp3

    may be an id10t error

    Thanks

    Mickey

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    .Value is valid for any textbox control. Although I ususally work in Access 2000 I was using ver 2003 yesterday when I tested this code. Copy and paste back your code here so we can look at it, and explain what exactly what is happening, i.e. the text isn't being changed to upper case or are you getting an error message?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also, is this a bound form and are all the textbox controls enabled?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    Yes the form is bound to a table (flatfile) the code below was copied directly from the application.


    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Then
    ctrl.Value = UCase(ctrl.Value)
    End If
    Next


    End Sub

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't know what to tell you, Mickey! Your coed looks identical! Code still works here in Access 2000! As I said previously, you won't see the conversion until you save the record, i.e. until you, say, go to another record and then come back. You haven't mentioned any error messages. Your references have to be OK because you were already using UCase successfully. You might want to take a look at the table itself to see if the data has been converted to uppercase there. What kind of form is this;continuous/datasheet/single view? Not a subform, is it?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    Missinglinq:

    As I had mentioned earlier, it was in fact an id10t error, your code works wonderfully for me. Thank you very much.

    Mickey

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it figured out!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •