Check if multiple text boxes are empty (1 Viewer)

[Atthe]

Good morning,

I am trying to create a code that checks on click if a textbox is empty and if it is it assigns a back colour and makes a textbox(* symbol) visible and then checks if the next textbox and performs the same routine until all have been checked. the end goal being if any are empty the user can clearly see this by means of the back colour change and a * symbol adjacent to the textbox. If all textboxes contain data the record is saved

I realise the way I have attempted to code this is wrong and would appreciate any help as I am stuck at 'End if required'

Thanks

If Me.Location.Value = "" Then
    
    Me.Location.BackColor = RGB(255, 113, 113)
    Me.astloc.Visible = True
    
    End If
    
    If Me.cmbMachine.Value = "" Then
    
    Me.cmbMachine.BackColor = RGB(255, 113, 113)
    Me.astmachine.Visible = True
    
    
    End If
    
    If Me.cmbToolType.Value = "" Then
    
    Me.cmbToolType.BackColor = RGB(255, 113, 113)
    Me.asttool.Visible = True
    
    End If
    
    If Me.PartNumber.Value = "" Then
    
    Me.PartNumber.BackColor = RGB(255, 113, 113)
    Me.astpart.Visible = True
    
    End If
    
    If Me.DrawingNumber.Value = "" Then
    
    Me.DrawingNumber.BackColor = RGB(255, 113, 113)
    Me.astdraw.Visible = True
    
    End If
    
    If Me.CALNumber.Value = "" Then
    
    Me.CALNumber.BackColor = RGB(255, 113, 113)
    Me.astCAL.Visible = True
    
    End If
    
    If Me.cmbToolCondition.Value = "" Then
    
    Me.cmbToolCondition.BackColor = RGB(255, 113, 113)
    Me.asttoolcon.Visible = True
    
    End If
    
    If Me.cmbLine.Value = "" Then
    
    Me.cmbLine.BackColor = RGB(255, 113, 113)
    Me.astline.Visible = True
    
    End If
    
    MsgBox "*These fields cannot be blank!"
    
    Else
    
    DoCmd.RunCommand acCmdSaveRecord

    MsgBox "Tooling added/edited"

 

[Gasman]

Most people use the tag property of the controls required to be completed.
Search here, it is asked often, see also similar threads.
Indenting your code would also help a lot?

 

[Minty]

The neat way to achieve this is to loop through all the controls and build a message that something is missing.
If you set the datasheet caption you can use that to populate the description with something more user friendly than the field name.

You'll need to adapt this code to suit but it returns pops up a message box and returns False if the controls aren't validated.

You'll need to add the tag value of Req to each control you want to check.
This routine also checks if a control is enabled - as on the form it's from disables and enables various required controls based on other business rules. We don't want to check disabled controls as they can't have data entered.

Function ValidateRecs() As Boolean
   
    Dim sControls As String
    Dim ctl As Control
    Dim ctlColour As Long

    For Each ctl In Me.Controls
        If ctl.Tag = "Req" Then     'Reset the colours
            If ctl.BackColor = vbYellow Then
                ctlColour = vbWhite   ' GetHexColor("#C6D9F1") ' light blue
                ctl.BackColor = ctlColour
            End If
            If ctl.Enabled Then
                'Debug.Print ctl.Name, ctl.value, ctl.Properties("DataSheetCaption")
                If IsNull(ctl.value) Then
                    sControls = sControls & ctl.Properties("DataSheetCaption") & vbCrLf
                    ctl.BackColor = vbYellow
                End If
            End If
        End If
    Next ctl
   
    ValidateRecs = True
 
    If Len(sControls & "") > 0 Then
        MsgBox "The following fields require data before the completion date can be entered;" & vbCrLf & vbCrLf & sControls, vbInformation + vbOKOnly, "Missing Data!"
        ValidateRecs = False

    End If
   
End Function

 

[Atthe]

The neat way to achieve this is to loop through all the controls and build a message that something is missing.
If you set the datasheet caption you can use that to populate the description with something more user friendly than the field name.

You'll need to adapt this code to suit but it returns pops up a message box and returns False if the controls aren't validated.

You'll need to add the tag value of Req to each control you want to check.
This routine also checks if a control is enabled - as on the form it's from disables and enables various required controls based on other business rules. We don't want to check disabled controls as they can't have data entered.

