How stop subform adding blank values?

[Dorian]

I have a main form and several subforms.
All of the subforms are allowing blank values to be saved despite my
multiple attempts at validation inside the subform BeforeUpdate events.
How do I prevent a sunform saving a blank value? These are mostly combo box
selections. E.g. if the user makes a combo box selection and then spaces it
out, blanks are saved in the value.
I want to trap these things myself, I do not want Access to detect them and
display its own message.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[OldKingCole]

I have a main form and several subforms.
All of the subforms are allowing blank values to be saved despite my
multiple attempts at validation inside the subform BeforeUpdate events.
How do I prevent a sunform saving a blank value? These are mostly combo box
selections. E.g. if the user makes a combo box selection and then spaces it
out, blanks are saved in the value.
I want to trap these things myself, I do not want Access to detect them and
display its own message.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

For ComboBoxes use the following events to trap rubbish or invalid
entries;


Private Sub cbMyComboBoxp_NotInList(NewData As String, Response As
Integer)
' Trap entries that are Not in the underlying support table....

MsgBox "You must select an entry from the List", _
vbExclamation, cnsMyAppTitle & " - List Option Only"
Response = acDataErrContinue
UndoCtl Me

End Sub

Private Sub cbMyComboBox_BeforeUpdate(Cancel As Integer)

If Not (SomethingIn(Me.cbMyComboBox)) Then
MsgBox "The 'My Combo box Name' cannot be blank." & vbCrLf &
vbCrLf & _
"Please make a valid entry.", vbExclamation, _
cnsMyAppTitle & " - Missing Data"
UndoCtl Me
Cancel = True
End If

You'll see that the code above requires the following public
declarations Put these in a Module;

cnsMyAppTitle = "My Super App"

Public Function SomethingIn(ctlMyControl As Control) As Boolean
' Returns True if the specified control contains a value.

SomethingIn = True
If IsNull(ctlMyControl) Then
SomethingIn = False
Else
If Trim(ctlMyControl.Value) = "" Then
SomethingIn = False
End If
End If

End Function

 

[John W. Vinson]

I have a main form and several subforms.
All of the subforms are allowing blank values to be saved despite my
multiple attempts at validation inside the subform BeforeUpdate events.
How do I prevent a sunform saving a blank value? These are mostly combo box
selections. E.g. if the user makes a combo box selection and then spaces it
out, blanks are saved in the value.
I want to trap these things myself, I do not want Access to detect them and
display its own message.

Correct the error in your BeforeUpdate events.

You should be using the Form BeforeUpdate event, not that of the individual
combo boxes; the combo's BeforeUpdate will never fire at all if the user
ignores it. You could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strWarning As String
Dim iAns As Integer
strWarning = ""
If IsNull(Me!cboA) Then
strWarning = strWarning & "Value A, "
End If
If IsNull(Me!cboB) Then
strWarning = strWarning & "Field B, "
End If
If IsNull(Me!cboC) Then
strWarning = strWarning & "Answer C, "
End If
If Len(strWarning) > 0 Then ' user missed one or more controls
strWarning = Left(strWarning, Len(strWarning) - 2) ' trim off trailing comma
strWarning = strWarning & " must be filled in before you can save." _
& vbCrLf & "Click OK to correct the error, Cancel to abandon this entry."
iAns = MsgBox(strWarning, vbOKCancel)
Cancel = True ' cancel the update in any case
If iAns = vbCancel Then
Me.Undo ' erase the form if the user chooses Cancel
End If
End If
End Sub

 

[OldKingCole]

Correct the error in your BeforeUpdate events.

You should be using the Form BeforeUpdate event, not that of the individual
combo boxes; the combo's BeforeUpdate will never fire at all if the user
ignores it. You could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strWarning As String
Dim iAns As Integer
strWarning = ""
If IsNull(Me!cboA) Then
   strWarning = strWarning & "Value A, "
End If
If IsNull(Me!cboB) Then
   strWarning = strWarning & "Field B, "
End If
If IsNull(Me!cboC) Then
   strWarning = strWarning & "Answer C, "
End If
If Len(strWarning) > 0 Then ' user missed one or more controls
  strWarning = Left(strWarning, Len(strWarning) - 2) ' trim off trailing comma
  strWarning = strWarning & " must be filled in before you can save."_
    & vbCrLf  & "Click OK to correct the error, Cancel to abandon this entry."
  iAns = MsgBox(strWarning, vbOKCancel)
  Cancel = True ' cancel the update in any case
  If iAns = vbCancel Then
    Me.Undo ' erase the form if the user chooses Cancel
  End If
End If
End Sub

Good point John, I missed adding that one