Need code for "null" value field

[LeiDBug007]

Aloha,

We've got a form that has 7 fields, and a close command button at the bottom.

We need a code that will prevent the user from closing the form (clicking the close button) when there are null values in any fields. We need all fields filled out, and none left blank.

And of course, a message box for each field saying they cannot close the form until they enter data for that field. I can basically use the same code from the first field, and just customize to each field name w/its own message box.

Hope this makes sense. HELP!!!

Thanx so much!

~LeiDBug for DRM

 

[mgolla]

If FieldName="" or IsNull(FieldName) Then
'prevent user from closing
else
docmd.close
End if

HTH
Mike

 

[Terpsfan]

I would actually trap this in the form_unload event which really prevents the form from being closed. If cancel = true the form can't be closed...this will also set the focus to the offending control. This assumes you are using text or combo boxes. This saves you having to write the same code for each field.

Form_Unload(cancel as integer)
Dim ctl as control
For each ctl in me.controls
If ctl.ControlType = acTextBox or acComboBox Then
If IsNull(ctl) Then
Cancel = True
ctl.setfocus
MsgBox "You need to enter data here"
End If
End If
Next ctl
End Sub

 

[LeiDBug007]

TO: Omega36

Hi Omega... I am getting a compile error "Next without For". It highlights the "Next" on "Next ctl" at the end?

Thanx!
~LeiDBug007

 

[p27br]

if your form is based on a table, you can set the Required property for the fields to YES.

 

[grnzbra]

LeiDeBug007,

Check that you have the correct number of End If statements. Whenever I've gotten that kind of message in a loop, I always traced it to the wrong number of End If statements.

The code Omega gave you seems like it would generate a message box each time it hit a null text box. Personally, I don't like doing it that way. What I have done is have a line in the loop (where the msgbox line is now) set the visibility property of a "Please enter required fields" lable to true (its forecolor is 255 - red)and have another line set the forecolor of the control's label to 255. This way, you display the message along with highlighting of the empty required fields all at one time. It's not a better way to to it; it's just a preference thing.

 

[LeiDBug007]

To grnzbra,

Mind sharing me the code for your suggestion?

Thanx!

~LeiDBug007

 

[grnzbra]

I remember what I did and how I did it, but don't remember the exact code (my boss has been mucking around with permissions and now nothing works that wasn't originally written in XP, so I can't look at it). I'll give you what I remember and perhaps someone else can give you the correct syntax.

Once you have Omega's code working, comment out the msgbox statement.

Add a label somewhere on your form that says, "Please enter required fields" (or something like that). Set its properties to Visible = false and Forecolor = 255. Change the label names to match the control names with the suffix "lbl"

add a local variable,
Dim lblnm as string

Then the new code is in flower boxes
Can someone please correct the syntax.
I am trying to use the control name in
the set of controls to specify which label
I want to set the color.
msglabel is the name of the "Please enter
required fields." label.

**************************************
*Me.msglabel.visible = false *
**************************************

For each ctl in me.controls
If ctl.ControlType = acTextBox or acComboBox Then
**************************************
* lblnm = ctl.name & "lbl" *
* me.lblnm.forecolor = 0 *
**************************************
If IsNull(ctl) Then
Cancel = True
'ctl.setfocus
'MsgBox "You need to enter data here"
**************************************
* me.msglabel.visible = true *
* me.lblnm.forecolor = 255 *
**************************************
End If
End If
Next ctl
End Sub

As you can see, it's only an extra few lines. The
problem is getting the syntax correct. I know it works,
and I got the help to make it work (the syntax) right
here, about a year ago.

 

[grnzbra]

I think I found it. The only problem is picking up the control name (ctltxt), which I picked up from a field in a recordset. (I had a table that told me which parameters were needed for each report and which of those were required parameters and which were optional. So, in this, I think you could get it with:

ctltxt = ctl.name
or
ctltxt = ctl.text


str = "Inp" & ctltxt & "Lbl"
Me.Controls(str).ForeColor = 0
If required = True Then
If tovars = "" Then
Me!ParmMissed = 1
With Me!Prmpt
.Caption = "Please supply missing required parameters!"
.ForeColor = 255
End With
Me.Controls(str).ForeColor = 255
End If

End If

So, I think if you change the stuff in the flowerboxes of the previous post, you'll be close with

lblnm = ctl.text & "lbl"
or
lblnm = ctl.name & "lbl"

Me.Controls(lblnm).Forecolor = 0
and
Me.Controls(lblnm).Forecolor = 255