Solved Form behavior for null/empty controls

[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.

 

[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.