Access: How to intercept “You must enter a value in the [Field-Name] field”

Topic

New Reply

I have a table and a textbox referring to it that requires a default value of 0 (zero) if a value is not entered. The 0 is displayed on first opening my data entry form, but if I enter a value, then delete it leaving a NULL value, when I try to update the field I get a standard Access error dialogue advising “You must enter a value in the ‘[Textbox Field-Name]’ field”.

How can I trap this standard message and automatically insert a 0 in lieu of the null as the field is updated?

Looking forward to a simple solution, using either a validation rule or an event procedure.

Thanks in anticipation of any assistance

Baffled

BygAuldByrd

Reply | Quote

Replies

Did you define a default value for the associated field? That would be the quickest way to achieve what you want. You can then remove the required property from that table column (which I suppose is what triggers the error message).

Reply | Quote

Hi Ruirib,

Thanks for the prompt response. Unfortunately your solution only creates other issues for me.

My table has the Required property set to Yes because the field is used in numerous calculations in reports and forms and a Null value causes all sorts of problems. The Default Value in the table is set to 0, as is the default value in the textbox on the form that refers to the table. Everything works just fine until I enter a valid value in the textbox on my data entry form then decide to delete it, the default value is not reinserted but left as Null unless I expressly insert a 0. I’d like this situation to be recovered automatically.

Reply | Quote

BygAuldByrd,

In the LostFocus event of the Textbox some code like:

Code:

  Private Sub ContractNo_LostFocus()

     If IsNull(Me.ContractNo) Then
       MsgBox "Setting Contract No to 0", vbOKOnly, "Execution Test"
       Me.ContractNo.Value = 0
     End If

End Sub

Of course you’ll replace ContractNo with your Textbox name and comment out or delete the MsgBox line after testing. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG,

Tried your solution but the standard error dialogue still appears.

I’ve tried similar procedures in Private Sub Textbox_BeforeUpdate() and Private Sub Textbox_AfterUpdate(), but with the same disappointing results.

Any more thoughts?

BygAuldByrd

Reply | Quote

Setting the Required property to Yes means the default value set in the table definition will never be used, because you are mandating, in fact, that the user always specify a value when inserting a new record. Setting Required to No, if the user does not define a value will never result in a NULL, because you defined a default value, so when an insertion takes place without an explicit value for that column, the database will assign it the default value of 0.

P.S.: Of course, if you have update statements that can affect the column value as well and set it to NULL, then the best option is to ensure that the text field never gets a NULL value. If that is the case, then RetiredGeek suggestion should be followed. In that case, the default value definition is, in fact, superfluous and will never be used.

Reply | Quote

Hi Again Ruirib,

You explanation has clarified the situation for me. Many thanks.

To be clear for others reading this thread I have:

* In my table set the “Required” property to NO and the Default property to 0,
* In my Form the textbox Default property is set to 0, and
* In the textbox After Update property is the following Event Procedure:

Private Sub Textbox_Name_AfterUpdate()

‘ *** Check of value in Textbox Name property


If IsNull(Me.Textbox_Name) Then
Me.Textbox_Name.Value = 0
End If


’ *** End Check of value in Textbox_Name

End Sub

Thanks again

BygAuldByrd

Reply | Quote

The code likely is best added to the Form_BeforeUpdate event, if you go the coding route.

Reply | Quote

Glad you got it sorted :).

Reply | Quote