Solved Form behavior for null/empty controls (1 Viewer)

[Romio_1968]

I have a form that doesn't work as it should.

First problem>
On form load, i set the value for all controls to null.
Just for testing, after loading the null values, i added the code to colour the border for the null controls.
Only one is getting red.

Second problem
I set a variable to supress the form to save the record as typing, and alow saving only on clicking on the Save button.
Before saving the record, a test for null values in the mandatory fields is performed.
If there are required fields that are not filled, the record is not saved and the borders of unfilled controls should become red.
If the controls are properly filled, the record is saved in the table, and a NewRecord is called.
Here comes the weird behaviour.
Letting those controls empty, by clicking the save button, only one control becomes red. The record is not saved.

For sure I am doing something wrong...

The code is the following and the database is attached

Option Compare Database

Public CloseOk As Boolean

Private Sub Form_Load()
CloseOk = False
Me.Call_No = Null
Me.Title_Box = Null
Me.Publisher_Box = Null
Me.PublishYear_Box = Null
Me.DomainCombo_1 = Null
Me.ClassCode_Box1 = Null
Me.DomainCombo_2 = Null
Me.ClassCode_Box2 = Null
Me.DomainCombo_3 = Null
Me.ClassCode_Box3 = Null

'the next 4 ifs are only to check if the null value is loaded to the controls
'only ClassCode_Box1 gets red

If IsNull(Me!Title_Box) Then
Me!Title_Box.BorderColor = RGB(186, 20, 25)
Else
Me!Title_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me!Publisher_Box) Then
Me!Publisher_Box.BorderColor = RGB(186, 20, 25)
Else
Me!Publisher_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me!Publisher_Box) Then
Me!PublishYear_Box.BorderColor = RGB(186, 20, 25)
Else
Me!PublishYear_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me.ClassCode_Box1) Then
Me!ClassCode_Box1.BorderColor = RGB(186, 20, 25)
Else
Me!ClassCode_Box1.BorderColor = RGB(192, 192, 192)
End If

End Sub

Private Sub DomainCombo_1_Change()
Me.ClassCode_Box1 = Me.DomainCombo_1.Column(1)
Me.Call_No = Me.DomainCombo_1.Column(1)
End Sub

Private Sub DomainCombo_2_Change()
Me.ClassCode_Box2 = Me.DomainCombo_2.Column(1)
End Sub

Private Sub DomainCombo_3_Change()
Me.ClassCode_Box3 = Me.DomainCombo_3.Column(1)
End Sub

Private Sub DomainCombo_1_NotInList(NewData As String, Response As Integer)
MsgBox "Selectati domeniul din lista!", vbExclamation
Me.DomainCombo_1.Undo
Me.ClassCode_Box1.Undo
Me.Call_No.Undo
Response = acDataErrContinue
End Sub

Private Sub DomainCombo_2_NotInList(NewData As String, Response As Integer)
MsgBox "Selectati domeniul din lista!", vbExclamation
Me.DomainCombo_2.Undo
Response = acDataErrContinue
End Sub

Private Sub DomainCombo_3_NotInList(NewData As String, Response As Integer)
MsgBox "Selectati domeniul din lista!", vbExclamation
Me.DomainCombo_3.Undo
Response = acDataErrContinue
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not CloseOk Then
Me.Undo
Cancel = True
End If
End Sub

Private Sub SaveTitle_Click()

'Set Red borders to unfilled required fields

If IsNull(Me!Title_Box) Then
Me!Title_Box.BorderColor = RGB(186, 20, 25)
Else
Me!Title_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me!Publisher_Box) Then
Me!Publisher_Box.BorderColor = RGB(186, 20, 25)
Else
Me!Publisher_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me!Publisher_Box) Then
Me!PublishYear_Box.BorderColor = RGB(186, 20, 25)
Else
Me!PublishYear_Box.BorderColor = RGB(192, 192, 192)
End If

If IsNull(Me.ClassCode_Box1) Then
Me!ClassCode_Box1.BorderColor = RGB(186, 20, 25)
Else
Me!ClassCode_Box1.BorderColor = RGB(192, 192, 192)
End If

If (IsNull(Me!Call_No) Or IsNull(Me!Title_Box) Or IsNull(Me!Publisher_Box) Or IsNull(Me!PublishYear_Box) Or IsNull(Me!ClassCode_Box1)) Then
MsgBox "Nu ati completat campurile obligatorii!"
CloseOk = False
GoTo OutPoint
End If

CloseOk = True
DoCmd.RunCommand acCmdSaveRecord

Me.Call_No = Null
Me.Title_Box = Null
Me.Publisher_Box = Null
Me.PublishYear_Box = Null
Me.DomainCombo_1 = Null
Me.ClassCode_Box1 = Null
Me.DomainCombo_2 = Null
Me.ClassCode_Box2 = Null
Me.DomainCombo_3 = Null
Me.ClassCode_Box3 = Null

DoCmd.RunCommand (acCmdRecordsGoToNew)

OutPoint:
End Sub

 

[Gasman]

Please start putting code within code tags.
That keeps indentation, which I hope you are using.

 

[Gasman]

Might want to check your third test for null?, copy and paste errors?

 

[Romio_1968]

Gasman, i checked the third test and now it works. (copy/paste, indeed). Thank you.
Yet, the first two stil don't geet red.

 

[Gasman]

Make sure you have Option Explicit in every module.
Do you compile your code?

Why are mixing . and ! In control references?
I only ever used ! when I had to.

 

