none
test for and prevent NULL passwords RRS feed

  • Question

  • A form (frmEditPasswords) is bound to tblLogin and has one textbox corresponding to each of tblLogin three fields. Each tblLogin field is NULL upon application start. When frmEditPasswords starts (on_Load), the string 'enter pwd' <no quotes> is placed in each textbox (value). The User is required to provide a (max) 10-character password in each textbox. An empty string IS allowed and should be considered valid.

    Upon entering the three passwords, the User would click SUBMIT button, the table would be updated with the textbox values, and the next form would be called. However, I cannot come up with the correct combination of event/code that will DISallow the User from executing the SUBMIT button code [update table/open new form] if any of the textboxes is NULL -- obviously preventing the form data from being saved and the next form called. The User is 'forced' to enter a password or an empty string!

    My searches turned up "NULL test" examples using LEN and NZ functions, but when I tried numerous (adapted) samples of code in my form's VB -- the tests fail to catch the NULLs and fail to stop the cmdSubmit_Click event.

    I did not think showing my incorrect code would be of any help. Rather, I'll plead for fresh assistance on the embarrassingly simple task described above.

    Your guidance would be greatly appreciated.

    Wednesday, August 31, 2016 1:24 AM

Answers

  • Since you allow empty strings, clearing the text box probably sets the value to an empty string instead of Null. So the tests for IsNull will fail.

    And if the code reaches Else, the values should be OK, so no need to display an error message.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by sfrvn Wednesday, August 31, 2016 12:06 PM
    Wednesday, August 31, 2016 11:34 AM

All replies

  • You could use code like this, substituting the correct names of course:

        If IsNull(Me.Password1) Then
            Me.Password1.SetFocus
            MsgBox "Please enter a password!"
            Exit Sub
        End If

    (Repeat for the other text boxes)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 31, 2016 5:54 AM
  • Hans...

    Thanks for taking the time to reply. Unfortunately, your suggestion did not work. I made the following changes:

    1. set each tblLogin field equal to 'enter pwd'  <no quotes

    2. Deleted frmEditPassword_onLoad code that sets value of each textbox to 'enter pwd'  <no quotes

    3. Modified my code to:

    Private Sub cmdSubmit_Click()
    On Error GoTo Err_cmdSubmit_Click
    
        Dim vbNotice, vbNotice1, vbNotice2, vbNotice3 As String
    
    '-------------------------------------------
    If tbxAdmin.Value = "enter pwd" Or tbxSupv.Value = "enter pwd" Or tbxUser.Value = "enter pwd" Then
        vbNotice = "You have not entered a password for one or more Groups." & vbCrLf
        vbNotice = vbNotice & "You MUST enter a password (or 'empty string') in each text box!" & vbCrLf
        vbNotice = vbNotice & "To specify 'No Password' for a Group, put 'empty string' in the text box." & vbCrLf
        vbNotice = vbNotice & "An 'empty string' is TWO double-quotes with NO space between them: """"" & vbCrLf
        vbNotice = vbNotice & "[An 'empty string' is NOT visible in the box when used!]" & vbCrLf
        vbNotice = vbNotice & "Otherwise, type a password in each text box and click Submit button." & vbCrLf
        vbNotice = vbNotice & "Passwords are limited to TEN characters."""
        MsgBox vbNotice, vbOKOnly, "Specify Group Passwords -- Program Initialization"
        Exit Sub
    ElseIf IsNull(Me!tbxAdmin) Then
            Me!tbxAdmin.SetFocus
            MsgBox "Please enter a password for Admin!"
            Exit Sub
    ElseIf IsNull(Me!tbxSupv) Then
            Me!tbxSupv.SetFocus
            MsgBox "Please enter a password for Supv!"
            Exit Sub
    ElseIf IsNull(Me!tbxUser) Then
            Me!tbxUser.SetFocus
            MsgBox "Please enter a password for User!"
            Exit Sub
    Else
        MsgBox "No Submit test failed"
        Exit Sub
    End If

    frmEditPasswords opens without error. Since the form is bound to tblLogin, each textbox displays 'enter pwd' -- the value of each table field. If the Submit button is clicked while any of the textboxes contains 'enter pwd', the long message IS displayed and execution stops. I put some random text in textboxes 2 and 3 and highlighted/deleted the value in textbox1. Clicking the Submit button results in message display: No Submit test failed.

    I'm baffled...  What am I overlooking? Can you provide some further guidance, Hans?

    Thanks... gary b

    Edit: Let me add that the table now has NULL for field 1 (Admin) and 'aaa' (my random characters) in the other two fields
    • Edited by sfrvn Wednesday, August 31, 2016 11:32 AM
    Wednesday, August 31, 2016 11:23 AM
  • Since you allow empty strings, clearing the text box probably sets the value to an empty string instead of Null. So the tests for IsNull will fail.

    And if the code reaches Else, the values should be OK, so no need to display an error message.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by sfrvn Wednesday, August 31, 2016 12:06 PM
    Wednesday, August 31, 2016 11:34 AM
  • Touche'

    Obviously, I did not consider the possibility that highlighting/deleting the textbox content (value) does NOT constitute a NULL for that textbox. I will test each textbox (on cmdSubmit_Click) for vbEmptyString -- just to confirm that an empty string is indeed placed in the textbox.

    I will report back with my findings, Hans. Again, thank you for your time and insight.

    gary b

    Wednesday, August 31, 2016 11:53 AM
  • Hans...

    You identified the issue! I added this code to further test tbxAdmin:

    ElseIf Me!tbxAdmin = vbEmptyString Then
            MsgBox "Admin value is empty string!"
            Exit Sub

    I put some random text in textboxes 2 and 3 and highlighted/deleted the value in textbox1 (tbxAdmin). Clicking the Submit button results in message display: Admin value is empty string!

    Frankly, I doubt if I would have discovered that "deleting" the bound textbox value actually replaces the value with an empty string. Thank you for pointing out what, for me, was NOT so obvious.

    Regards

    gary b

    Wednesday, August 31, 2016 12:05 PM