Difficulty Using Case Statement Instead of If/Then

[RunTime91]

Can't believe I'm here for this but after an exhaustive research on how to do this - I wonder if it can even be done??

Pretty straight forward I have 22 If/Then statements designed to create a 'List' of mandatory fields not populated on a form.

Below is a snipet of those 22 If/Then Statements

Dim Str1 As String
  
  With Me


    If IsNull(.TxtRecipient) Or IsEmpty(.TxtRecipient) Then
      Str1 = "ABDC" & vbCrLf & ""
    End If


    If IsNull(.TxtFaxNum) Or IsEmpty(.TxtFaxNum) Then
      Str1 = Str1 & "Fax" & vbCrLf & ""
    End If


    If IsNull(.TxtRqstDate) Or IsEmpty(.TxtRqstDate) Then
      Str1 = Str1 & "Date/Time" & vbCrLf & ""
    End If


    If IsNull(.txtDMIS) Or IsEmpty(.txtDMIS) Then
      Str1 = Str1 & "EXPR" & vbCrLf & ""
    End If


    If IsNull(.TxtFacilityName) Or IsEmpty(.TxtFacilityName) Then
      Str1 = Str1 & "Facility" & vbCrLf & ""
    End If


    If IsNull(.TxtRqstProvName) Or IsEmpty(.TxtRqstProvName) Then
      Str1 = Str1 & "Name" & vbCrLf & ""
    End If


    If IsNull(.TxtRqstProvNPI) Or IsEmpty(.TxtRqstProvNPI) Then
      Str1 = Str1 & "TMI" & vbCrLf & ""
    End If

End With

If Str1 <> "" Then
  MsgBox "The Following Fields are Missing Critical Information" & vbCrLf & " " & vbCrLf & "" & ("" & Str1 & "")
  Cancel = True
End If

The code works perfectly in that it provides the user a concise list of the fields missing data, but I know this method, besides being not at all sexy - is probably just about the most inefficient way to do this as well.

I would like to use Case Statements - But can I? And if not, is there another 'better' way to acomplish this



Thanks everyone...

[ranman256]

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(cboWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "User name is missing"
   Case IsNull(txtEmail)
      vMsg = "Email field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

[pbaldy]

I'm not sure Select/Case is appropriate, as it will drop out after the first successful test, and you want the list of all failed tests. I'd use a loop of controls with the Tag property:

Dim ctl As Control  
For Each ctl In Me.Controls       
  If ctl.Tag = "CheckMe" Then      
    If Len(ctl.Value & vbNullString) = 0 Then
      Str1 = Str1 & ctl.Name & vbCrLf & ""
    End If
  End If   
Next ctl

[RunTime91]

RanMan - Thank you for stepping in - You're solution would have worked perfectly - If I hadn't overlooked what Paul brought up - (facepalm)

Paul - Thank You so much as always, I modified your idea just a little to work perfectly!

Because all of my controls begin with Txt.This or Cmb.That I simply put the Control Label Caption in the Tag property then ran your code as such:

Dim Str1 As String
Dim ctl As Control


For Each ctl In Me.Controls
  If ctl.Tag <> "" Then
    If Len(ctl.Value & vbNullString) = 0 Then
      Str1 = Str1 & ctl.Tag & vbCrLf & ""
    End If
  End If
Next ctl
  
If Str1 <> "" Then
  MsgBox "The Following Fields are Missing Critical Information" & vbCrLf & " " & vbCrLf & "" & ("" & Str1 & "")
  Cancel = True
End If

Thank You, Paul ~ Thank You, RanMan

[Micron]

I agree with the loop and tag. However, if you're naming your controls properly (OK, that might be a matter of opinion) a message like "Please provide data for txtPropNum" won't do for some users. I prefer to get the attached/associated label caption as it's easy to reference and most often is easy for the user to relate to, but not if it's unattached.
as in
strMsg = strMsg & ctl.Controls(0).Caption & vbcrlf
because controls like textboxes have a controls collection if the label is attached, albeit only one member.
FWIW, I once had my loop error out because one control didn't have a .Tag property, yet I've never been able to find one that didn't after Access 2003 so maybe that will never be an issue going forward.

EDIT - saw response after posting. Fairly certain your code prevents you from using the Tag property on any other control on the form for any other purpose.

[pbaldy]

Paul - Thank You so much as always, I modified your idea just a little to work perfectly!

Happy to help! It was intended to be a starting point, not an end solution. Glad you got it working.