[Pat Hartman]

On form load, i set the value for all controls to null.

Not necessary. Also dangerous if any records were retrieved.

I set a variable to supress the form to save the record as typing, and alow saving only on clicking on the Save button.

That is not the way forms work. Data is not saved as it is typed. Data is saved when YOU ask to save it or due to some action YOU take, Access decides the record needs to be saved. The ONLY reliable way to stop a form from saving a record is to use code in the Form's BeforeUpdate event. You can ensure that the user presses your "save" button but that takes code in at least three different form level events plus code in the click event of the button.

Before saving the record, a test for null values in the mandatory fields is performed.

Null is not the same as a ZLS (Zero Length String) or spaces. Testing only for null will not trap ZLS. Access seems to ignore spaces and store a ZLS if there is no character in the control except for spaces.
Plus, there are simpler ways to check all the controls for Nul/ZLS than doing it control by control

Most of your code is unnecessary or is in the wrong event.
Your form opens dirty which is reallllllly bad. You don't want YOUR code to dirty a form. You want the user to type something and that should dirty the form.

I'm going to fix the form so give me some time.

 

[Pat Hartman]

I removed most of your code and added some of my own.
I changed the data type of class code to long so I could bind the combo to it.
I changed the form's RecordSource to a query that joins to UDC_Class_Code so it will display both fields. I locked the lookup field because it cannot be changed on this form.
I changed the required and default properties for all columns of titles and I set the AllowZLS property to No.
The code behind the form is now:

Option Compare Database
Option Explicit

Public CloseOk As Boolean

Private Sub Form_Load()
  CloseOk = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Answer As Long
    If Not CloseOk Then
      Answer = MsgBox("Do you want to save the changes?", vbYesNoCancel)
      If Answer = vbYes Then
          CloseOk = True
      Else
          Cancel = True
          If Answer = vbCancel Then
              Exit Sub
          Else
              Me.Undo
          End If
          Exit Sub
      End If
    End If
 
    If EnsureNotEmpty(Me) = False Then
      Cancel = True
      Exit Sub
    End If
 
End Sub

Private Sub SaveTitle_Click()

On Error GoTo ErrSub
    CloseOk = True
    DoCmd.RunCommand acCmdSaveRecord
      
    CloseOk = False
    DoCmd.RunCommand (acCmdRecordsGoToNew)
    
ExitSub:
    Exit Sub
ErrSub:
    Select Case Err.Number
        Case 3021   ''' no current record -- ignore
        Case Else
            MsgBox Err.Number & " -- " & Err.Description
            Resume ExitSub
    End Select

End Sub

I added common code to handle checking all form controls for null/zls. It doesn't highlight all errors. Users rarely make huge numbers of errors so the code stops at the first error. When you save again, if there are more/different errors, the next error is displayed. I also used my technique of marking each control you want to verify this way since not all controls will be required and need no other validaton.

Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
    
    EnsureNotEmpty = True
End Function

 

[Romio_1968]

Too compact and yet seems to have some issues...

1. The color does not change to the unfilled control
2. There are controls that can be saved with empty value, so I cannot use a global function. (Call_No, ClassCode2 and 3)
3. The control labeled Cota, is dead
4. How is triggered the MsgBox ("Do you want to save the changes?", vbYesNoCancel). I cannot replicate that.
5. More fields will be added, so the Global function Issue will affect them too.

Thank you

 

[Romio_1968]

6. In the error message regardin the unfilled control, I cannot refer to the control name, but to the control label

 

[arnelgp]

you can check and test this also.

 

[KitaYama]

you can check and test this also.

@arnelgp Can you explain why the original method didn't work?
I wonder why OP assigned null to all controls, but IsNull returned true for only one control, and false for others.

Thank you.

 

[arnelgp]

method didn't work?

i did not test it, i just take his Words that it does not work.
then made the code much simpler.

 

[Pat Hartman]

2. There are controls that can be saved with empty value, so I cannot use a global function. (Call_No, ClassCode2 and 3)

You didn't look at how the common code works. This is very typical. Some fields are required, but not all. THEREFORE, you add an R in the Tag field of any control where the data is required. The common code only processes the controls with the R in the tag property. This keeps you from having to change the code.

1. The color does not change to the unfilled control

I explained specifically why I did not highlight the fields if you read my explanation. If you want the control to be highlighted, then add the highlight code to the common code. Pay attention to how it works. You are referring to the ctl object rather than the name of the individual control so the form can have a hundred controls or two. the common code loops through all of them checking for that R in the Tag property.

3. The control labeled Cota, is dead

It is dead in your version since it is unbound.

4. How is triggered the MsgBox ("Do you want to save the changes?", vbYesNoCancel). I cannot replicate that.

You do NOT want to display this message UNLESS the user doesn't use your button to save the record. It is very poor practice to ask the user if he wants to save every time he saves a record ESPECIALLY if you give him a SAVE button. Do your users press the SAVE button when they don't want to save????

5. More fields will be added, so the Global function Issue will affect them too.

As I said, the global function affects ONLY controls where the Tag property is R indicating that the field is required. If you want the control to be processed by the global function, set the Tag property to R.

6. In the error message regardin the unfilled control, I cannot refer to the control name, but to the control label

That is a different problem. It is doable but it requires great discipline on your part. It requires formatting the name of the control consistently so it is yourcontrolname_lbl. Then the code in the global function can be modified to use the label control's caption property rather than the edited control's name.