Force data entry of certain fields on a form

[Sara]

Hi, I have an unbound form that only when the user clicks submit it add the
new record to a Complaints table. Now, I am trying to enforce certain fields
such as textboxes and comboboxes. I have tried the suggestions on previous
posts such as

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.name) Then
MsgBox "Please Enter Data"
Me.name.SetFocus
Cancel = True
End If
End Sub

But it doesnt work, it still allows me to add a new record without entering
a name.

I have also tried to add the code on submit :


Private Sub Submit_Click()
On Error GoTo Err_Submit_Click
If IsNull(Name) Then
MsgBox "Please enter Terminal"
Name.SetFocus
Cancel = True
End If

If MsgBox("Are you sure you want to add a new inquiry?", vbYesNo) = vbYes Then
Dim stDocName As String
stDocName = "APPEND - DATA"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Source = Null
Equipment = Null
Terminal = Null
Train = Null
Pending = Null
Comments = Null
ProblemName = Null
ProblemDescription = Null
Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

Else
Cancel = True
End If

End Sub



Whit this last code, it prompts me to enter a Name but inmediately after
that asks me if I wan to "Add a new inquiry" and If I click yes it allows me
to add it without filling the required fields. How can I correct this? what
do I need to add?
I appreciate your help

 

[missinglinq via AccessMonster.com]

The problem is that you've named your control *name* and name is a property
of controls!

Me.Name yields the name of your form! Since your form has a name, Me.Name
isn't null! Rename your control to something else such as NName or txtxName
and it should work!

 

[Mr. B]

Hi, I have an unbound form that only when the user clicks submit it add the
new record to a Complaints table. Now, I am trying to enforce certain fields
such as textboxes and comboboxes. I have tried the suggestions on previous
posts such as

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.name) Then
MsgBox "Please Enter Data"
Me.name.SetFocus
Cancel = True
End If
End Sub

But it doesnt work, it still allows me to add a new record without entering
a name.

I have also tried to add the code on submit :

Private Sub Submit_Click()
On Error GoTo Err_Submit_Click
If IsNull(Name) Then
MsgBox "Please enter Terminal"
Name.SetFocus
Cancel = True
End If

If MsgBox("Are you sure you want to add a new inquiry?", vbYesNo) = vbYes Then
Dim stDocName As String
stDocName = "APPEND - DATA"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Source = Null
Equipment = Null
Terminal = Null
Train = Null
Pending = Null
Comments = Null
ProblemName = Null
ProblemDescription = Null
Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

Else
Cancel = True
End If

End Sub

Whit this last code, it prompts me to enter a Name but inmediately after
that asks me if I wan to "Add a new inquiry" and If I click yes it allows me
to add it without filling the required fields. How can I correct this? what
do I need to add?
I appreciate your help

Sara,

The way I normally handle this situation is:

Next, I set the enable property of my command button to false. Then I
use code to check to see when it is appropriate for the user to be
able to click the "Submit" button and when the appropriate criteria is
met, I have my code to enable the command button.

First I create a function in the forms module that will check for any
and all required data entry. Something like:

Function ChkForReqData()
If IsNull(Me.txtBox1) Then
Me.cmdSubmit.Enabled = False
Else
Me.cmdSubmit.Enabled = True
End If
End Function

Of course, you would change the name of the text box (txtBox1) to the
actual name of your text box.

If you need to check more than one field for data entry the function
would include a check for data in all required fields. Something
like:

Function ChkForReqData()
'check two fields to assure at least some value entered
If IsNull(Me.txtBox1) And IsNull(Me.txtBox2) Then
Me.cmdSubmit.Enabled = False
Else
Me.cmdSubmit.Enabled = True
End If
End Function

You may find that, in some cases, you would actually need to check for
specific perameters. You could include these perameters in the same
function.

Then, in the After Update event of each of the controls on my form, I
place a call to my function:

Private Sub txtBox1_AfterUpdate()
ChkForReqData
End Sub

The function will check for the values that are to be provided and
will enable or disable the "Submit" command button depending on the
entries the user has provided.

The prevents your user from even having the option to click a button
or other action until your requirements are met.

HTH

Mr. B

 

[missinglinq via AccessMonster.com]

Her original code was fine, she just needs to change the name of her control
to something other than
* Name *

 

[Sara]

Thanks for the reply, I just used Name as an example for the post my control
is actually call "Terminal" which is a combo box, and the other is "Train"
which is a text box. So there is no problem with the name, I don't understand
why the code is not working.
Also, you said the code was correct which one the code in the Before_Update
event or the Code on submit? I gave 2 examples on how I was approaching this.

 

[Sara]

Thanks, for your reply, what you proposed is an interesting approach, but my
boss wants to see the sumbmit button, Why? I don't know, would you be able to
guide me in this?

 

[Mr. B]

Thanks, for your reply, what you proposed is an interesting approach, but my
boss wants to see the sumbmit button, Why? I don't know, would you be able to
guide me in this?
















- Show quoted text -

Sara,

I was not proposing that you make the button to be not visible. It
would remain visible, just not enabled.

Try implementing the proposed method just as I posted it. If you run
into specific problems, post back and I'm sure I or someone else will
be able to assist you in getting it to work in your applicaiton.

It's really not that difficult. :>)

HTH

Mr B

 

[missinglinq via AccessMonster.com]

I just read the entire posting again and realized that this is an unbound
form! The rules are all different for unbound forms. I haven't dealt with
them for a long time, but if I remember correctly they don't actually have a
BeforeUpdate event, so naturally your original code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.name) Then
MsgBox "Please Enter Data"
Me.name.SetFocus
Cancel = True
End If
End Sub

which is standard validation code for BOUND forms, won't work. The code you
have behind your submit button won't work for bound or unbound, in that if
you look at the first line of your Click sub

Private Sub Submit_Click()

you'll see that the standard Click sub doesn't have (Cancel as Integer) at
the end of it, so

Cancel = True

won't work in this kind of sub.

Sorry, you'll have to get advice from someone who uses unbound forms on a
regular basis.

Good Luck

 

[LarryP]

Folks have given you various useful advice already, but if your boss has his
heels dug in about seeing the Submit button enabled, here's a way to go. Set
up a global Boolean variable, e.g., ReadyToSubmit. Then, when Submit is
clicked,
(1) Set that variable to False.
(2) Run a series of IF statements to check each of your required controls;
in each IF statement,
--if the contents are OK, set the variable to True and move on to the
next IF.
--if not, set it to False, display an appropriate messagebox, such as
"You have not provided xxxxx -- please do so before clicking <Submit>!" Then
DoCmd.CancelEvent.
(3) After all the control-ckecking IF statements, add one more IF that
checks the Boolean value. If it gets to this point and still shows True, go
ahead and run the necessary code to write the new record; that means all your
controls passed the test. If it gets this far and shows False, display an
appropriate Messagebox and then Exit Sub without creating the new record.