Returning the names of fields if IsNull equals True

[beacon]

Hi everybody,

[Access 2003]

I'm trying to determine if there isn't a quicker way to do what I'm considering. I have a form with roughly ten fields; some text boxes, some combo boxes, some option groups.

I have a submit command that I'm using to validate the form and whether or not the user has entered something in each field. I know I could write the following:

Private Sub cmdSubmit_Click()
 
Dim subVal
 
If IsNull(Combo1.Value) Or IsNull(Text2.Value) Or IsNull(OptionGroup3.Value) Then
     MsgBox "One of the fields has a NULL value. This is not allowed", vbExclamation, "Null Value"
     Exit Sub
End If
 
End Sub

What I would like to do is return the name of the field(s) in the message box, and possibly highlight the label red to point out to the user what fields they forgot to fill in.

I know I could also set the fields to be required, but I want the message box to say something to the effect of:

"The following fields are blank:

Combo1
Text2
OptionGroup3

Please fill in these fields before submitting"

I'm trying to stay away from a huge nested if/else conditional statement, but understand that may be my only option. If anybody has any ideas, I'm all ears.

Thanks,
beacon

[OldBirdman]

Private Sub cmdSubmit_Click()
Dim strError
strError = ""
If IsNull(Combo1.Value) then strError = strError & vbNewLine & Combo1
If IsNull(Text2.Value) then strError = strError & vbNewLine & Text2
If IsNull(OptionGroup3) then strError = strError & vbNewLine & OptionGroup3
If Len(strError) <> 0 then
    'Errors encountered
    strError = "The following fields are blank:" & vbNewLine & _
                   strError & vbNewLine _
                   "Please fill in these fields before submitting"
End If
End Sub 
 

Or for TextBoxes and ComboBoxes you could use conditional formatting. As long as the field is a zero-length string, the BackColor is not white. This highlights the progress dynamically as the form is filled in. Your cmdSubmit would be the same as you write, but need only say "Fields shown in RED are have not been entered. Submit Cancelled".

This method works for continuous forms also, but will not work for options groups. You would have to change these to comboboxes.

A more dynamic method for single forms would be to set backcolor to RED if the field were invalid, whether filled in or not, or if it contains an invalid entry. User is "Rewarded" when Red changes to White, signalling a valid entry. For textboxes, this can be done as each character is entered, and validation rules can be very complex and mutually dependent. Now the cmdSubmit becomes "If Text1.BackColor = 256 or Text2.BackColor = 256 or Frame1.BackColor = 256 or . . . then MsgBox . . .items in Red are not valid - Submit Cancelled"

OldBirdman

[beacon]

Hi OldBirdMan,

Thank you for the response, but I think you may have missed the point of my question. I'm trying to validate programmatically whether or not a field has had data entered into it prior to the submit command being selected.

If the user doesn't fill in one or more of the fields and selects the submit command, I want a message box to appear that states that the user forgot to fill in required fields and then lists the names of the fields in the message box.

I'm pretty sure I can figure out how to add the color to the labels if I can figure out how to check the fields.

Maybe it would be better if I described what I'm trying to do in a different way:

• Loop through the form
• If a field is blank, remember the field or label name (I think label would be cleaner)
• When end of form is reached, display a message box that returns all field names

[ChipR]

To loop through the form, you can do:

dim ctrl as Control
 
For Each ctrl in Me.Controls
    If ctrl.ControlType = acTextBox Then
        ctrl.BackColor = 255
        strControlList = strControlList & ctrl.Name & ", "
    End If
Next

Just as an example. You may have to Select Case on control type for what you want to do.

[OldBirdman]

For a solution that does exactly what you ask (I think), I submit the following code. It requires each control that needs to be checked have the Tag property set to "Required". This stops the test from checking Labels, non-entry TextBoxes, Command Buttons (including the Submit Button), and other controls on the form. If you want to also check for zero-length text, change the IsNull(cntl.Value) to nz(cntl.Value) = "" for text & combo boxes.

Private Sub cmdSubmit_Click()
Dim cntl As Control
Dim strMessage As String
strMessage = ""
For Each cntl In Me.Controls
Select Case cntl.ControlType
Case acTextBox
If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
strMessage = strMessage & vbNewLine & cntl.Name
Case acComboBox
If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
strMessage = strMessage & vbNewLine & cntl.Name
Case acOptionGroup
If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
strMessage = strMessage & vbNewLine & cntl.Name
End Select
Next
 
