Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

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

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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).

  3. The Following User Says Thank You to ruirib For This Useful Post:

    BygAuldByrd (2011-11-05)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2011-11-05)

  6. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    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.

  7. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Last edited by ruirib; 2011-11-04 at 20:29.

  8. The Following User Says Thank You to ruirib For This Useful Post:

    BygAuldByrd (2011-11-05)

  9. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    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

  10. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The code likely is best added to the Form_BeforeUpdate event, if you go the coding route.

  11. The Following User Says Thank You to ruirib For This Useful Post:

    BygAuldByrd (2011-11-05)

  12. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    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

  13. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Glad you got it sorted .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •