Required Fields on Form

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to make
a
separate post. On the positive side, at least they know what is required and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in. Therefore,
if
the form is left completely blank, no record is created so it does not need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are populated
it
closes fine. Also if the user does not populate any fields and clicks save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before Update.
Having the code in the fields event would produce the error message when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure you
select Form from the dropdown box at the very top of the Properties box.

Also, I actually built this to make sure that it works for you, instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news
Ok I figured out the reason I wasn't getting the error message from the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better on
click
for the save button instead of checking as they move between fields. I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel =
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Maybe that's where I'm going wrong. I have 5 fields and all 5 are
required.
When the user hits Save and Close, I need it to then validate that
all
5
fields are populated before Saving and Closing the form. If they are
not,
I
would like a message to appear that indicates which field is not
populated
and take the cursor to that field. Then to repeat this for every
field
that
is not populated each time the user clicks Save and Close.
Ultimately
the
form will not write any data to the table or close the form until
all
fields
are populated.

I also tried making the fields required in the table, but didn't see
any
difference when clicking Save and Close on the form.

It might also be helpful to know that the tables are stored on a be
file
and
the forms are on an fe file. I made the fields required on the be
tables,
but
this did not change the user form experience.

"CJ" wrote:

Hi Sprinks

Not that I am a code pro or anything but I would have written it
as:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

Also, I would put it into the OnClick event of your button before
any DoCmd statements.

The way that you have it written suggests that it would execute
when
you move from one record to the next but not when you save and
close.

--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

"Whitney" wrote in message
...
I tried to use this in the BeforeUpdate event, but it's not
working.
On
my
form after all information is updated the user hits the Save and
Close
button. Then the form closes, but no error pops up. Help!

If IsNull(Me!Caller) Then
MsgBox "Please fill in Caller's Name."
Cancel = True
Me!Caller.SetFocus
End If

"Sprinks" wrote:

For this job, use the *form's* BeforeUpdate event. For each
required
control, use code similar to:

If IsNull(Me!YourRequiredControl) Then
MsgBox "Please fill in YourRequiredControl"
Cancel = True
Me!YourRequiredControl.SetFocus
End If

HTH
Sprinks

"Raj" wrote:


I need help with code that will verify if certain fields have
been
filled
in. I dont want to use field required at the table level. I
want
to
use
BeforeUpdate can someone please help me with the coding..
Thanks!!!

I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says "No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each message and
then the form closes, when it should just ignore the validation code and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to make
a
separate post. On the positive side, at least they know what is required and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in. Therefore,
if
the form is left completely blank, no record is created so it does not need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are populated
it
closes fine. Also if the user does not populate any fields and clicks save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before Update.
Having the code in the fields event would produce the error message when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure you
select Form from the dropdown box at the very top of the Properties box.

Also, I actually built this to make sure that it works for you, instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message from the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better on
click
for the save button instead of checking as they move between fields. I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel =
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Maybe that's where I'm going wrong. I have 5 fields and all 5 are
required.
When the user hits Save and Close, I need it to then validate that
all
5
fields are populated before Saving and Closing the form. If they are
not,
I
would like a message to appear that indicates which field is not
populated
and take the cursor to that field. Then to repeat this for every
field
that
is not populated each time the user clicks Save and Close.
Ultimately
the
form will not write any data to the table or close the form until
all
fields
are populated.

I also tried making the fields required in the table, but didn't see
any
difference when clicking Save and Close on the form.

It might also be helpful to know that the tables are stored on a be
file
and
the forms are on an fe file. I made the fields required on the be
tables,
but
this did not change the user form experience.

"CJ" wrote:

Hi Sprinks

Not that I am a code pro or anything but I would have written it
as:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

Hi

What is the code for your Cancel button?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each message
and
then the form closes, when it should just ignore the validation code and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to
make
a
separate post. On the positive side, at least they know what is required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and clicks
save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message from
the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better
on
click
for the save button instead of checking as they move between fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Maybe that's where I'm going wrong. I have 5 fields and all 5 are
required.
When the user hits Save and Close, I need it to then validate
that
all
5
fields are populated before Saving and Closing the form. If they
are
not,
I
would like a message to appear that indicates which field is not
populated
and take the cursor to that field. Then to repeat this for every
field
that
is not populated each time the user clicks Save and Close.
Ultimately
the
form will not write any data to the table or close the form until
all
fields
are populated.

I also tried making the fields required in the table, but didn't
see
any
difference when clicking Save and Close on the form.

It might also be helpful to know that the tables are stored on a
be
file
and
the forms are on an fe file. I made the fields required on the be
tables,
but
this did not change the user form experience.

"CJ" wrote:

Hi Sprinks

Not that I am a code pro or anything but I would have written it
as:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

Make sure that you do not have any code in the Before Update
event for any of your fields. It sounds like there is other code that
has not been cleaned up and is causing interference.

If you think you need other code you need to post it so that
we can see the whole picture.

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each message
and
then the form closes, when it should just ignore the validation code and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to
make
a
separate post. On the positive side, at least they know what is required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and clicks
save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message from
the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better
on
click
for the save button instead of checking as they move between fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Maybe that's where I'm going wrong. I have 5 fields and all 5 are
required.
When the user hits Save and Close, I need it to then validate
that
all
5
fields are populated before Saving and Closing the form. If they
are
not,
I
would like a message to appear that indicates which field is not
populated
and take the cursor to that field. Then to repeat this for every
field
that
is not populated each time the user clicks Save and Close.
Ultimately
the
form will not write any data to the table or close the form until
all
fields
are populated.

I also tried making the fields required in the table, but didn't
see
any
difference when clicking Save and Close on the form.

It might also be helpful to know that the tables are stored on a
be
file
and
the forms are on an fe file. I made the fields required on the be
tables,
but
this did not change the user form experience.

"CJ" wrote:

Hi Sprinks

Not that I am a code pro or anything but I would have written it
as:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

I was missing the first docmd, so I added your suggestion. It works when one
field is populated, but now I get an error when canceling and there are no
fields populated.
The command or action 'Undo' isn't available now.

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

"CJ" wrote:

Hi

What is the code for your Cancel button?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each message
and
then the form closes, when it should just ignore the validation code and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to
make
a
separate post. On the positive side, at least they know what is required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and clicks
save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message from
the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better
on
click
for the save button instead of checking as they move between fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--

Hi Whitney

It sounds like you have other code in place for your fields
or form that is causing interference.

You should have the following and only the following:

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

For your Close button in the On Click event you should have:

On Error GoTo Err_Save_Click

DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click

For your Cancel button in the On Click event you should have:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close


You should not have any code in any of the Events for any of your fields.

If you clean up any extra code that you have and you are still getting
error messages, I think you should start a new thread and deal
with each one individually. I think I'm out of ideas on this.

--
Good Luck

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I was missing the first docmd, so I added your suggestion. It works when
one
field is populated, but now I get an error when canceling and there are no
fields populated.
The command or action 'Undo' isn't available now.

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

"CJ" wrote:

Hi

What is the code for your Cancel button?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one
field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each
message
and
then the form closes, when it should just ignore the validation code
and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have
to
make
a
separate post. On the positive side, at least they know what is
required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would
be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few
more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for
each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the
first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and
clicks
save
and close it closes the form without the error messages, this should
be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to
either
move
to the next record (Caller) or save and close the form, then you
MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you
want.
To make sure you are putting the code in the correct event, make
sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message
from
the
required table fields validation rule. I had the test fe file
still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is
too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more
straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be
better
on
click
for the save button instead of checking as they move between
fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command,
see
below.
However I get an error message Run-time error '438': Object
doesn't
support
this property or method. When I click Debug it points to the
Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should
definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use
your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code
for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back
on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you
push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--

Also, look back in your table and make sure that you
do not have Required set to yes and a bunch of
data validation rules. You do not need these
kinds of controls in both locations.

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

"Whitney" wrote in message
...
I was missing the first docmd, so I added your suggestion. It works when
one
field is populated, but now I get an error when canceling and there are no
fields populated.
The command or action 'Undo' isn't available now.

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

"CJ" wrote:

Hi

What is the code for your Cancel button?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one
field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each
message
and
then the form closes, when it should just ignore the validation code
and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have
to
make
a
separate post. On the positive side, at least they know what is
required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would
be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few
more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for
each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the
first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and
clicks
save
and close it closes the form without the error messages, this should
be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to
either
move
to the next record (Caller) or save and close the form, then you
MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you
want.
To make sure you are putting the code in the correct event, make
sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message
from
the
required table fields validation rule. I had the test fe file
still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is
too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more
straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be
better
on
click
for the save button instead of checking as they move between
fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command,
see
below.
However I get an error message Run-time error '438': Object
doesn't
support
this property or method. When I click Debug it points to the
Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should
definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use
your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code
for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back
on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you
push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--

