Show blank entries on close

[oxicottin]

Hello, I have a frmMain that requires txtEmployeeTime, txtMinutes, txtDTRegular and txtFootage to have an entry in it. The way we enter info into each record is like this. We have 7 machines that run on a shift so for each shift there are 7 records (One for each machine) The Supervisor who enters the info always enters the basic info for each of the seven records and through out the day they continue to enter the remaining info as it happens for each machine. What happens sometimes is they will forget to fill out a control on one of the 7 records and it messes things up. Is there a way to give an MsgBox when the form is closed telling me which boxes went filled out on what record? Thanks!

 

[CyberLynx]

There is a simple FOR/NEXT loop for that sort of thing. You will want to Place the code into the Form's Unload event. It should look something like this:

Private Sub Form_Unload(Cancel As Integer)
   [COLOR="DarkGreen"]'Declare a Variable as Control[/COLOR]
   Dim Ctrl As Control

   [COLOR="DarkGreen"]'Enumerate through all the Form's Controls[/COLOR]
   For Each Ctrl In Me.Controls
      [COLOR="DarkGreen"]'Is the Control a TextBox or a ComboBox? If yes then...[/COLOR]
      If (Ctrl.ControlType = acTextBox) Or (Ctrl.ControlType = acComboBox) Then
         [COLOR="DarkGreen"]'Is the Control Null ordoes it contain a Blank String? If yes then...[/COLOR]
         If IsNull(Ctrl) = True Or Len(Ctrl) = 0 Then
            [COLOR="DarkGreen"]'Inform the User that not all required data was entered.[/COLOR]
            MsgBox "You did not fill in all the required data. Please do so now." & vbCr & _
                   "The field which requires data will contain focus.", vbExclamation, "Incomplete Data Entry"
            [COLOR="DarkGreen"]'Cancel the Close Form process[/COLOR]
            Cancel = True
            [COLOR="DarkGreen"]'Set Focus on the Control that requires Data.[/COLOR]
            Ctrl.SetFocus
            [COLOR="DarkGreen"]'Exit this loop and basically get outta here.[/COLOR]
            Exit For
         End If
      End If
   Next Ctrl
End Sub

.

 

[oxicottin]

CyberLynx, I just wanted it to show a message that there is missing data in what text boxes on what date. I just want to let the person closing the form that there is missing data and clike OK to close the msg box. I dont want to restrict the user from closing the form for certain reasons. the txtStartDate holds the date of the record and the fields it needs to check are txtEmployeeTime, txtMinutes, txtDTRegular and txtFootage. Is this possible?

Thanks,
Chad

 

[CyberLynx]

Basically...you just need to remove the Cancel = True and add a little didy in the MsgBox to inform which control. I've changed the code to list any or all Controls that do not have data entered then the Form just closes. An Informative tool only.

Private Sub Form_Unload(Cancel As Integer)
   [COLOR="DarkGreen"]'Declare a Variable as Control[/COLOR]
   Dim Ctrl As Control
   [COLOR="DarkGreen"]'Declare a String variable to hold the Names of Controls[/COLOR]
   Dim Strg As String

  [COLOR="DarkGreen"] 'Enumerate through all the Form's Controls[/COLOR]
   For Each Ctrl In Me.Controls
     [COLOR="DarkGreen"] 'Is the Control a TextBox or a ComboBox? If yes then..[/COLOR].
      If (Ctrl.ControlType = acTextBox) Or (Ctrl.ControlType = acComboBox) Then
         [COLOR="DarkGreen"]'Is the Control Null ordoes it contain a Blank String? If yes then...[/COLOR]
         If IsNull(Ctrl) = True Or Len(Ctrl) = 0 Then
            [COLOR="DarkGreen"]'Fill the Strg string Variable with the Controls that have bee found with no Data.[/COLOR]
            Strg = Strg & Ctrl.Name & vbCr
         End If
      End If
   Next Ctrl

   MsgBox "You did not fill required data within the following Fields:" & vbcr & _
           vbCr & Strg, vbExclamation, "Incomplete Data Entry"
