Message if no entry?

[Kassy]

I tried using a message box to try and validate text entry on a form into a bound txt box 'PaymentAmount', If there is no payment entered, using the properties box Validation Rule ,- 'is not null' then entered a validation text ="Please enter an Amount" but this does not work. How can I get a message box to appear if someone leaves the entry field empty so that they have to make some entry to move on. I have already entered this in the table validation field as well as 'Required - yes', but the message is an Access one not my own. Where or how do I do this ?

 

[RuralGuy]

The proper way is to put validation code in the form's BeforeUpdate event to catch these type of problems. The only issue then is when no changes occur to the underlying record then you don't get a BeforeUpdate event. There is *no* "We're about to leave this record" event.

 

[Kassy]

How?

OK So exactly what validation code should I put in the BeforeUpdate event? I dont know VB code and so far have used mainly SQL? Is this where I put a message box. If the underlying record doesnt change then will it affect the working of the code?

 

[ansentry]

Make sure you still have Required - yes for the PaymentAmount in your table;

Put this code behind the On Error Event of your Form

If DataErr = 3314 Then
   Response = acDataErrContinue
      MsgBox "You must enter an Amount in Payment Received", vbCritical, "Data Entry Error!"
 [B][COLOR="Blue"]  Me.txtPaymentAmount[/COLOR][/B].SetFocus
End If

Me.txtPaymentAmount I have used this as the name of your control on the form (the control being PaymentAmount)

Also make sure that you do NOT have a default value for the PaymentAmount in the table, if your field is Format = Currency then the default value will be 0 (Remove it), if you don't then the code will not run as the control will have $0.00 in it.

 

[Dwight]

Here is what I use. It is based on a Pat Hartman recommendation. Put it on the BeforeUpdate event as RuralGuy suggests.

If IsNull(Me.txtPaymentAmount) Then
MsgBox "Payment Amount is a required field", vbCritical, "Field required "
Cancel = True
Me.txtPaymentAmount.SetFocus
Exit Sub
End If

Dwight

 

[Kassy]

thanks All

Thanks for all the responses, will try them to see results.

 

[RuralGuy]

You have some good suggestions Kassy. Keep us posted.

 

[Kassy]

Dwight. (Pat Hartman) recommendation. Put it on the BeforeUpdate event
If IsNull(Me.txtPaymentAmount) Then
MsgBox "Payment Amount is a required field", vbCritical, "Field required "
Cancel = True
Me.txtPaymentAmount.SetFocus
Exit Sub
End If
This gives Access message box : The field tblPayments cannot contain a null value because the required property for this field is set to true. Enter A value in this field.
Then another msg box:- the macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing MSAccess for saving the data in the field.
If this is a macro open the macro window and remove the action that forces a save (Go to Control)
If the macro includes a SetValue action set the macro to the After Update property instead or If this is a function redefine the function in the Module window.
ancestry John A -Get runtime error 2108 –you must save the record before you go to ‘control method’, ‘Set focus’ method. This works if you comment out 'set focus' line. Leaves focus in another field though:-

 

[ansentry]

Have a look at the attached sample. I have used both methods that have been discussed (On Error and Before Update).

Each form has its own table because the tblPayments has the PaymentAmount "Required set to Yes" and tblPayments2 does not.

 

[Kassy]

Have download will try

Thanks -I have downloaded the zip file will try both methods, thanks again.

 

[Kassy]

Sorry still cant get it working tried it both ways but am getting a compile error now method or data member not found. I have rechecked my field and it is definitely called PaymentAmount. I am entering this on the Before update event of the main form -should this be on the SubForm 'PaymentForm' Before update?
Code:-
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PaymentAmount) Then
MsgBox "Payment Amount is a required field", vbCritical, "Data required "
Cancel = True
Me.PaymentAmount.SetFocus
Exit Sub
End If
End Sub
This was exactly as I had it before but for some reason if I commented out
'Me.PaymentAmount.SetFocus' then it worked which it wont do now.

 

[ansentry]

Does the sample I posted work?

The code for BeforeUpdate should be on the Form NOT the control.

The code for On Error should be on the form NOT the control.

Did you have a look at my sample, it will should you where the code should go. If PaymentAmount is on a SubForm then put the code in the BeforeUpdate on the Form, same if you try the On Error.

Maybe you should post a copy of your database.

 

[Kassy]

Will try again but

Sorry John -I did check your forms while they are read only I can't check to see if they work because I can't add another record however I did check the code behind the forms and that is exactly what I have done. Both ways- however I did put it onto the Main form that contains the sub form. I can't remember but I THINK I also tried using it on the Sub Form and again it was not in the control of the form will check again and run through it all -too tired last night to bother anymore -it's irritating because I get the feeling it's going to be one of those silly things.My Field is formatted to currency with no default declared., again two tables one with required -no the other yes. As soon as I get the opportunity to retry will get back to you, Thanks again.

 

[ansentry]

My samples should not be read only!
What version of access are your running?
I am running Access2003 (Default file format 2000).

 

[Kassy]

Working

Code is finally working. I went over it all deleted all pieces of code I had tried and had commented out then reinserted this into Payment Form.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PaymentAmount) Then
MsgBox "Payment Amount is a required field", vbCritical, "Data required "
Cancel = True
Me.PaymentAmount.SetFocus
Exit Sub
End If
End Sub
This works and I feel happier now I have got rid of all the other bits and pieces which were cluttering my code up. It appears to be exactly the same as before however it is now on the Payment form. Thank you very much.
By the way I am usng Access 2000, Windows XP and your database is read only. Not only is your database read only but other example DB I have tried using from the forums are more than not (not all) often read only as well. Quite often I get the examples suddenly terminating with some unkown error however I put all this down to the fact the others may have had some error anyway! Once again many thanks for your patience.

 

[Rod D]

Read Only (Slightly Off topic)

Kassy

I get the "read only" status on every sample DB that is posted as a Zip file and is opened without extracting it to my hard drive first. This is down to how Zip works - it creates a temp file, which Access cannot use as the names are all "wrong".

To get around the problem, extract the zip file to your hard drive and once extracted, open the properties of the db file and make sure that the file is "Unblocked". This is because your details have not been added to the security (access control) lists.

You will then have full read/write permissions to the file.

Hope this helps...

Regards
Rod

 

[RuralGuy]

Hi Kassy,
It sounds like something might me amiss with your copy of Access. Do you have all of the latest updates? Do you use Office Update? Or better yet, switch from Windows Update to Microsoft Update which will pick up any changes to both windows and office.

 

[Kassy]

Is up to date.

Thanks for the tip Rod will check it out.

Rural Guy - I had megga problems with my database back two -three months ago. I was using a memory stick to transfer the DB from one laptop to another then once I tried to use my first function and Macros the DB kept getting corrupt forms/tables etc. It got so bad I thought I'd completely lost everything because even going back to previous back up versions the errors seemed to be re-occuring. After lots of forum help I got an article that pointed me to corrupt Databases and yes low and behold I needed several different updates and instead of using access repair database utilities The article advised to download the Microsoft Jet Database Compact Utility JETCOMP which repaired the DB perfectly. At least 'touch wood' don't seem to have problem re-occur. So I think I have all latest necessary updates. Thanks for your help everyone.

 

[RuralGuy]

You are welcome Kassy! Thanks for posting back.