Results 1 to 5 of 5
  1. #1
    MissaLissa is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    2

    Deleting data from form field not considered null?


    I have a form which contains a source of complaint field, last name field and first name field which when exported to excel are concatenated as such: Customer Service (Doe, Jane). However if the user accidentally enters a last name and/or first name and then goes back to delete it, the export to excel displays: Customer Service (,). So, I assumed that access was still seeing this as a field with data. To test this I created a select query with criteria as "is null" in the last name field and the record does not appear. Is there a way to actually make the field null after data has been deleted?

    One thing that may be affecting this is the following code I have on the form. I did remove the code and nothing changed, so I put it back.

    This is my original code:
    Code:
    Private Sub Source_Last_Name_AfterUpdate()
        If IsNull(Source_Last_Name) Then
            Source_Last_Name = ""
        Else
            Me.Source_Last_Name = fProperCase(Source_Last_Name)
        End If
    End Sub
    I also tried this, but it didn't make a difference:
    Code:
    Private Sub Source_Last_Name_AfterUpdate()
        If IsNull(Source_Last_Name) Then
            Source_Last_Name = vbNullString
        Else
            Me.Source_Last_Name = fProperCase(Source_Last_Name)
        End If
    End Sub
    Thanks... any help is appreciated.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What is the concatenated string that you are exporting to Excel? I assume it contains "Customer Service", but does it check for Null?

    Is there a way to actually make the field null after data has been deleted?
    If a user deletes data in a form textbox, it will be treated as Null in VBA, and it will also be treated as Null if a user types only a blank.
    However, setting the textbox to a blank or an empty string in VBA results in the textbox NOT being treated as Null in VBA.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    IIRC, VBA will consider a textbox as having an empty string if text is deleted from it. In earlier versions, you could assign an empty string to a table. IIRC, you cannot save an empty string to a table in newer versions of Access.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is there a way to actually make the field null after data has been deleted?

    One thing that may be affecting this is the following code I have on the form.
    Tracing through the code tells the story.

    You have
    Code:
        If IsNull(Source_Last_Name) Then
            Source_Last_Name = ""
        Else
            Me.Source_Last_Name = fProperCase(Source_Last_Name)
        End If
    So the code says:
    If the control "Source_Last_Name" is NULL then
    ....set the control "Source_Last_Name" value to an empty string
    else
    ..... change the control "Source_Last_Name" value to proper case
    end if

    I changed your code to
    Code:
        If IsNull(Me.Source_Last_Name) Then
            Me.Source_Last_Name = vbNullString
        End If
    
        MsgBox "Is Null: " & IsNull(Me.Source_Last_Name)
    Now enter a name in the "Source_Last_Name" control and move to the next control.
    The msgbox function should fire and say "False" meaning the control is not null (has a entry).
    Next delete the last name entry and move to another control.
    The msgbox function should fire and still say "False".

    Change the code to (lines commented out)
    Code:
    Private Sub Source_Last_Name_AfterUpdate()
    
    '    If IsNull(Me.Source_Last_Name) Then
    '        Me.Source_Last_Name = vbNullString
    '    End If
    
        MsgBox "Is Null: " & IsNull(Me.Source_Last_Name)
    End Sub
    Do the same steps:
    Enter a name in the last name control. The msgbox function should fire and say "False".
    Then delete the name. The msgbox function should fire and now say "TRUE".


    In effect, you were/are changing the NULL to an empty string (ie not null).


    ------------------------------------------------------------------------
    Maybe you could use
    Code:
    Private Sub Source_Last_Name_AfterUpdate()
        If Not IsNull(Me.Source_Last_Name) Then
            Me.Source_Last_Name = fProperCase(Source_Last_Name)
        End If
    End Sub
    or
    Code:
    Private Sub Source_Last_Name_AfterUpdate()
        If IsNull(Source_Last_Name) Then
            Source_Last_Name = "Unknown"
        Else
            Me.Source_Last_Name = fProperCase(Source_Last_Name)
        End If
    End Sub

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by MissaLissa View Post
    Is there a way to actually make the field null after data has been deleted?
    As John_G said, if the data is deleted, the Control/Field is Null! And not that you need it, here, but to 'make a field null,' can be done like making it equal to anything else

    Me.Source_Last_Name = Null

    But you don't really need any of this!

    Your Concatenation string is, no doubt, pretty standard, like this, the exact syntax depending on where it is done:

    Me.WholeName = Me.LastName & " ," & Me.FirstName

    and whether or not LastName, FirstName, or both are missing, the Comma will always show up. You're confused when you expect nothing to show up, if one or both are Null...that rule doesn't apply to Concatenation of Nulls...it applies to Addition of Nulls! Change the above to

    Me.WholeName = Me.LastName + " ," + Me.FirstName

    and the problem should go away...with the Comma only showing if the FirsName and LastName are both populated.

    BTW, as ssanfu said..."In effect, you were/are changing the NULL to an empty string..." and a Null and a Zero-Length String (i.e.empty string) are not the same thing!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 36
    Last Post: 08-30-2015, 01:39 PM
  2. inserting/deleting field in form by vba
    By joshynaresh in forum Forms
    Replies: 5
    Last Post: 09-14-2014, 04:06 PM
  3. Replies: 4
    Last Post: 04-06-2014, 10:01 AM
  4. Deleting data from a report using form
    By LonnieCAR in forum Access
    Replies: 9
    Last Post: 03-27-2014, 07:48 AM
  5. Replies: 1
    Last Post: 03-25-2012, 05:36 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