Consulting

Results 1 to 4 of 4

Thread: UCase & Cancel

  1. #1
    VBAX Regular
    Joined
    Jan 2017
    Location
    Hudson
    Posts
    11
    Location

    UCase & Cancel

    Greetings all,
    I am hoping that someone here can provide a solution to an odd problem that I have run into with my project. I am using Access 2016 working on a single form. The form has four inputs and three buttons. Two of these are at the core of my issue.
    I have a text field, txtref, which I use to enter manual references into. I have code that converts any letters to capitals.
    Private Sub txtref_LostFocus()
    
    
    Dim t As String  'Raw inital string input from user.
    Dim e As String  'Final output
    
    
    If Not IsNull(t) Then
        t = Me.txtref.Value
        e = UCase(t) 'UCase forces any leters to be captilaized.
        Me.txtref.Value = e
    End If
    
    
    End Sub
    I am using a command button, cmdCancel, to abort an entry before it is saved.
    Private Sub cmdCancel_Click()
    
    
    Me.Undo
    DoCmd.Close , , acSaveNo
    
    
    End Sub
    The form has a description field which is auto populated from a previous form. (this function works perfectly) I have a combo box for publisher, and a combo box for reference type. Both of these work just fine. The cancel button is throwing me for a loop. When the form opens, if I click cancel, the form closes without any records being created. If I enter a response into the publisher combo box and click cancel, the form closes without any records being created. However if I enter responses into publisher and reference type combo boxes and tab into the reference number text box and click cancel I get an error for "Invalid Use of Null". I tried changing the code to a Len (t) > 0 Then, and this worked, however the UCase function stopped working. I tried using a message box and If and Else statements to determine if it was just me missing something. One message was for an empty text field and the other was for anything being entered into the text field. What I found is that no matter what I do, as soon as the txtref box has the cursor it think that there is text there. I know I'm close, but I can't seem to put my finger on what it is that I am missing to make the cancel function work at any time.

    Any help is appreciated. Thanks in advance.
    While testing the buttons on my form today I noted something interesting.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,203
    Location
    Private Sub txtref_LostFocus()
    
       .Value = UCase(.Value) ' If Value is null then UCase(Value) returns Null
    End Sub
    : but try
    Private Sub cmdCancel_Click()
    
    Me.UndoAction
    DoCmd.Close , , acSaveNo
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Jan 2017
    Location
    Hudson
    Posts
    11
    Location
    Well I'm feeling slightly relieved and a little silly. Thanks for the suggestion SamT. While your suggestion didn't solve the problem, it did lead me to the final solution. The variable in the If statement is what seems to have been tripping up the code. As soon as I realized that I had over complicated it and thus simplified it, everything worked like a champ. I will however be trying your UndoAction suggestion in other parts of the project. I'm curious to know what it will do.
    In the meantime though, in case anyone has a similar issue, the code below is my final solution.


    Private Sub txtref_LostFocus()
    
    
    Dim e As String  'Final output
    
    
    If Not IsNull(Me.txtref.Value) Then
        e = UCase(Me.txtref.Value) 'UCase forces any leters to be captilaized.
        Me.txtref.Value = e
    End If
    
    
    End Sub
    Thanks for the suggestion Sam, hope to hear from you on a different question.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,203
    Location
    I don't know why I left out the Me.txtRef in front of .Value
    Me.txtRef.Value = UCase(Me.txtRef.Value)
    Or, slightly faster...
    With Me.txtref
       .Value = UCase(.Value)
    End with
    Anyway, I'm glad you got it working
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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