If strMessage <> "" Then _
MsgBox "The following fields are blank:" & vbNewLine & _
strMessage & vbNewLine & vbNewLine & _
"Please fill in these fields before submitting"
End Sub
 

Suggestion - Use the Tag property to give a more user-friendly name to the control. The Access-generated names Combo1, Text2, and so forth aren't helpful for users or programmers, nor are the more logical patterns such as cbxCombo1, txtText2, . . . could have in .Tag "State" or "Customer Name" and then modify the tests (above) to "If cntl.Tag <> "" Then . . ."

OldBirdman

[beacon]

Hi OldBirdman,

I ended up using the method that I didn't want to use that involved writing a ton of nested if statements, but I do want to try this code out on a copy of this database when I finish it.

As for the Combo1, Text2 issue...I always use useful, specific names for my controls, objects, functions, et cetera and only used the generic in this instance to help indicate which types of controls I would be working with.

I imagine it will be a good heads up for those users that might read this and don't have any programming experience.

[OldBirdman]

Of course you do, and I understand using generic names in problems submitted to this forum.

However, one of my issues with Microsoft is presenting internal stuff to me, a user of their products. "Connection to server is reset" in my browser means "Connection broken". I try to program for a new user, even if it is me. Nice is in the details.

If you are like me, you need different names for the program (and programmers) than for the users. A textbox would have a name like txtCustomerName, but the label for that textbox might have the caption "Customer Name:", or "Enter Customer Name". An error message to the user might want to use none of these, but use "Customer Name" in your list of missing entries. Comboboxes often have labels beginning with "Select . . . " or "Choose . . . ". I personally prefer:
"The following fields are blank:

Customer Name
Customer's State

Please fill in these fields before submitting"

instead of:
"The following fields are blank:

txtCustomerName
cbxCustomerState

Please fill in these fields before submitting"

The "Tag" property is an easy way to do this. But this is only my idea of a good product.

OldBirdman

[beacon]

OBM,

I couldn't agree with you more. I work in a support department and constantly receive phone calls from users about error messages they receive using particular programs we use and it seems as though none of the developers take the user lack of programming knowledge into consideration when writing code.

It would seem beneficial to return an error message that had something useful for the user "Customer Name" AND something useful for the programmer, possibly in parenthesis (cboCustName).

It's always good to see other programmers in here that take error handling seriously. I'll be the first to admit that my programming skills aren't where they could be, especially in regards to error handling, but I think I make up for it by trying to make the db's I create easy to use.

Anyway...sorry for the diatribe and thank you again for all of your help!

beacon

[OldBirdman]

Start your error message with a code, i.e. "xa042 - The following fields . . ." This will be ignored by the user (and probably not even noticed, but will allow any programmer to easily find the code where the message is generated. More important is to see the code behind the message than the control in error, as the error is not generated by an event raised by the control.

OldBirdman

[SusanK4305]

Hi i was reading ya'lls thread and thought I might be able to use the first code to do what I need. However, I need it to get the user to click the save command button (i.e. Command165). Once all Errs are corrected then that same save button will save the record. These are the 2 codes I have but I am missing somthing.I don't know much about coding so if you could brake it down for me pleas.


Private Sub Command165_Click()
On Error GoTo Err_Command165_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command165_Click:
Exit Sub

Err_Command165_Click:
MsgBox Err.Description
Resume Exit_Command165_Click

End Sub

__________________________________________________ ___
Private Sub Command165_Click()
Dim strError
strError = ""

If IsNull(Social_Security_Number.Value) Then strError = strError & vbNewLine & Social_Security_Number
If IsNull(LastName.Value) Then strError = strError & vbNewLine & LastName
If IsNull(FirstName.Value) Then strError = strError & vbNewLine & FirstName
If Len(strError) <> 0 Then
'Errors encountered
strError = "The following fields are blank:" & vbNewLine & _
strError & vbNewLine _
"Please fill in these fields before submitting"
End If
End Sub

[beacon]

Hi Susan,

I think I speak for everyone that participates in this forum when I say that we would be happy to help you out. However, and this is a biggie, you need to post your question in an original thread. Posting a question in another member's thread is generally frowned upon, if not completely against the site's policy.

Please create a thread and include a hyperlink to this thread if you want to reference materials in it.

I will be on the lookout for your post and will help to the best of my ability as soon as possible.

Thanks,
beacon