MS Access programmatically edit a datasheet cell value

Question

I'm validating my datasheet programmatically because it's not possible to do it the "normal" way (linked tables). The "normal" way to tell the user they entered a badness is to punish them with MsgBox popups - I want to display a message in a label on my form (this works) and change the background color of the offending cell. I can only change cell colors through Conditional Formatting (this works), so I set up some rules to highlight cells with leading spaces.

So now the last piece of the puzzle is to change the value of offending cells with a leading space so it gets highlighted via Conditional Formatting. But that part isn't working. When I change the value of the cell, nothing happens. I feel as if my datasheet needs to be told to update itself with my new values.

Here's what I have so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim isValid As Boolean
    isValid = myDataSheetRow_IsValid()
    ' if the page is valid, don't cancel the update
    Cancel = Not isValid
End Sub

Private Function myDataSheetRow_IsValid() As Boolean
    ' validate our data as it cannot be validated in the GUI due to the way linked tables work
    myDataSheetRow_IsValid = True
    Dim myErrMsg As String
    myErrMsg = ""

    'evaluate my column called ABC
    If (IsNull(Me.ABC)) Then
        myErrMsg = myErrMsg + "ABC cannot be blank" + vbNewLine

        '---------------------
        'PROBLEM START
        '---------------------
        'Set the cell value to what it was plus a leading space
        'Because I have set up conditional formatting on the datasheet
        'So that leading spaces highlights the cell for attention

        Me.ABC= " " + Me.ABC 'trigger conditional formatting ??
        '---------------------
        'PROBLEM END
        '---------------------
    End If
    ' ... more validating ...
    ' done validating
    Call Me.Parent.UpdateErrorMsgArea(myErrMsg)
    If (Len(myErrMsg) > 1) Then
        myDataSheetRow_IsValid= False
    End If
End Function

How do I set the value of a datasheet cell programmatically?

I've tried it this way and it's not working - nothing is changed on the datasheet, it's as if the datasheet isn't updating itself to display my new programmatically changed value.

Answer 1

The code runs the following statement when Me.ABC is Null ...

Me.ABC= " " + Me.ABC

My impression is you expect that statement to store a space in Me.ABC, but it actually stores Null.

The reason is that adding Null to something gives you Null. It doesn't matter whether something is a number or a text string; adding Null still gives you Null.

However if you concatenate (with the & operator) Null with a text string, you get the text string back.

Me.ABC= " " & Me.ABC

But since you know Me.ABC is Null at that time, there is not really any benefit from concatenation. Assign just the space character ...

Me.ABC = " "