Making custom data check for field

[mcdhappy80]

I want to make the fields in my table required and without zero length but I don' like the default Field Required or No Zero length messages I'm receiving.

How can I modify this with VBA code?

Thanks

 

[khawar]

use before update event of form and check there whether the value is provided for the field or not

 

[DCrake]

You need to apply these rules at form level using the before update event. That way you can apply your own custom message boxes.

David

 

[mcdhappy80]

Can someone please point me to where I can read on how to make a Msg Box and also, what code do I need to type to check the length of field or if the field has Spaces or Null values (not empty)?
What functions should I use and for which controls on form do they apply?
Thanks.

 

[khawar]

code for message box is as below

msgbox("Enter your message here")

 

[DCrake]

First of all do you have a form where you can collect the data from?

You should not be collecting/modifying data at table level.

What stage are you at now?

 

[mcdhappy80]

I have a form where I enter the data for workers. All the fields in the database are set to Required and No Zero length, but as I said I don't want the default display message for that control but want to check in the form. So I suppose I need to check if the field is not Null or empty string but as I also said I don't know which methods to use.

 

[DCrake]

As I said you need to use the before update event of the control on the form to validate the contents of the, or the lack of it.

 

[Coatezy]

Hi,

I'm also trying to do the exact same thing at the moment.. And as said above I'm not sure on the code required to check a specific field to check it has data in the field. I understand the before and after update side of things..

Cheers

 

[DCrake]

Ok Lets assume we have a field called fldQuantity in a table, and this field is bound on a form to a textbox named Txtquantity.

On the BeforeUpdate Event of the control

Private Sub TxtQuantity_BeforeUpdate()

If IsNull(Me.TxtQuantity) Then
   MsgBox "You must enter a value in this field before saving"
End If

End Sub

 

[mcdhappy80]

And how would You check if the length of text in textfield is zero?

Do textfields have LENGTH property?

Also, how would You check if the entered characters are all spaces (blanks)?

Thanks

 

[DCrake]

Len(Me.Field) = 0 Empty

Trim(Me.Field) = "" Empty

IsNull(Me.Field) = True/False

 

[mcdhappy80]

I've put this code to check if Date type text field is empty:

Private Sub btnbrpRadniOdnos_BeforeUpdate(Cancel As Integer)

If IsNull(Me.dtePocetakRadnogOdnosa) Then
    [dtePocetakRadnogOdnosa].Value = Now()
End If

End Sub

When I want to test it and try to enter no data I receive the message in attachment.

Why does this happen because I'm setting the new value for the field on the fly?
How do I fix this?

Thanks.

 

[DCrake]

first of all you don't need the .Value part of the code.

Me.dtePocetakRadnogOdnosa = Now()

will suffice.

Then you need to issue a cancel command

Cancel = True

I do not tend to use Before Update checking as I do not tend to use bound controls.

David

 

[mcdhappy80]

I've added the lines as You suggested and the code looks like this now:

Private Sub dtePocetakRadnogOdnosa_BeforeUpdate(Cancel As Integer)

If IsNull(Me.[dtePocetakRadnogOdnosa]) Then
    MsgBox ("Morate uneti vrednost u polje Pocetak Radnog Odnosa")
    Me.dtePocetakRadnogOdnosa = Now()
    Cancel = True
End If

End Sub

Then you need to issue a cancel command

Cancel = True

What does the Cancel = True command do in this particular case, could You explain please?

I do not tend to use Before Update checking as I do not tend to use bound controls.

What do You mean by this last part? What do You use instead?

Thank You.

 

[mcdhappy80]

I've added the lines as You suggested and the code looks like this now:

Private Sub dtePocetakRadnogOdnosa_BeforeUpdate(Cancel As Integer)

If IsNull(Me.[dtePocetakRadnogOdnosa]) Then
    MsgBox ("Morate uneti vrednost u polje Pocetak Radnog Odnosa")
    Me.dtePocetakRadnogOdnosa = Now()
    Cancel = True
End If

End Sub

And now I'm getting another error message:

Then you need to issue a cancel command

Cancel = True

What does the Cancel = True command do in this particular case, could You explain please?

I do not tend to use Before Update checking as I do not tend to use bound controls.

What do You mean by this last part? What do You use instead?

Thank You.

 

[mcdhappy80]

What code I must use in order to check if the entered data in text field is date data type?
The format I'm displaying the date is dd. mmmm. yyyy.

I need the same thing to check if the entered data are all numbers or all text.

How do I do it?

Thank You.