I've got a form for entering data but the foremen have a bad habit of leaving
certain fields blank. I want the form to check to make sure all the fields
have been filled in before allowing the database to be closed or before they
can move to the next form. OR at the very least I'd like to be able to make
the form check to see if any of the boxes are still blank and then display a
message box advising them to go back and fill them in with a button to
continue or cancel. I would like it to simply allow them to continue without
anything occuring if they have filled in ever cell.

Now what makes this challenging for me is that there is no added button on
the form that they press to move to the next form... its just those arrow
buttons at the bottom of the form view which allow you to enter new sets of
data or view old sets. Do I need to tie this program into those buttons
somehow? Do I need to add a button? Is there a command that will auto_execute
to check when they try to exit or move to the next form?

Thanks for any help!

Personally I remove the standard nav buttons and replace them with my own,
for reasons such as these.

But more to the point, it sounds like you want to use the form's
BeforeUpdate event to validate the required user entry. The BeforeUpdate
event of the form runs when the record is about to be saved, but has not been
saved yet. It also provides the means to cancel the save. So I would think
something like this may work:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Nz(Me.ControlName, "")) = 0 Then
MsgBox "Please Enter a Value"
Cancel = True
End If
End Sub


or if you have multiple values to check, you may want a flag variable:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnVerified As Boolean
blnVerified = True

If Len(Nz(Me.Control1, "")) = 0 Then
blnVerified = False
End If

If Len(Nz(Me.Control2, "")) = 0 Then
blnVerified = False
End If

...

If blnVerified = False Then
MsgBox "Please Enter All Values"
Cancel = True
End If

End Sub


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"BTU_needs_assistance_43" wrote:

> I've got a form for entering data but the foremen have a bad habit of leaving
> certain fields blank. I want the form to check to make sure all the fields
> have been filled in before allowing the database to be closed or before they
> can move to the next form. OR at the very least I'd like to be able to make
> the form check to see if any of the boxes are still blank and then display a
> message box advising them to go back and fill them in with a button to
> continue or cancel. I would like it to simply allow them to continue without
> anything occuring if they have filled in ever cell.
>
> Now what makes this challenging for me is that there is no added button on
> the form that they press to move to the next form... its just those arrow
> buttons at the bottom of the form view which allow you to enter new sets of
> data or view old sets. Do I need to tie this program into those buttons
> somehow? Do I need to add a button? Is there a command that will auto_execute
> to check when they try to exit or move to the next form?
>
> Thanks for any help!

BTU,

I too have to make sure that users fill out all the required fields. I have
2 other forms on my main form to check also, but I will keep my answer as if
it were just one form. On the cmdClose_Click() event on my main form I have
this:

Private Sub cmdClose_Click()
'Declare and initialize variables
Dim iRequiredFieldCnt As Integer
iRequiredFieldCnt = 0

Call CheckRequiredFields(iRequiredFieldCnt)

If iRequiredFieldCnt = 0 Then
DoCmd.Save
DoCmd.Close acForm, "MyFormName"
Else
MsgBox "Please fill in all required fields."
End If
End Sub


Now for the CheckRequiredFields module, go through and check all the fields
on your form for null and add to the counter if it is null:

Private Function CheckRequiredFields(iRequiredCnt As Integer)

'Declare variables
Dim strMsg As String

'Initialize variables
strMsg = "THE FOLLOWING FIELDS ARE REQUIRED
iRequiredCnt = 0

If IsNull([Part Number]) Then
iRequiredCnt = iRequiredCnt + 1
strMsg = strMsg & vbCrLf & iRequiredCnt & ".) Part Number"
End If

If IsNull([Part Name]) Then
iRequiredCnt = iRequiredCnt + 1
strMsg = strMsg & vbCrLf & iRequiredCnt & ".) Part Name"
End If

....

If iRequiredCnt <> 0 Then
MsgBox strMsg, vbExclamation, "Required Fields"
End If

End Function

Hope this helps.

Sincerely,

Cheryl



"BTU_needs_assistance_43" wrote:

> I've got a form for entering data but the foremen have a bad habit of leaving
> certain fields blank. I want the form to check to make sure all the fields
> have been filled in before allowing the database to be closed or before they
> can move to the next form. OR at the very least I'd like to be able to make
> the form check to see if any of the boxes are still blank and then display a
> message box advising them to go back and fill them in with a button to
> continue or cancel. I would like it to simply allow them to continue without
> anything occuring if they have filled in ever cell.
>
> Now what makes this challenging for me is that there is no added button on
> the form that they press to move to the next form... its just those arrow
> buttons at the bottom of the form view which allow you to enter new sets of
> data or view old sets. Do I need to tie this program into those buttons
> somehow? Do I need to add a button? Is there a command that will auto_execute
> to check when they try to exit or move to the next form?
>
> Thanks for any help!