Function ValidateRecs() As Boolean
   
    Dim sControls As String
    Dim ctl As Control
    Dim ctlColour As Long

    For Each ctl In Me.Controls
        If ctl.Tag = "Req" Then     'Reset the colours
            If ctl.BackColor = vbYellow Then
                ctlColour = GetHexColor("#C6D9F1") ' light blue
                ctl.BackColor = ctlColour
            End If
            If ctl.Enabled Then
                'Debug.Print ctl.Name, ctl.value, ctl.Properties("DataSheetCaption")
                If IsNull(ctl.value) Then
                    sControls = sControls & ctl.Properties("DataSheetCaption") & vbCrLf
                    ctl.BackColor = vbYellow
                End If
            End If
        End If
    Next ctl
   
    ValidateRecs = True
 
    If Len(sControls & "") > 0 Then
        MsgBox "The following fields require data before the completion date can be entered;" & vbCrLf & vbCrLf & sControls, vbInformation + vbOKOnly, "Missing Data!"
        ValidateRecs = False

    End If
   
End Function

Thanks a lot for this, where should I put the code? In the On Click of the button?

 

[oleronesoftwares]

None of the answers has advised on which event the codes will be put under.

 

[Minty]

None of the answers has advised on which event the codes will be put under.

I'm not sure we were asked that in the initial question, and it's really dependent on how the form is presented to the end user.
Personally, I would have a "Save and Exit Button" and remove other ways of closing the form.

You call the function from the button click. If it returns true then close the form.

 

[arnelgp]

you call minty's code on the Form's BeforeUpdate event:

private sub form_beforeUpdate(cancel as integer)
cancel = ValidateRecs()
end sub

//note that the function ValidateRecs() should be inside the Form module, because it is using the
Me Object.

 

[arnelgp]

here is another demo.

 

[Atthe]

here is another demo.

Thanks that really helps I have implemented the module but now when I press my 'Save' button the missing fields checks are performed as expected but I then get error "2051 the run command action was cancelled" (DoCmd.RunCommand acCmdSaveRecord) what is the best way to incorporate the checks with the save record?

Thanks

 

[The_Doc_Man]

Something in your post causes me to offer a suggestion. First, what you did is NOT wrong. But I can save you some typing. Here is the suggestion:

If Me.cmbToolCondition.Value = "" Then

can be written as

If Me.cmbToolCondition = "" Then

because the .Value of any control (if it HAS a .Value property at all) is always the default property to return. Labels, for a counter-example, don't have values so don't have .Value properties. Combo boxes DO have a .Value if a selection has been made. Your test might be improved, though, by including the test as

If (Me.cmbToolCondition = "" ) or (Me.cmbToolCondition.ListIndex = -1) Then

This works because the .ListIndex of -1 means nothing has been selected yet. If you have no default value for the combo, you might expect to see the list index.

