By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Returning the names of fields if IsNull equals True

P: 579
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmit_Click()
  3. Dim subVal
  5. If IsNull(Combo1.Value) Or IsNull(Text2.Value) Or IsNull(OptionGroup3.Value) Then
  6.      MsgBox "One of the fields has a NULL value. This is not allowed", vbExclamation, "Null Value"
  7.      Exit Sub
  8. End If
  10. 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:


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.

Dec 1 '08 #1
Share this Question
Share on Google+
10 Replies

P: 675
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmit_Click()
  2. Dim strError
  3. strError = ""
  4. If IsNull(Combo1.Value) then strError = strError & vbNewLine & Combo1
  5. If IsNull(Text2.Value) then strError = strError & vbNewLine & Text2
  6. If IsNull(OptionGroup3) then strError = strError & vbNewLine & OptionGroup3
  7. If Len(strError) <> 0 then
  8.     'Errors encountered
  9.     strError = "The following fields are blank:" & vbNewLine & _
  10.                    strError & vbNewLine _
  11.                    "Please fill in these fields before submitting"
  12. End If
  13. 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"

Dec 1 '08 #2

P: 579
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
Dec 1 '08 #3

Expert 100+
P: 1,287
To loop through the form, you can do:

Expand|Select|Wrap|Line Numbers
  1. dim ctrl as Control
  3. For Each ctrl in Me.Controls
  4.     If ctrl.ControlType = acTextBox Then
  5.         ctrl.BackColor = 255
  6.         strControlList = strControlList & ctrl.Name & ", "
  7.     End If
  8. Next
Just as an example. You may have to Select Case on control type for what you want to do.
Dec 1 '08 #4

P: 675
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmit_Click()
  2. Dim cntl As Control
  3. Dim strMessage As String
  4. strMessage = ""
  5. For Each cntl In Me.Controls
  6. Select Case cntl.ControlType
  7. Case acTextBox
  8. If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
  9. strMessage = strMessage & vbNewLine & cntl.Name
  10. Case acComboBox
  11. If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
  12. strMessage = strMessage & vbNewLine & cntl.Name
  13. Case acOptionGroup
  14. If cntl.Tag = "Required" Then If IsNull(cntl.Value) Then _
  15. strMessage = strMessage & vbNewLine & cntl.Name
  16. End Select
  17. Next
  19. If strMessage <> "" Then _
  20. MsgBox "The following fields are blank:" & vbNewLine & _
  21. strMessage & vbNewLine & vbNewLine & _
  22. "Please fill in these fields before submitting"
  23. 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 . . ."

Dec 2 '08 #5

P: 579
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.
Dec 2 '08 #6

P: 675
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:


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.

Dec 3 '08 #7

P: 579

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!

Dec 3 '08 #8

P: 675
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.

Dec 3 '08 #9

P: 88
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 Sub

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
Sep 10 '10 #10

P: 579
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.

Sep 10 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.