Check if all required fields have been filled (1 Viewer)

[ScottXe]

I tried to check if all required fields have been filled before saving the record using the following code.


Private Sub txtReportNo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cboMainCat) Then
Cancel = True
MsgBox ("Please enter Data for Main Cat")
Me.cboMainCat.SetFocus
End If

Repeat above code for all required controls here.

End Sub


It did not work with following error code.


Run-time error ‘2108’

You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.


The error message contradicts my original intention that prevents from saving without all required fields have been filled. Is there any way to get rid of this error message?


In addition, under the value of certain field, I need to check additional fields under that scenario. Is any additional code required to add in for that purpose?

 

[spikepl]

Your check will not prevent unfilled fields, because it is only executed if user places the cursor in the field.

You need to do validation in the form's BeforeUpdate, and there check if all required fields have obtained values. Google - plenty of examples of that, also here on AWF

 

[ScottXe]

Thanks for your response. There is a reason for the check that put in this field. The data is entered while the progress from the beginning to the end. When the project is completed, this field must be completed as an end of the project. The check cannot be put in the form, otherwise the user is unable to enter the data while the project is progressing.

Below is an example I got it from the internet but unfortunately it does not work in full. Thus it was raised to seek for advice.

 

[ScottXe]

Thanks for your response. There is a reason for the check that put in this field. The data is entered while the progress from the beginning to the end. When the project is completed, this field must be completed as an end of the project. The check cannot be put in the form, otherwise the user is unable to enter the data while the project is progressing.

Below is an example I got it from the internet but unfortunately it does not work in full. Thus it was raised to seek for advice.

Below should read as the example given.

 

[DennisOJensen]

Can I assume you are using database binding to your fields ?

 

[ScottXe]

Hi Dennis,

Yes, the form is linked with a query.

 

[DennisOJensen]

May I suggest that you use Unbound fields and then dynamically assign the Field.RowSource the query you want to use. I have never experienced issues with unbound fields while I must say I have and I have heard others almost always struggling with bound fields. This has 2 benefits 1) you avoid the pitfalls of bound fields which seem to be numerous and 2) if you ever want to port this code to something more robust it is almost as easy as copy and paste -- rather then a total re-engineering

 

[Acropolis]

For checking all field have been completed on a form, a usually give the required fields a tag of "required" then in the code, loop through each control and check the ones that are required are not empty, if they are stop until they are not.

 

[ScottXe]

Thanks for your advice. I have got a reference sample to achieve what you said. However, when I completed the project, the remaining mandatory fields must be filled as well. How can the system to check if the mandatory fields have been filled when the project is completed?

 

[The_Doc_Man]

It is a matter of designing how you do the update.

I have a bunch of forms with 4 or 5 (depending on form's function) selections. Could be combo-box, could be multi-select list box, could be a calendar control... doesn't matter.

When I tab through the controls and make my selections, I can hold down the tab key and nothing happens because I do not trigger or allow a form save based on LostFocus. Instead, I have two buttons, CANCEL and COMMIT, and to save data you must click the COMMIT button. (Tabbing through a button doesn't click it.) In the button-click routine, I do my checks for valid data selection and will not allow the update to occur if something is missing. Only if the data requirements are fully met will I allow it to reach the SaveRecord method for that form. I do NOT allow Access to make that decision by implication.

This means that the tab option on the Options page does not allow "navigate to next record" because that is an implied Save. It also means that I trap the update via a Form_BeforeUpdate and (in essence) slap the user's hand with a "Please COMMIT or CANCEL before attempting to leave this record." I disable the ribbon so the user can't click the appropriate record-control options. I also don't allow the user to see the navigation panel since you could force a Close from there and cause an Implied Save.

Even when my forms are bound to underlying recordsets, this approach works. In fact, it works equally well for bound and unbound recordsets. Since the users cannot see "behind the scenes" they cannot tell the difference anyway.

 

[ScottXe]

Hi The_Doc_Man,

Many thanks for your good advice that I might neglect it in the past. It is good for practicing it again.

 

[Roger Reinsch]

Your check will not prevent unfilled fields, because it is only executed if user places the cursor in the field.

You need to do validation in the form's BeforeUpdate, and there check if all required fields have obtained values. Google - plenty of examples of that, also here on AWF

I found this thread looking for a VBA equivalent to the saverecord macro you get when you create a "save" button. I needed to do other things before the record actually got saved. The [B/] Forms' BeforeUpdate [/B] was the perfect place for the logic I needed to add. Thanks.