Creating Required Fields on a form

[topp]

So I have created a basic form for users to enter data when creating a new item. I want to make every field required so I have given each field a tag and then created a If statement to run through those fields and check whether or not each field is null. (code below)

Private Function CheckAllFields() As Boolean
Dim Ctrl As Control
CheckAllFields = False
For Each Ctrl In Me.Controls
If Ctrl.Tag = "*" And IsNull(Ctrl) Then
MsgBox "A required Field has not been filled."
CheckAllFields = True
Exit For
End If
Next Ctrl


End Function

So that code works and will pop up a box if a user tries to close the form without entering something into each field. The code I am using to check after a user goes to close the form is below:

Private Sub Form_Unload(Cancel As Integer)
If CheckAllFields = True Then Cancel = True: Exit Sub
End Sub

Now this all works as it should until I get someone who enters this form by accident and does not need to enter new information. The way it is setup currently the user is unable to close the form because of the Unload statement seeing that fields are null. I have been trying to work around this by messing with an If statement that checks to see if the form is dirty or not because I figured that if someone opens the form and realizes that it is the wrong form they just go to close it and the form should still be Dirty = False. Everything I have tried seems to not work with this dirty property. If anyone could lend some advice as how I can get this to work it would be much appreciated!! Thanks in advance!

[pbaldy]

I would use the before update event of the form rather than the unload event. It shouldn't fire unless the user has dirtied the record.

[topp]

pbaldy,

Thank you for your quick response. That seemed to do the trick as now they can close the form and just now just have to hit yes saying that nothing will be saved. One thing that is happening now is when I click on the Add new record button and a field is not filled in I get the same error message saying a required field has not been filled but then once I click Ok on that msg box another one pops up saying "You cant go to the specified record" Any idea what might be causing this new error?

[pbaldy]

I wouldn't expect any message. Is it yours or a built-in message?

I suspect your code to go to a new record is running into the update event being cancelled. What happened before? I suppose you could run your function first, though then it would be run twice if it passed.

[topp]

Its an access message not anything I have created. And the code im using is just a macro button that I created from the button wizard to add new record. It also seems to be causing my auto index ID to be cleared when it tries to create a record but a field is missing. This in turn is not allowing me to create a record at all once I have filled in all the fields since the RecordID is an auto indexing field.