End Sub

.

 

[oxicottin]

CyberLynx, this is exactly what I was looking for! The only thing is its naming the name of the control and whomever looks at this will never know what txtSupervisor or cbo? would be. Is there a way I could use names for each control on my form that would show in the MsgBox? Thanks!

 

[oxicottin]

CyberLynx, Messed with this all day and came up with a working solution
Thanks again for your well explained answers...

Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
strMsg = "The following fields are missing information!"

If Nz(Me.txtTotalFootage) = "" Then
strMsg = strMsg & vbCr & "    -Gross Footage"
Cancel = False

End If
If Nz(Me.txtMinutes) = 0 Then
strMsg = strMsg & vbCr & "    -Run Minutes"
Cancel = False

End If
If Nz(Me.txtDTRegular) = 0 Then
strMsg = strMsg & vbCr & "    -Delay"
Cancel = False
End If

If Len(strMsg) = 0 Then
      Cancel = False
    Else
        MsgBox strMsg, vbExclamation, "Missing Imformation"
        Cancel = False
    End If
End Sub

 

[CyberLynx]

Very Good...I'm glad you found a solution.

For your particular situation, with only a few controls, your solution is fine although the Code can be reduced somewhat using the principle (see below). Consider this now, what if you build a Form with a mix of say 80 TextBoxes and ComboBoxes and you want to check for Data Entry. That would be a LOT of IF/THEN statements which is why most programmers will loop through the Controls on Form.

It's all a matter of how you name your Controls, for example:

If the Control txtTotalFootage was instead named Gross_Footage and txtMinutes was actually named Run_Minutes then in the For/Next sample I had provided earlier, you could have the line of code that fills the Strg variable changed to this:

Strg = Strg & Replace(Ctrl.Name, "_", " ") & vbCr

This would now display the name desired.

Keep in mind....using txt in front of TextBox Control names is good naming practice and you should continue to do as such when developing your Database. If you always use three characters describing the Control within your Control names such as txtGross_Footage then the above line would need to change to this:

Strg = Strg & Replace(Mid$(Ctrl.Name,4), "_", " ") & vbCr

This would display Gross Footage.

You can reduce the size of your code by removing all those Cancel = False statements. By Default, Cancel is always False anyway. If you want to cancel the Form from closing then you would need to set it to True. For example:

Private Sub Form_Unload(Cancel As Integer)
   Dim strMsg As String
   strMsg = "The following fields are missing information!" 

   If Nz(Me.txtTotalFootage, "") = "" Then strMsg = strMsg & vbCr & "    -Gross Footage"
   If Nz(Me.txtMinutes, 0) = 0 Then strMsg = strMsg & vbCr & "    -Run Minutes"
   If Nz(Me.txtDTRegular, 0) = 0 Then strMsg = strMsg & vbCr & "    -Delay"

   If Len(strMsg) > 0 Then
        MsgBox strMsg, vbExclamation, "Missing Imformation"
   End If
End Sub

.

 

[oxicottin]

CyberLynx, I’m going to use your code because I have way more controls than the 3 I listed but neither yours nor mine would have worked because they only checked the last record, I needed it to check all records for the entire day if possible? Also there are 8 controls I do not want checked because most of the time they are not used.

txtDescription_Of_DTMaintenance, cboDT_Maintenance_Reason, cboDelay_Machine, txtDT_Maintenance, txtDT_Reason2, cboDT_Reason02, txtDT_Reason1, cboDT_Reason01

I inserted your code and changed all my controls names to txtName_Name or cboName_Name and used the code line you suggested

Strg = Strg & Replace(Mid$(Ctrl.Name,4), "_", " ") & vbCr

And im getting an Run-Time error 2447
There are invalid use of the .(dot) or the ! operator or invalid parentheses

I then Debug and it takes me to this line of code.
If IsNull(Ctrl) = True Or Len(Ctrl) = 0 Then

