Ensure all fields are filled (1 Viewer)

[moishy]

Hello all,

I'm using DAO to insert data (entered in unbound controls on a unbound form) into a table.

Before running the code to insert the the data I must check that all fields on the form are not empty.

I don't want to Set the Required property of the fields to Yes since the error message produced isn't user friendly.

I believe I have to check that no field is empty either before running the appending code or on the form before update.

So what I'm asking is what code is necessary to check that all fields are filled.

I tried this in both the form before update and and before the appending code, but it didn't stop the empty data.

Dim ctl As Control
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If ctl.Value = "" Then
                    MsgBox "Missing required information." & vbCrLf  & "Please fill in all missing information and try again.",  vbCritical, "Can't Save"
                End If
            Case Else
        End Select
    Next ctl

This is the code I use for appending the data to the tables.

 Private Sub cmdUpdate_Click()

    On Error GoTo catch
    
    Dim ctl As Control
    
    Dim db As DAO.Database
    Dim rs1 As Recordset
    Dim rs2 As Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("tblContacts", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("tblRequests", dbOpenDynaset)

    
    ' Insert info from form to tables
    With rs1
        .AddNew
        ![Surname] = [txtSurname]
        ![FirstName] = [txtFirstname]
        ![Phone] = [txtPhone]
        ![Fax] = [txtFax]
        ![Email] = [txtEmail]
        .Update
    End With

    With rs2
        .AddNew
        ![RequestedCity] = [cboCity]
        ![RequestedNeighborhood] = [cboNeighborhood]
        ![RequestedRooms] = [cboRooms]
        ![RequestedPriceTop] = [txtPrice]
        ![Comments] = [txtComments]
        .Update
    End With

    ' Clear all fields
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If ctl.ControlSource = "" Then
                    ctl.Value = Null
                End If
            Case Else
        End Select
    Next ctl
    
catch:
        MsgBox "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

 

[vbaInet]

So what I'm asking is what code is necessary to check that all fields are filled.

Yes, you're on the right track but...

I don't want to Set the Required property of the fields to Yes since the error message produced isn't user friendly.

You can make this error message a user-friendly message and it also ensures that your validation is at a much more granular level, i.e. the table-level, which is actually preferrable.

In any case, with your current method you need to also add Nz() in your test because new fields are not zero-length strings, they are Null.

 

[mdlueck]

In any case, with your current method you need to also add Nz() in your test because new fields are not zero-length strings, they are Null.

... and you may refer to this thread as well concerning safely reading unbound form controls regardless of UI interaction. I found that Nz() alone could not handle both reading a control which no one had ever typed anything in AND a control which started out empty / someone typed something / then all characters were deleted. Nz() crashed with one of those scenarios, so I developed a bit of code. vbaInet also pointed out that I could standardize on setting a property of field controls different than default. I err'ed in the direction of shared code and NOT having to remember to always update a field property.

http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

 

[moishy]

Thanks for the pointers.

How can I make the error message user friendly, I know I can capture the error but how do I determine what control is null?

In any case, with your current method you need to also add Nz() in your test because new fields are not zero-length strings, they are Null.

Is that to suggest that this

If ctl.Value = "" Then

should be this

If ctl.Value = Null Then

Or is that method one that will not work?

btw, why doesn't the method I tried work ?

 

[vbaInet]

Suggestions:

If Nz(ctl.Value, vbNullString) = vbNullString Then

Or

If Len(Nz(ctl.Value, vbNullString)) = 0 Then

Or

If Len(ctl.Value & vbNullString) = 0 Then

 

[moishy]

Will the above work with all types of controls? will Len() and Nz() actually work for option groups, check boxes and radio buttons?

 

[vbaInet]

Sure will!

 

[moishy]

Thank You.

 

[vbaInet]

No probs!

 

[moishy]

One more problem...

I want to change the border of the empty controls. I tried this but no changes are made to the controls.

    ' Ensure all fields are filled
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If Len(Nz(ctl.Value, vbNullString)) = 0 Then
                    With ctl
                        .BorderColor = vbRed    ' Red
                        .BorderWidth = 2    ' 2 points
                        .BorderStyle = 1    ' Solid
                    End With
                    MsgBox "Missing required information." & vbCrLf & "Please fill in all missing information and try again.", vbCritical, "Can't Save"
                    Exit Sub
                End If
        End Select
    Next ctl

 

[vbaInet]

Start with changing the Style before setting the other properties.

Have you stepped through your code? Does the msgbox fire?

 

[moishy]

I sorted it out. Here is the working code.

    ' Ensure all fields are filled
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If Len(Nz(ctl.Value, vbNullString)) = 0 Then
                        With ctl
                            .BorderColor = vbRed            ' Red
                            .BorderWidth = 1                ' 1 point
                            .BorderStyle = 1                ' Solid
                        End With
                End If
        End Select
    Next ctl
    MsgBox "Missing required information." & vbCrLf & "Please fill in all missing information and try again.", vbCritical, "Can't Save"
    Exit Sub

Thank you.

 

[vbaInet]

What went wrong?

 

[moishy]

The only thing is the borders of acCheckBox and acOptionButton (when added to the case statment) aren't affected.

Any ideas?

 

[moishy]

What went wrong?

The location of the message box and the Exit Sub were in middle of the loop so it didn't continue after the first one.

 

[vbaInet]

Just the way they were built I guess. You can try putting a control behind it and format that or use this:

http://allenbrowne.com/ser-52.html

 

[moishy]

Is there something inherently wrong with check boxes and option buttons?

 

[vbaInet]

Nope! Just how Microsoft chose to design them.

 

[moishy]

HaHa.

But they do have the properties I'm trying assign them.

 

[vbaInet]

It works only when it has Shadows.