And finally, if you are not trying to make use of the .NotInList option (i.e. not allowing a "write-in" value that isn't in the list), then you don't even need to test the value, just test the .ListIndex property to see if anything has been selected.

 

[arnelgp]

here is what is happening on your form.
if you are changing a record (either new record or old record) and the pencil image
is showing on the record selector, it means that the Form is in Dirty state.
when you leave the form, in your case by clicking the save button, the BeforeUpdate event
of the form fire first (not your code on the button).
so it validate your controls first.
if all is OK it will then run the code in your button, which is save the record.
if not OK, meaning Cancel is set to True, it will go back to your form.

to prevent your error just add this line to your code on the save button:

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

 

[azhar2006]

here is another demo.

Thank you very much dear @arnelgp

 

[Atthe]

here is what is happening on your form.
if you are changing a record (either new record or old record) and the pencil image
is showing on the record selector, it means that the Form is in Dirty state.
when you leave the form, in your case by clicking the save button, the BeforeUpdate event
of the form fire first (not your code on the button).
so it validate your controls first.
if all is OK it will then run the code in your button, which is save the record.
if not OK, meaning Cancel is set to True, it will go back to your form.

to prevent your error just add this line to your code on the save button:

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

Good Morning,

I have added the code and it prevents the error but It also allows the rest of my code to run still, Where within the code is the correct place to put it as what I need to happen is on click of the 'Save' button to validate the controls and if any empty controls are found stop the code running further else run the rest of the code

Private Sub SaveTooling_Click()

    On Error Resume Next
   
    DoCmd.RunCommand acCmdSaveRecord

    MsgBox "Tooling added/edited"

    Me.New_Tooling.Visible = True
    Me.DeleteTooling.Visible = True
    Me.EditTooling.Visible = True
    Me.cmdSearch.Visible = True
 
    Me.focustxt.SetFocus

    Me.SaveTooling.Visible = False
   
    Me.SearchItem.Visible = True

    Me.Location.Enabled = False
    Me.cmbMachine.Enabled = False
    Me.cmbToolType.Enabled = False
    Me.PartNumber.Enabled = False
    Me.DrawingNumber.Enabled = False
    Me.CALNumber.Enabled = False
    Me.cmbToolCondition.Enabled = False
    Me.cmbLine.Enabled = False
    Me.SAPNumber.Enabled = False
    Me.Notes.Enabled = False

    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 17

End Sub

 

[arnelgp]

Private Sub SaveTooling_Click()

    On Error Resume Next
  
    DoCmd.RunCommand acCmdSaveRecord
    ' arnelgp
    ' just exit if there is error like
    ' in case there is Validation error
    If Err Then
        Exit Sub
    End If
    
    MsgBox "Tooling added/edited"

    Me.New_Tooling.Visible = True
    Me.DeleteTooling.Visible = True
    Me.EditTooling.Visible = True
    Me.cmdSearch.Visible = True
 
    Me.focustxt.SetFocus

    Me.SaveTooling.Visible = False
  
    Me.SearchItem.Visible = True

    Me.Location.Enabled = False
    Me.cmbMachine.Enabled = False
    Me.cmbToolType.Enabled = False
    Me.PartNumber.Enabled = False
    Me.DrawingNumber.Enabled = False
    Me.CALNumber.Enabled = False
    Me.cmbToolCondition.Enabled = False
    Me.cmbLine.Enabled = False
    Me.SAPNumber.Enabled = False
    Me.Notes.Enabled = False

    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 17

End Sub

 

[Uncle Gizmo]

Here is some very similar code, but in this case it checks the name of the text box and only works on appropriately named text boxes:-

Loop Through a Set of Controls - Nifty Access

Loop Through a Set of Controls A common requirement is to check a set of controls for a particular value or condition. Here, I demonstrate two methods. The technique employed is what I call the "For Each Control in Me Controls" - it's a technique where you interrogate each control "in/on" the...

www.niftyaccess.com

Particularly useful if you use the tag property for something else...

 

[CJ_London]

as an alternative method, you could use conditional formatting to format the back colour

or use the format property to show a value is required -

for text use @;[Red]"Req'd"
for numbers ;;;[Red]"Req'd"

neither require code

 

[Uncle Gizmo]

"Nifty Container"

Here is another method I developed which I'm quite pleased with!

You surround a set of Controls with a frame/rectangle/container, whatever you want to call it, and your code will treat all the controls within that rectangle in a particular manner.

Nifty Container - Nifty Access

Nifty Container The "Nifty Container" offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your...

www.niftyaccess.com

I haven't had the opportunity yet, but it would lend itself to to creating a sort of venn diagram on your form, with overlapping areas.

If anyone comes up with such a use of this Nifty Container, I'd be very interested in seeing your solution....

If you would like a free copy then contact me via private message on this forum.

 

[Atthe]

Private Sub SaveTooling_Click()

    On Error Resume Next
 
    DoCmd.RunCommand acCmdSaveRecord
    ' arnelgp
    ' just exit if there is error like
    ' in case there is Validation error
    If Err Then
        Exit Sub
    End If
   
    MsgBox "Tooling added/edited"

    Me.New_Tooling.Visible = True
    Me.DeleteTooling.Visible = True
    Me.EditTooling.Visible = True
    Me.cmdSearch.Visible = True

    Me.focustxt.SetFocus

    Me.SaveTooling.Visible = False
 
    Me.SearchItem.Visible = True

    Me.Location.Enabled = False
    Me.cmbMachine.Enabled = False
    Me.cmbToolType.Enabled = False
    Me.PartNumber.Enabled = False
    Me.DrawingNumber.Enabled = False
    Me.CALNumber.Enabled = False
    Me.cmbToolCondition.Enabled = False
    Me.cmbLine.Enabled = False
    Me.SAPNumber.Enabled = False
    Me.Notes.Enabled = False

    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 17

End Sub

This works great but is there anyway to return the datasheet caption in the message box rather than the just the control name?

Thanks

 

[Gasman]

None of the answers has advised on which event the codes will be put under.

I am trying to create a code that checks on click

Here is some very similar code, but in this case it checks the name of the text box and only works on appropriately named text boxes:-

Loop Through a Set of Controls - Nifty Access

Loop Through a Set of Controls A common requirement is to check a set of controls for a particular value or condition. Here, I demonstrate two methods. The technique employed is what I call the "For Each Control in Me Controls" - it's a technique where you interrogate each control "in/on" the...

www.niftyaccess.com

Particularly useful if you use the tag property for something else...

Tag property could hold several values and just use Instr() ?

 

[arnelgp]

This works great but is there anyway to return the datasheet caption in the message box rather than the just the control name?

i don't have your datasheet, so only you can modify the code.