resetting null

[sudo]

resetting to null

I have a form where the user sees the current value in a group of fields and they are allowed to change one or all of the values. There are two columns of data, one column has a locked set of fields (for reference purposes) showing the current value. The second column also shows the current values, but the user can click on the field and change the value. One of the options I would like the user to have is to "blank out" a value, that is, set it to Null (not Empty). Is there a way to do this? The reason I want a Null value to be recorded when the user blanks out the value is that I use Is Null and Is Not Null in a series of macro commands to do some recalculations if the user blanks out a certain date field. When I tested the form and highlighted the value and deleted it, Access ignored this and when the form refreshes, reverted back to the non-blank value. That is, Access ignored the fact that I had deleted the non-blank value. Is there a way for the user to delete a non-blank entry such that the new value is set to Null? I know it isn't to enter double quotes as that's the symbol for Empty. I also assumed typing the word Null (with or without quotes) probably wouldn't work (I haven't tested these, though) as Access would likely see that as a string as opposed to the value Null. I don't know if this has any bearing, but the field of interest is formatted to Short Date and has a data entry mask - could this be preventing the user from being able to blank out the current value?

Thanks

 

[WayneRyan]

sudo,

Use the BeforeUpdate event of the field to:

If Nz(Me.YourField, "") = "" Then
   Me.YourField = Null
End If

Wayne

 

[geralf]

sudo,
Just an idea I use myself

Place a command button below the field you want to set to Null (or someplace else), and use the button's OnClick event to run this code:

Me.YourFieldName1=Null


You could make the button's Caption something like 'Reset'

You could use a command button for each field you want to reset, and also include a button to 'Reset all' at once. Use the OnClick event to run a sequence for resetting all the fields like this:

Me.YourFieldName1=Null
Me.YourFieldName2=Null
Me.YourFieldName3=Null
.....
....
.....

 

[sudo]

Wow - 2 ideas

This is great. Two ideas to try out. Thanks geralf and WayneRyan. I'll try both ideas and see which one fits in with the flow best. I was actually expecting someone to say "Nope - impossible", so having two ideas to play with is amazing. Access sucks.