I disagree on putting an exit sub line in each conditional. Good practice
says only have have one exit point for a procedure, and often in a
BeforeUpdate procedure there's other stuff besides this going on... Exit Sub
in each conditional will give no opportunity to clean up the procedure.

Op is looking to make sure that ALL fields are filled in, not just a
specific field. If it were a specific field then we could easily set the
focus to that field (op may want to do this anyway), but if you'll notice in
my code, there's only one message box at the bottom that displays a msg
stating that all fields need to be filled in. Otherwise, if the message box
we're to specifically tell what field needs to be filled in, the would then
think that only this field needs to be filled in, and would probably be a bit
aggrevated when he got this field specific message for every empty field.

An alternative would be to contencate field names into a msg string that is
to be displayed at the end of the procedure. Or, rather than using Exit Sub
in each conditional, use Goto Exit_Procedure to maintain a single exit point.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Proc
Dim bValid As Boolean
Dim sMsg As String

bValid = True
sMsg = "The following fields require values:" & vbCrLf

If Len(Nz(Me.Control1, "")) = 0 Then
bValid = False
sMsg = sMsg & Me.Control1.Name & vbCrLf
'Goto Exit_Proc
End If

If Len(Nz(Me.Control2, "")) = 0 Then
bValid = False
sMsg = sMsg & Me.Control2.Name & vbCrLf
'Goto Exit_Proc
End If

....
....

Exit_Proc:
If Not bValid Then
If Len(sMsg) <> 0 Then MsgBox sMsg
Cancel = True
End If
Exit Sub
Err_Proc:
bValid = False
sMsg = ""
MsgBox Err.Number & " " & Err.Description
Resume Exit_Proc
End Sub



In any case, I don't mean to shoot down your ideas (they are good ones,
depending on how Op wants to interact with his users), but I personally
wouldn't use an Exit Sub except at the exit point of a procedure as indicated
above.

But, as some people say, there's ALWAYS more than one way to skin a cat! :-)

Hopefully between everyone's various ideas Op can conjure up something that
will work.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"Linq Adams via AccessMonster.com" wrote:

> If you're doing multiple Validations, you also need to add the line
>
> Exit Sub
>
> and then set focus to the control in question.
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> If Len(Nz(txtFieldA, "")) = 0 Then
> MsgBox "You Must Enter Data in Field A"
> Cancel = True
> txtFieldA.SetFocus
> Exit Sub
> End If
>
> If Len(Nz(txtFieldB, "")) = 0 Then
> MsgBox "You Must Enter Data in Field B"
> Cancel = True
> txtFieldB.SetFocus
> Exit Sub
> End If
>
> End Sub
>
> If the
>
> Exit Sub
>
> line is omitted and more than one field is empty, Access will display both
> messages without giving the user the chance to enter data in the first empty
> filed.
>
> Likewise, if multiple fields are empty, the SetFocus allows Access to go to
> the control the current warning is talking about.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200907/1
>
>

You do have a point. With respect, in my situation, my Records Selector,
Navigation Buttons, Close Button are all set to No, Pop Up is set to Yes, and
Scroll Bars are set to Neither, so with the method I chose, my user MUST fill
out the required fields for the particular record or it wont let them leave
the form.

Cheryl

"Linq Adams via AccessMonster.com" wrote:

> And what happens if the user moves to another record or closes the form using
> the native Access "Close" button? Validation checking for empty fields should
> always be in the Form_BeforeUpdate event, as Jack and I have shown. Then the
> Validation will be done regardless of how the user closes the form or moves
> to another record.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200907/1
>
>

Thank you, yeah I am still a bit wet behind the ears at this and the
vernacular is coming a little slowly.

"Linq Adams via AccessMonster.com" wrote:

> Meant to add, please take the time to learn the names of the various objects
> in Access. You speak of moving to the next "page" and then moving to the next
> "form" and in both cases you're actually speaking of moving to the next
> record.
>
> Since Access has "pages" and "forms," it's confusing when these names are
> used incorrectly and can cause a delay in your receiving help..
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200907/1
>
>