I want to ensure that the DEPT field in the subform of the attached DB is not null. How to do it?
I want to ensure that the DEPT field in the subform of the attached DB is not null. How to do it?
One way is to set it as required in table and let Access nag users when they try to leave new record row after starting data entry. Another is to use textbox/combobox ValidationRule and ValidationText properties. Can use DefaultValue property. Anything else requires code.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
There are a number of ways to approach this, using code...such as
Code:Private Sub Form_BeforeUpdate(Cancel As Integer) If Nz(Me.Control1,"") = "" Then MsgBox "Control1 Must Not Be Left Blank!" Cancel = True Control1.SetFocus Exit Sub End If If Nz(Me.Control2, "") = "" Then MsgBox "Control2 Must Not Be Left Blank!" Cancel = True Control2.SetFocus Exit Sub End If End Sub
You could loop through some or all Controls and do the same thing. If the number of Controls makes the above too time consuming, given your situation, this will loop through all Textboxes and all Comboboxes and check that they're populated
Code:Private Sub Form_BeforeUpdate(Cancel As Integer) Dim ctl As Control Dim CName As String For Each ctl In Me.Controls Select Case ctl.ControlType Case acTextBox, acComboBox If Nz(ctl, "") = "" Then CName = ctl.Controls(0).Caption MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName Cancel = True ctl.SetFocus Exit Sub End If End Select Next ctl End Sub
You could also use the Tag Property to mark certain Controls, and then loop through all Controls but only check on/address the status of these 'marked' Controls.
To set the Tag Property for multiple Controls, all at once:
- Go into Form Design View
- Holding down <Shift> and Left clicking on each Control in turn.
- Go to Properties – Other and enter Marked in the Tag Property (just like that, no Quotation Marks)
Code:Private Sub Form_BeforeUpdate(Cancel As Integer) Dim ctl As Control Dim CName As String For Each ctl In Me.Controls If ctl.Tag = "marked" Then If Nz(ctl, "") = "" Then CName = ctl.Controls(0).Caption MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName Cancel = True ctl.SetFocus Exit Sub End If End If Next ctl End Sub
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
I have put in validation rule but it is not working. I have attached the DB. Please guide me.
Actually, ValidationRule alone won't assure a field is populated. If control does not get focus then the ValidationRule will not trigger.
Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, all caps is not recommended because it is harder to read.
Advise not to allow empty string in text fields. I always set this field property in table to No.
Sorry, I should have said ValidationRule alone will not assure field is populated. User must start input in the field for the rule to trigger. Just tabbing through the field will not trigger rule. So use it in combination with other settings.
Last edited by June7; 04-08-2019 at 07:59 PM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.
Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
Actually, ValidationRule alone won't assure a field is populated. If control does not get focus then the ValidationRule will not trigger.
Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, all caps is not recommended because it is harder to read.
Advise not to allow empty string in text fields. I always set this field property in table to No.
Sorry, I should have said ValidationRule alone will not assure field is populated. User must start input in the field for the rule to trigger. Just tabbing through the field will not trigger rule. So use it in combination with other settings.
Thanks for your advice. GOD BLESS YOU!!!!!
Thanks for your advice. GOD BLESS YOU!!!!!!As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.
Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!
Linq ;0)>
As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.
Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!
Linq ;0)>
I am attaching DB with the code in the Before Update Event. But I am not getting the desired result. Please tell me where I am making a mistake.
What are you getting that you don't appreciate? Seems to work.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Sorry, for my impatience. I was impatiently expecting the message to appear immediately when I leave the field. Doesn't matter, it is working fine. Thanks for your advice. GOD BLESS YOU!!!!!!As June7 indicated...assuring that a Control is populated cannot be done with Validation done using the Control's Properties...it has to be done using the Form_BeforeUpdate event...as my code showed in Post #3.
Anything based on the Control's events can be circumvented by simply ignoring the Control...i.e. not entering anything into it!
Linq ;0)>
The original code is,
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo TowardsError
If Nz(Me.DEPT, "") = "" Then
MsgBox "DEPT.???!!!"
Cancel = True
Me.DEPT.SetFocus
Exit Sub
End If
Exit Sub
TowardsError:
' You only get here if an error occurred.
' Show the error.
21 MsgBox Err.Number
MsgBox Err.Description
End Sub
I modified it and put it in a module as,
Sub depchk
On Error GoTo TowardsError
If Nz(Form_Cash_Office.Child65![DEPT], "") = "" Then
MsgBox "DEPT.???!!!"
Cancel = True
Form_Cash_Office.Child65![DEPT].SetFocus
Exit Sub
End If
Exit Sub
TowardsError:
' You only get here if an error occurred.
' Show the error.
21 MsgBox Err.Number
MsgBox Err.Description
End Sub
I call it as follows,
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call depchk
End Sub
My problem is how to substitute the statement ( Cancel = True ) in my Sub?
Why did you put code in a separate sub?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.