Require Data Entry in field w/in Form

[Laurel]

Hi all,

For each field in my form, I would like to require that it be populated prior to moving to next field and/or saving form. I have set the 'required' property to Yes for all of the fields, however, when I open the form it allows me to continue from field to field with no data entry....

I've obviously missed some basic step. Not too big a surprise to me, as it's been about 15 years since working in Access or with db's at all!

Any help/guidance is MUCH appreciated!!

Laurel

 

[HiTechCoach]

If you want to validate teh form before saving, then use the before Update event of the form. This is the way I normally handle validations.

Example using the 2000/2002/2003 Northwind database's customer form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

   Cancel = False


' perform data validation
If IsNull(Me.CompanyName) Then

   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   Me.CompanyName.BorderColor = vbRed
   DoCmd.GoToControl "CompanyName"
      
   Cancel = True

End If


If Not Cancel Then
  ' passed the validation process

    If Me.NewRecord Then
        If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
            ' run code for new record before saving
        
        End If
    
    
    Else
        If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
           ' run code before an existing record is saved
           ' example: update date last modified
            
        End If
    End If

End If


' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

    If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
        Me.Undo

    End If
    
End If



End Sub

 

[missinglinq]

I would like to require that it be populated prior to moving to next field and/or saving form. I have set the 'required' property to Yes for all of the fields, however, when I open the form it allows me to continue from field to field with no data entry....

Setting the Required Property will only send up a warning message when you try to save the record (as will Coach's validation code in the Form_BeforeUpdate event) not when you move out of a given textbox. Either method is acceptable, although the latter allows you, as the developer, to give your users a more friendly/clearer warning message if the validation fails.

The problem with trying to prevent a user from "moving to the next field" until a given field is populated is that the user may simply not enter the given field! In order to do this you'd have to force a user into FieldA, check it for data on exiting it, and repeat this all the way thru the form. Different data input people may want to enter data in a different order from that which you decided would be best. You could very well leave them not only frustrated and, but less productive.

 

[HiTechCoach]

I totally agree with missinglinq's points. That is also why I did not offer a solution that forces data entry by field. I tried it once. The data entry people were revolting until I removed it.

 

[Laurel]

Thanks so much for the guidance...

I entered the code in the Before Update Event on the form. I've done something incorrectly for certain...

The first field for entry on the form is "Title." When I intentionally leave the title empty and then attempt to save the record, the following happens -

1) I get message about needing to enter the title - so far, so good;
2) I click "Okay" and...
3) I immediately receive message asking whether I want to undo changes.

I was expecting to have the cursor jump to the "title" field where I could enter the missing "title"....

Have I mentioned how I'm in over my head with this??

Here is the Form Before Update code as it is at the moment:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = False

' perform data validation
If IsNull(Title) Then
MsgBox "You must enter an Engagement Title.", vbCritical, "Data entry error..."
Title.BorderColor = vbRed
DoCmd.GoToControl "Title"

Cancel = True

End If

If Not Cancel Then
' passed the validation process
If NewRecord Then
If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code for new record before saving

End If


Else
If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code before an existing record is saved
' example: update date last modified

End If
End If
End If

' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then
If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
Me.Undo
End If

End If

End Sub

SO. At this point, I need to do two things. A. figure out what I've done wrong with regard to the 'title' field validation - and once I've got that; B. figure out how I incorporate the validation to the remaining fields on the form.

Any Help on New Year's Eve out there???

 

[philosofe]

Hi Laurel,

Try changing the first part to

If IsNull(Me.Title) Then
MsgBox "You must enter an Engagement Title.", vbCritical, "Data entry error..."
Me.Title.BorderColor = vbRed
Me.Title.SetFocus
Cancel = True
End If

However, if the control's formatting is sunken, the change in Border Colour won't show. You could change the label's ForeColor instead (and you'll have to restore it to black in the case of correct validation, or the colour will stay red until the user closes the form.

Hope this helps on point A

 

[Laurel]

Thank you so much for your suggestion.

Replacing the begining of the code with what you provided did cause the cursor to move to the unpopulated "title" field, but just as the cursor moves to the field, the message about whether user wants to undo changes continues to appear. (Thereby not allowing any data entry into the title field)

 

[philosofe]

Hi again,

yup that message box is created by the

If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
Me.Undo
End If

If you don't want that message to show, just comment those lines out - but the users won't be given the option to undo the data entry in case of erroneous creation / edits.