Ok one last try and then I'm giving up and sticking with the ugly validation
rule error message.
I removed the validation rule from the table. All fields are set to No for
required.

Here is all of my current code for this form:

Option Compare Database


Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 (with this
line I don't get the error messages when cancelling with at least on field
populated, but I do get an error message that says "The command or action
'Undo' isn't available. Without this line I get the required field error
messages before the form closes.)
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Caller's name is required."
Me.Caller.SetFocus

End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Date is required."
Me.Date.SetFocus

End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Reason for call is required."
Me.Reason.SetFocus

End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Your name is required."
Me.Submitter.SetFocus

End If
End Sub

Private Sub Save_Click()
On Error GoTo Err_Save_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
(without this line I get the required field error messages, but the form
closes. With this line I get the extra error message "No Current Record")
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub




Private Sub Undo_Click()
On Error GoTo Err_Undo_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Undo_Click:
Exit Sub

Err_Undo_Click:
MsgBox Err.Description
Resume Exit_Undo_Click

End Sub


"CJ" wrote:

Hi Whitney

It sounds like you have other code in place for your fields
or form that is causing interference.

You should have the following and only the following:

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

For your Close button in the On Click event you should have:

On Error GoTo Err_Save_Click

DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click

For your Cancel button in the On Click event you should have:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close


You should not have any code in any of the Events for any of your fields.

If you clean up any extra code that you have and you are still getting
error messages, I think you should start a new thread and deal
with each one individually. I think I'm out of ideas on this.

--
Good Luck

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I was missing the first docmd, so I added your suggestion. It works when
one
field is populated, but now I get an error when canceling and there are no
fields populated.
The command or action 'Undo' isn't available now.

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

"CJ" wrote:

Hi

What is the code for your Cancel button?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one
field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each
message
and
then the form closes, when it should just ignore the validation code
and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have
to
make
a
separate post. On the positive side, at least they know what is
required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would
be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few
more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for
each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the
first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and
clicks
save
and close it closes the form without the error messages, this should
be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to
either
move
to the next record (Caller) or save and close the form, then you
MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you
want.
To make sure you are putting the code in the correct event, make
sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message
from
the
required table fields validation rule. I had the test fe file
still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is
too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more
straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be
better
on
click
for the save button instead of checking as they move between
fields.
I
want
to give them the flexibility to populate the fields in any order

I am have a problem with required fields. I read these postings and I can't
get mine to work. This is what I have done and please correct my errors. I
opened my form in design view, selected properties, selected forms from the
drop down menu and clicked" ..." in Before Update row. Within the code
builder I wrote:
The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
changing what I thought were fields in this example. Is "Caller" the field
name in the table the form is referencing or is "Caller" the name of the text
box inside the form? Once I get this to work correctly, will the error
message once the form is closed?

Thanks

"CJ" wrote:

Make sure that you do not have any code in the Before Update
event for any of your fields. It sounds like there is other code that
has not been cleaned up and is causing interference.

If you think you need other code you need to post it so that
we can see the whole picture.

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
I get the No Current Record as the last error message in the sequence of
error messages. So if I populate all fields except one, I get the error
message for that field, click ok and then get another message that says
"No
Current Record", then click Ok and then it takes me back to the form.

Also I do have a cancel button, when the user populates at least one field
and then decides to cancel they get all of the error messages for the
remaining unpopulated fields. They then have to click ok to each message
and
then the form closes, when it should just ignore the validation code and
close the form.

"CJ" wrote:

snipI'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if anysnip

Yep, me too but I haven't figured out how to do that yet. Might have to
make
a
separate post. On the positive side, at least they know what is required
and
the
odds of somebody leaving all of that data should be pretty slim. Users
become
trained as to what is expected.

When does the No Current Record message show up? After the error
messages when you move to another record or after the error
messages after you have pushed your close button?

To cancel and close the form, create a CANCEL button. Your code would be:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close

Access does not create a new record until the user types data in.
Therefore,
if
the form is left completely blank, no record is created so it does not
need
to check for invalid data.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
...
Thanks CJ for the clarification. I'm almost there, but have a few more
issues.

I added the code to the form_BeforeUpdate, so now if they have only
populated one of the four required fields they get a message for each
unpopulated field before going back to the form. I don't really like
that,
but willing to accept it. I'd rather it give the message for the first
unpopulated field go back to the form to populate it, then when they
click
save and close it gives the next unpopulated field, if any.

Also when all of the messages pop up, the last message is always No
Current
Record. I'm not sure why this is happening.

My last issue, is that if the user populated one field, but then
decides
to
cancel the form, it also gives the error messages that fields are
required.
How can I prevent this and just close the form? If no fields are
populated
it
closes fine. Also if the user does not populate any fields and clicks
save
and close it closes the form without the error messages, this should be
opposite.


"CJ" wrote:

First thing, the difference between fields and records:
A field is each piece of data that you complete for each record.
So, if you have data for Charlie Brown:
Charlie would be in the first name Field,
Brown would be in the last name Field.
All of his data together is Charlie Brown's Record.

This is important because you need to understand when Access events
run.
If you want the validation to only happen when the use tries to either
move
to the next record (Caller) or save and close the form, then you MUST
have
the code in the FORMS Before Update event NOT the FIELDS Before
Update.
Having the code in the fields event would produce the error message
when
the user tries to move to the next field...this is not what you want.
To make sure you are putting the code in the correct event, make sure
you
select Form from the dropdown box at the very top of the Properties
box.

Also, I actually built this to make sure that it works for you,
instead
of
trying to wing it....=D Feel free to copy and paste.....

The code for your Form Before Update event should be:

If IsNull(Me.Caller) Then
DoCmd.CancelEvent
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
DoCmd.CancelEvent
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
DoCmd.CancelEvent
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
DoCmd.CancelEvent
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If

The code for your Close button should be:

On Error GoTo Err_Save_Click

DoCmd.Close
' You do not need to tell Access to save the data.
' It is saved automatically when the form closes

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox "Unable to Save and Close"
Resume Exit_Save_Click


--
HTH

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
"Whitney" wrote in message
news Ok I figured out the reason I wasn't getting the error message from
the
required table fields validation rule. I had the test fe file still
referrerencing the original be file instead of the test be file
where I
made
the table changes. Silly me.
So now I'm getting the error message, which I understand but is too
complicated for a user to understand.

So I'm still using the vb code in order to provide a more straight
forward
error message, however it's not working.
I wasn't sure where to put your code, as I think it might be better
on
click
for the save button instead of checking as they move between fields.
I
want
to give them the flexibility to populate the fields in any order
without
the
error messages, but to validate when they click save and close.
So I added the code on click, before the save and close command, see
below.
However I get an error message Run-time error '438': Object doesn't
support
this property or method. When I click Debug it points to the Cancel
=
True
line of the IF Is Null (Me. Caller) code.

Not sure what I'm doing wrong.

Private Sub Save_Click()
If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If
If IsNull(Me.Date) Then
Cancel = True
MsgBox "Please fill in Date."
Me.Date.SetFocus
End If
If IsNull(Me.Reason) Then
Cancel = True
MsgBox "Please fill in reason for call."
Me.Reason.SetFocus
End If
If IsNull(Me.Submitter) Then
Cancel = True
MsgBox "Please fill in your name."
Me.Submitter.SetFocus
End If
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

"CJ" wrote:

Hi Whitney

If you made the fields required in the table you should definitely
see
an
error message
if the field is left blank on your form.

Try a test:
Make all of the fields required in your table that need to be
required
and then try just navigating to a different record. Don't use your
button.
Unless you have your warnings turned off via code you should
receive
an error message.

If you do not receive a message, you need to check in the code for

DoCmd.SetWarnings False

If some code requires the warnings to be
turned off then you need to make sure that it gets turned back on
with

DoCmd.SetWarnings True

after the code has executed.

I should also have mentioned that you do need to keep the code
in the forms BeforeUpdate event. BeforeUpdate checks before the
record
is saved and you move between records, OnClick checks when you push
the
button.

For both events, your code would be something like:

If IsNull(Me.Caller) Then
Cancel = True
MsgBox "Please fill in Caller's Name."
Me.Caller.SetFocus
End If

If IsNull(Me.Phone) Then
Cancel = True
MsgBox "Please fill in Caller's Phone Number."
Me.Phone.SetFocus
End If

If IsNull(Me.Address) Then
etc, etc....

--