vba: how to clear field in datasheet with beforeupdate

[zeta]

Hi,

This is the scenario

1 - you have a table with one column: col (stores current date)

2 - you create a form with datasheet default view

3 - for some reason, you want to write a little vba that has the following effects when you press enter key:

a - the entered value never gets into the database
b - the field in the data sheet control is cleared

Private Sub col_BeforeUpdate(Cancel As Integer)
    Cancel = true;
end sub
 

achieves a), but you have no idea how to achieve b).

Things tried that didn't work (throw errors):

Me.col = Null
Me.col = Nothing
 

Thank you very much

[mrijet]

I m not clear with the question. Cannot help.

[jforbes]

You could try:

Me.Undo

This will only work for you if you are on a NewRecord, which I'm guessing you are since you have a Cancel=True in the BeforeUpdate Event.

You'll never be able to add more than one row in this scenario. If you plan on filling out multiple rows and then throw them away after you've done what you need to do, you will need to use controls bound to a table, allow the Updates to happen, and then run a DELETE query after you are done to clear out the records.

[zeta]

@jforbes awesome. your suggestion did the trick. thanks.