Simple validation issue - or it should be simple, sigh

[JamesWB]

Tearing my hair out trying to find any help from any source (despite loads of searching and looking in books!) showing me how to make a validation rule for numeric values including a decimal place, eg, I want to allow double values. I have the table field set to Double, but I need to do the validation on user input as well, before it's saved to the table, as there is a visible calculation field in the form based on some field values.

I assume it's something like LIKE "[0-9]* but how do I make it accept a decimal place in any position please?

Yours desperately.

 

[JHB]

Why do you not let MS-Access do the job?
If a (text) control is bound to the number field in the table, you are only able to input numbers, (with or without a decimal place, depending of the field type in the table), else MS-Access shows an error!

 

[JamesWB]

Why do you not let MS-Access do the job?
If a (text) control is bound to the number field in the table, you are only able to input numbers, (with or without a decimal place, depending of the field type in the table), else MS-Access shows an error!

I want to put a friendly error message when that happens - is there a way to do that other than through Validation Text?

 

[JHB]

You can handle it in the form's "On Error" event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  If DataErr = 2113 Then
    Response = acDataErrContinue
    MsgBox ("Show you message here")
  End If
End Sub

 

[JamesWB]

You can handle it in the form's "On Error" event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  If DataErr = 2113 Then
    Response = acDataErrContinue
    MsgBox ("Show you message here")
  End If
End Sub

Thanks, but presumably that's form-wide is it? I only want to give a friendly error on certain fields.

Can someone please tell me what the validation text is for the following rule please?

"Only allow numbers (negative or positive) with a decimal place in any position."

Thanks!

 

[JHB]

... I only want to give a friendly error on certain fields.

Then use the ActiveControl name to determine if you want to show your message or not.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  If Me.ActiveControl.Name = "Checkit" Then
    If DataErr = 2113 Then
      Response = acDataErrContinue
      MsgBox ("Show you message here")
    End If
  End If
End Sub

You can also use a control's Tag property to determine if you want to show your message or not.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  If Me.ActiveControl.Tag = "Check" Then
    If DataErr = 2113 Then
      Response = acDataErrContinue
      MsgBox ("Show you message here")
    End If
  End If
End Sub