The only thing I did was added the _ and changed the one line of code above.

Thanks,
Chad

 

[oxicottin]

CyberLynx, I was just thinking. since I need it to check for controls with either a 0 or null for an entire day then should we be pulling info straight from the forms control source "tblMain"?

 

[sgtSortor]

Thank You

I read this thread and was thrilled with this piece of code. THANK YOU!

Now though, I can't seem to get it to work with an Option Group. I've changed Combo Box to (Ctrl.ControlType = OptionGroup), but it still ignores the two option groups I have on this form..........

This is how I changed the Code:

Private Sub TotalRequested_Exit(Cancel As Integer)
'Declare a Variable as Control
Dim Ctrl As Control

'Enumerate through all the Form's Controls
For Each Ctrl In Me.Controls
'Is the Control a TextBox or a ComboBox? If yes then...
If (Ctrl.ControlType = acOptionGroup) Then
'Is the Control Null ordoes it contain a Blank String? If yes then...
If IsNull(Ctrl) = True Or Len(Ctrl) = 0 Then
'Inform the User that not all required data was entered.
MsgBox "You did not fill in all the required data. Please do so now." & vbCr & _
"The field which requires data will contain focus.", vbExclamation, "Incomplete Data Entry"
'Cancel the Close Form process
Cancel = True
'Set Focus on the Control that requires Data.
Ctrl.SetFocus
'Exit this loop and basically get outta here.
Exit For
End If
End If
Next Ctrl
End Sub


Thanks for any help.

 

[CyberLynx]

Private Sub Form_BeforeUpdate(Cancel As Integer)
   [COLOR="DarkGreen"]'Declare a Variable as Control[/COLOR]
   Dim Ctrl As Control

   [COLOR="DarkGreen"]'Enumerate through all the Form's Controls Collection[/COLOR]
   For Each Ctrl In Me.Controls
      [COLOR="DarkGreen"]'Is the Control a OptionGroup? If yes then...[/COLOR]
      If (Ctrl.ControlType = acOptionGroup) Then
         [COLOR="DarkGreen"]'Is the Control Null or does it contain a value of 0? If yes then...[/COLOR]
         If IsNull(Ctrl.Value) = True Or Ctrl.Value = 0 Then
            [COLOR="DarkGreen"]'Inform the User that a selection must be made[/COLOR]
            MsgBox "You must select at least one Option!.", vbExclamation, _
                    "Option Not Selected"
            [COLOR="DarkGreen"]'Cancel the Close Form or record save process process[/COLOR]
            Cancel = True
            [COLOR="DarkGreen"]'Exit this For/Next loop and basically get outta here.[/COLOR]
            Exit For
         End If
      End If
   Next Ctrl
End Sub

You really shouldn't need to check the OptionGroup for NULL unless you never supplied a default Option in which case Radio Buttons and Check Boxes would be gray (looks terrible). If there is to be no default selection then set 0 into the Default property of the Frame Control, that way the option controls will be blank white and you only have to test for a value of 0 within the code. That way then the IsNull(Ctrl.Value) = True portion of code can be ommitted.

If you want to be specific with the two OptionGroups you have only then this code does the same as above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If myFrameCtrlName1.Value = 0 Or myFrameCtrlName2.Value = 0 Then
      [COLOR="DarkGreen"]'Inform the User that a selection was not made in one of the
      'Option Groups.[/COLOR]
      MsgBox "You must select at least one Option from all Option groups on Form!.", vbExclamation, _
             "Option Not Selected"
      [COLOR="DarkGreen"]'Cancel the Close Form or record save process[/COLOR]
      Cancel = True
      [COLOR="DarkGreen"]'Set Focus on the Control that requires Data.[/COLOR]
      Ctrl.SetFocus
   End If
End Sub

.

 

[sgtSortor]

Thanks very much. I decided to only show the second option group after the first option group was selected. That way, I eliminate any need validate those two fields..... Works excellently this way.