MS Access programmatically edit a datasheet cell value -

i'm validating datasheet programmatically because it's not possible "normal" way (linked tables). "normal" way tell user entered badness punish them msgbox popups - want display message in label on form (this works) , change background color of offending cell. can change cell colors through conditional formatting (this works), set rules highlight cells leading spaces.

so last piece of puzzle change value of offending cells leading space gets highlighted via conditional formatting. part isn't working. when change value of cell, nothing happens. feel if datasheet needs told update new values.

here's have far:

private sub form_beforeupdate(cancel integer)     dim isvalid boolean     isvalid = mydatasheetrow_isvalid()     ' if page valid, don't cancel update     cancel = not isvalid end sub  private function mydatasheetrow_isvalid() boolean     ' validate our data cannot validated in gui due way linked tables work     mydatasheetrow_isvalid = true     dim myerrmsg string     myerrmsg = ""      'evaluate column called abc     if (isnull(me.abc))         myerrmsg = myerrmsg + "abc cannot blank" + vbnewline          '---------------------         'problem start         '---------------------         'set cell value plus leading space         'because have set conditional formatting on datasheet         'so leading spaces highlights cell 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)         mydatasheetrow_isvalid= false     end if end function 

how set value of datasheet cell programmatically?

i've tried way , it's not working - nothing changed on datasheet, it's if datasheet isn't updating display new programmatically changed value.

the code runs following statement when me.abc null ...

me.abc= " " + me.abc 

my impression expect statement store space in me.abc, stores null.

the reason adding null gives null. doesn't matter whether something number or text string; adding null still gives null.

however if concatenate (with & operator) null text string, text string back.

me.abc= " " & me.abc 

but since know me.abc null @ time, there not benefit concatenation. assign space character ...

me.abc = " "