Results 1 to 7 of 7
  1. #1
    objNoob is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    21

    [SOLVED] Strange Procedural Problem in Access 2003

    I created a custom form for adding records to a database.

    On the form, I have a button which validates the textbox controls, and executes the SQL statement.



    When I open a the form and click the button to add a record without entering in data, I get an msgbox error that a textbox is empty. However, when I type something into that textbox, and then delete the text from that text box, I do not get the error. I should be receiving the empty textbox error!!

    It's as if Access is saving it's procedural 'point' after the first error, not allowing that segment of code to re execute until ALL of button block has been executed.

    I've tried error checking using txtBox1.text = "", txtBox1 = "", Len(txtBox1) = 0, and nothing is resolving my issue.

    Any suggestions?
    Last edited by objNoob; 03-17-2010 at 05:02 PM.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    This code should solve your problem. what I have done is that Is that I have declared a variable intLength Data type integer. This variable will store the value of the length of the data that I type in TextBox4. But the using only

    if len(Text4)=0 then will give me errors as when the text box is empty len function cannot calculate the length of a Null value. This code will even further solve your problem and will give you an error message if you delete the data from the text box and try to enter a new record.


    Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click
    Dim intLenght As Integer

    intLength = IIf(IsNull(Len(Me.Text4)), 0, Len(Me.Text4))
    If intLength = 0 Then
    MsgBox "Text Box is empty"
    End If
    Exit_Command7_Click:
    Exit Sub
    Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

    End Sub

    if this solves your problem mark the thread solved.

  3. #3
    objNoob is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    21
    Max, thanks for the reply, and I understand not being able to Len(Null).

    I actually tried a different approach, and it seems to have cleared it up. But it doesn't explain your Len(Null) theory.

    I originally had
    Code:
        If Len(txtJobNumber) = 0 Then
            MsgBox ("You have not provided a Job Number!" & vbCrLf & vbCrLf & "You cannot add a new record without this value.")
            Exit Sub
        End If
    which didn't work to find a blank text box. So I fiddled around with the logic and came up with:
    Code:
        If Len(txtJobNumber) > 0 Then
        Else
            MsgBox ("You have not provided a Job Number!" & vbCrLf & vbCrLf & "You cannot add a new record without this value.")
            Exit Sub
        End If
    And it finds a blank txtJobNumber control every time! It's weird I know. Is there any explaination to a Len()=0 of blank textbox control not resolving to true?

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Null values are like little demons they are neither zero nor is it equal to zero. Null is not even equal to itself. Thus you see using len function without compensating for nulls will return errors.There are many ways to handle Null and I am listing a few for you.

    If Len(Me.Text4 & vbNullString) = 0 Then
    MsgBox "Text Box is empty"
    End If

    Or

    If Len(Me.Text4 & "") = 0 Then
    MsgBox "Text Box is empty"
    End If

    vbNullString is a special VB constant that denotes a null string. The "" literal is an empty string.There's an important difference. An empty string is a real string. A null string is not. It is just a zero. With vbNullString your code is executed faster.

    another way of testing Null values is Nz

    If Nz(Me.Text4, 0) = 0 Then
    MsgBox "Text Box is empty"
    End If

    The Nz Function does not do anything with empty strings. It just passes along the "" value without a message, which can really cause problems, expecially if you are doing payroll and there should have been a name there! A string value of "" is called a Zero Length String. A zero length string is a string that has zero length.

    If the TextBox is Null, adding "" will make it equal "". If the TextBox contains a zero length string adding a zero length string to a zero length string will result in a zero length string. So this method checks for both nulls and zero length strings. But, unlike the Nz Function, you have to write more code to handle the value.

    If Me.Text4 & "" = "" Then
    MsgBox "Text Box is empty"
    End If


    all this codes are applicable to your problem. This are a few ways including the one that I hade sent previously to handle null values. I assume that yout problem is solved so please mark this thread solved.


    Let me explain how your code worked:
    when you included len(Me.Text4)>0 then your code will be executed only when length of the value in the text box is either 1 or more than 1. Thus when your text box value is null you receive your error message in the code. This is also a way to do it. Hope Now you understand my len(Null) theory (Wonderfully said)

    Mark this solved anyway

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Mark the thread solved.

  6. #6
    objNoob is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Posts
    21
    Quote Originally Posted by maximus View Post
    Mark the thread solved.
    I wish I knew how.

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Go to thread tools and select solved. If you still can't follow this link to learn how. Did you understand the strange Procedural Problem.
    https://www.accessforums.net/forum-s...lved-1828.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-10-2010, 04:53 AM
  2. access 2003 on vista Out of Memory Problem
    By skbrede in forum Access
    Replies: 0
    Last Post: 07-25-2009, 08:28 AM
  3. Strange issue with Access 2002 - Any suggestions?
    By mlopezkimco in forum Access
    Replies: 0
    Last Post: 07-30-2008, 09:25 AM
  4. Replies: 1
    Last Post: 09-06-2006, 11:48 AM
  5. Replies: 1
    Last Post: 04-05-2006, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums