Required Fields on Form

[Raj]

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!!!

 

[Sprinks]

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]

THANK YOU!!

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

 

[Whitney]

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

 

[CJ]

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.

 

[Whitney]

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]

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....

 

[Whitney]

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]

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

 

[Whitney]

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]

<snip>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<snip>

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.

 

[Whitney]

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]

Hi

What is the code for your Cancel button?

 

[CJ]

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

 

[Whitney]

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]

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.

 

[CJ]

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

 

[Whitney]

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

 

[kyle12345]

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: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