How to check if all controls are null 1

[mrbboy]

I have a form for recording client complaints. The first control is a combo box, cbo_PM, where people can select their names from a drop down list. The rest of the text boxes and combo boxes in the form is for documenting other aspects of the complaint. The problem I have is that people sometimes forget to select their name in cbo_PM. So what I have is a customer complaint without knowing who initiated the complaint.

What I need is to somehow check all the text and combo boxes and if they are not null and cbo_PM is not null, then they can submit the complaint. If any of the text boxes or combo boxes are not null and cbo_PM is null, then it'll prompt them to select their name before allowing them to exit.

Is this possible?

 

[KenReay]

Hi

Not sure which part you dpn't know how to do

to test for Null try IsNull()

But better, for text fields is
If len(Trim(nz(controlname,"")&"")) = 0 Then ...

which also catches situations where user enters a space but no other data

If you want to check n controls FOR Each construct would be the thing to look at

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[randy700]

How about...

When the form opens, all controls are locked or disabled except the drop down. Forces the user to select his/her name first. Once selected, unlock/enable the rest of the controls.

I've done this by putting the drop down in the header. Then, I can lock/disable the detail section when the form is opened and unlock/enable it when a selection is made.


Randy

 

[mrbboy]

Randy,

Can you show me hoe this is done?

 

[randy700]

Sorry... should have said VISIBLE.

In design view, select the detail section. In the properties window, set the Visible property to NO.

Place your combo box in the Form Header. In the AfterUpdate event of the combo box, add code something like...

Private Sub cboName_AfterUpdate()
    Me.Detail.Visible = True
End Sub

The form will open with only the header visible. Once the user selects a name from the combo box, the detail section will become visible.

Alternatively, you could set the locked property to YES or the enabled property to NO on all controls. In the AfterUpdate event, you would change those properties. With this method, the user will see the text boxes, but won't be able to enter anything until after making a selection from the combo box. And, with this method, you can keep the combo box in the detail section if you prefer.

Something like...

Private Sub Combo0_AfterUpdate()
    Dim ctl As Control
    For each ctl in Me
        If ctl.TypeOfControl = "acTextBox" Then
            ctl.Enabled = Yes
[COLOR=red][b]OR[/b][/color]          ctl.Locked = No
        End If
    Next ctl
End Sub

Randy

 

[missinglinq]

A couple of points. First, it's a common misconception that a user entering a space, or deleting data from a textbox, by hiliting the data and hitting <Delete> or <SpaceBar>, or by using <BackSpace>, leaves the control in a non-Null state, but that's simply not true! All of these things return the field to Null, and so in the situation where data is being input by a user, code like

len(Trim(nz(controlname,"")&"")) = 0 Then

is simply not needed.

IsNull(Me.TextboxName)

is more than satisfactory.

If you doubt this, run up a form with a textbox and command button and this code:

Private Sub CheckForNull_Click()
 If IsNull(Me.TextboxName) Then MsgBox "TextboxName is null"
End Sub

Now try all the variations I mentioned above; the field will always pop the messagebox!

Now having said that, code like Ken's would be useful if

1) Data was being imported from another app, like Excel or a CVS file

2) The developer ever uses

Me.TextboxName = ""

to clear out the contents of the control

The former has the possibility of importing records with only spaces in a given field, while the latter will always create a non-Null state.

Second point,

If ctl.TypeOfControl = "acTextBox" Then

should be

If ctl.TypeOfControl = acTextBox Then

acTextBox

is a constant, it doesn't need the quotations marks.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[joebb3]

Thanx Randy! That was the code I needed for MY project. There were just few bugs in your code in ACC2003, but it got me where I needed to be!

I wrote a code to toggle Enable/Unlock on all the text boxes on the form (based on your code).

    Dim ctl As Control
    For Each ctl In Me
        If ctl.ControlType = "109" Then
             'Lock or unlock
             If ctl.Locked = False Then
             ctl.Locked = True
             ElseIf ctl.Locked = True Then
             ctl.Locked = False
             End If
             'Enable or unenable
             If ctl.Enabled = True Then
             ctl.Enabled = False
             ElseIf ctl.Enabled = False Then
             ctl.Enabled = True
             End If
        End If
    Next ctl
'Toggle Button Caption for next use
If Me!Command42.Caption = "Unlock for Editing" Then
Me!Command42.Caption = "Lock Form"
ElseIf Me!Command42.Caption = "Lock Form" Then
Me!Command42.Caption = "Unlock for Editing"
End If

It is run by pressing a button (Command42) on the form.

Once again... Thanx!

Joe