select case within if statement

[vttech]

I declared a variable as control then looped through the controls in my form
and controls that had a null or "" value I tried to add to a string called strRequired
I am having an issue with the select statement not passing the name in the case statement to that value
What am I missing??

Private Sub CheckForNulls()
  Dim ctl As Control
  Dim strRequired As String
  strRequired = ""

  For Each ctl In Form.Controls
    If TypeOf ctl Is ComboBox Or TypeOf ctl Is TextBox _
    Or TypeOf ctl Is ListBox Then
       If IsNull(ctl) Or ctl = "" Then
            
	Select Case ctl.Name
            Case cboSite
            strRequired = strRequired + "Site" & ", "
            Case cboActivity
            strRequired = strRequired + "Activity" & ", "
            Case txtLogin
            strRequired = strRequired + "LogIn" & ", "
            Case txtLogOut
            strRequired = strRequired + "LogOut" & ", "
            Case cboWorkDate Or txtDay
            strRequired = strRequired + "Date" & ", "              
	End Select
                         
        End If
    End If
  Next
  
  If strRequired = "" Or IsNull(strRequired) Then
    InputLogInOut
  Else
    MsgBox strRequired & " is a required field"
    'Debug.Print strRequired
  End If
  
End Sub

Newbie in search of knowledge

 

[PHV]

Case [!]"[/!]cboSite[!]"[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Look carefully at the structure of your code.

For Each Ctl In Form.Controls
    If TypeOf Ctl Is ComboBox Or TypeOf Ctl Is TextBox _
       Or TypeOf Ctl Is ListBox Then
        If IsNull(Ctl) Or Ctl = "" Then
            [COLOR=black cyan]' You get to here only if[/color]
            [COLOR=black cyan]' Ctl is one of the three control types AND[/color]
            [COLOR=black cyan]' Ctl is NULL or equal to an empty string.[/color]
            [COLOR=black cyan]' I doubt that all such conditions will[/color]
            [COLOR=black cyan]' ever be true so the SELECT will never be executed.[/color]
            Select Case Ctl.Name
                Case cboSite
                    strRequired = strRequired + "Site" & ", "
                Case cboActivity
                    strRequired = strRequired + "Activity" & ", "
                Case txtLogin
                    strRequired = strRequired + "LogIn" & ", "
                Case txtLogOut
                    strRequired = strRequired + "LogOut" & ", "
                Case cboWorkDate Or txtDay
                    strRequired = strRequired + "Date" & ", "
            End Select

        End If
    End If
Next

 

[CompuSolve]

vttech - I use a function to checked required entry field in forms (below). It pops an error for any indicated elements that are NULLs or empty strings. You can change what fields are checked by changing the array definition - the actual checking of the fields is looped based and doesn't need to be modified

You can use this in the before update event - i.e.

   If checkRequired = False Then
      cancel = True
      Exit Sub
   End If

checkRequired function:

Public Function checkRequired() As Boolean

'this function will check to make sure all required fields have values before saving the record

Dim fldArr(20, 3) As String
Dim loopX As Integer

'array position 1 is the name of the object on the form
'array position 2 is the text to display to the user
'   when the field is left blank

Dim fldArr(20, 3) As String
Dim loopX As Integer

fldArr(1, 1) = "AField"
fldArr(1, 2) = "A Field"

fldArr(2, 1) = "TheField"
fldArr(2, 2) = "The Field"

fldArr(3, 1) = "AnotherField"
fldArr(3, 2) = "This Field"

fldArr(4, 1) = "cboSal"
fldArr(4, 2) = "Salutation"

fldArr(5, 1) = "CustFirstName"
fldArr(5, 2) = "First Name"

fldArr(6, 1) = "CustLastName"
fldArr(6, 2) = "Last Name"

fldArr(7, 1) = "ADDRESS"
fldArr(7, 2) = "Address"

fldArr(8, 1) = "CITY"
fldArr(8, 2) = "City"

fldArr(9, 1) = "cboState"
fldArr(9, 2) = "State"

fldArr(10, 1) = "ZIP"
fldArr(10, 2) = "Zip"

fldArr(11, 1) = "PHONENO"
fldArr(11, 2) = "Home Phone"

checkRequired = False

For loopX = 1 To 11
   If IsNull(Me(fldArr(loopX, 1))) Or Me(fldArr(loopX, 1)) = "" Then
      fMsgBox fldArr(loopX, 2) & " is a required field.@All required fields " & _
         "must be filled in before saving.@Please complete " & fldArr(loopX, 2) & _
         " before saving.", vbCritical, "Missing Required Field"
      Me(fldArr(loopX, 1)).SetFocus
      Exit Function
   End If
Next

checkRequired = True

End Function

'this is the formatted msgBox that is prettier than the current Access msgbox - duplicates functionality of Access97 MsgBox

Function fMsgBox( _
 Prompt As String, _
 Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
 Optional Title As String = vbNullString, _
 Optional HelpFile As Variant, _
 Optional Context As Variant) _
 As VbMsgBoxResult

    If IsMissing(HelpFile) Or IsMissing(Context) Then
        fMsgBox = Eval("msgbox(""" & Prompt & _
         """, " & Buttons & ", """ & Title & """)")
    Else
        fMsgBox = Eval("msgbox(""" & Prompt & _
          """, " & Buttons & ", """ & Title & """, """ & _
          HelpFile & """, " & Context & ")")
    End If
    
End Function

 

[Zion7]

As PHV stated...

a small revamp (hope you don't mind).

Dim ctl As Control
Dim strRequired As String
strRequired = ""

For Each ctl In Form.Controls
If TypeOf ctl Is ComboBox Or TypeOf ctl Is TextBox _
Or TypeOf ctl Is ListBox Then
If IsNull(ctl) Or ctl = "" Then

Select Case ctl.Name
Case "cboWorkDate", "txtDay"
strRequired = strRequired & "Date" & ", "
Case Else
strRequired = strRequired & Mid(ctl.Name, 4) & ", "
End Select

End If
End If
Next

If strRequired = "" Or IsNull(strRequired) Then
InputLogInOut
Else
MsgBox strRequired & " is a required field"
'Debug.Print strRequired
End If

 

[TheAceMan1]

How are ya vttech . . .

If you performed a compile on your code you'd find the error. Since your select case is looking for a name ([purple]string[/purple]), as you have it posted VBA is gonna think the names are variables!

[blue]   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Or _
         ctl.ControlType = acComboBox Or _
         ctl.ControlType = acListBox Then
         
         If Trim(ctl & "") = "" Then
            Select Case ctl.Name
               Case [red][b]"[/b][/red]cboSite[red][b]"[/b][/red]
               strRequired = strRequired + "Site" & ", "
               Case [red][b]"[/b][/red]cboActivity[red][b]"[/b][/red]
               strRequired = strRequired + "Activity" & ", "
               Case [red][b]"[/b][/red]txtLogin[red][b]"[/b][/red]
               strRequired = strRequired + "LogIn" & ", "
               Case [red][b]"[/b][/red]txtLogOut[red][b]"[/b][/red]
               strRequired = strRequired + "LogOut" & ", "
               Case [red][b]"[/b][/red]cboWorkDate[red][b]"[/b][/red] Or [red][b]"[/b][/red]txtDay[red][b]"[/b][/red]
               strRequired = strRequired + "Date" & ", "
            End Select
         End If
      End If
   Next[/blue]

See Ya! . . . . . .