Delete field not whole record

[Dave Hawks]

I am trying to delete the text in one specific field in a record but leave
the others.
The fields are displayed on a Form frm Table 1 and each record has 5 fields
fldTime, fldPlayer1,fldPlayer2, fldPlayer3, fldPlayer4.
The decision to delete is based on an If Then Else Procedure run on a
different form frmAuthenticate.
The frmTable 1 is open with the focus on the field I am trying to delete and
frm Authenticate is opened by the before update event if the existing entry
has been deleted .
I have tried using a
DoCmd.RunSQL "Delete Table1.Player3 From Table1"
after the else statement, but this deletes all records from the table.
Is this the best way to tackle the problem ,and if so how do I limit
deletion to the correct field.
Thanks for your help

 

[e.mel]

Just set the field to an empty string:
fldPlayer3 = ""

....sounds a little too easy though, I may have misunderstood your
question.

 

[e.mel]

Oh, I see, your deleting from another form.
try an update query, along the lines of:

UPDATE Table1 SET Player3 TO "" WHERE <<where condition goes here>>;

you can use the query builder to get it just right, then copy and paste
the SQL out.

hope that helps

 

[Klatuu]

I would recommend Null rather than "" as that is the default for new records,
unless, of course, the field does not allow nulls.

 

[Dave Hawks]

Thanks Null solved the problem.
One small problem remaining is that until I close the form the record is not
updated and therefore if someone then tries to enter data in that field the
value of fld Player1.OldValue is not null and the cycle is triggered again.
Is it possible to update the record without closing the form?
Thanks again for the help.

 

[Klatuu]

Yes. You can use Me.Requery; however, it will cause you to loose your
current record postition and put you back to the first record in the
recordset. The cure for that is to capture the bookmark of the current
record, do the Requery, then reposition the recordset to the saved bookmark.

 

[J. Goddard]

Instead of me.requery, you can use me.refresh.

John

 

[Klatuu]

I am not a big fan of the Refresh method, particularly in a multi user
environment. This is taken directly from VBA Help:

The Refresh method shows only changes made to records in the current set.
Since the Refresh method doesn't actually requery the database, the current
set won't include records that have been added or exclude records that have
been deleted since the database was last requeried.