Add Required Fields to Forms and can i change required field Validation Text (1 Viewer)

EmergentGuru · Sep 14, 2016

Hello, where can you go to make a field in a form a required field. For example you can do it in the table but not in the form. Also, Like when you set validation text for a validation rules, is there an option for setting error text for required fields to make things more user friendly?

For example the validation rule could just be that the field has data and the validation text shows this field is required.

Thanks

missinglinq · Sep 14, 2016

Here's one way: 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)>

EmergentGuru · Sep 14, 2016

I assume I am not putting that in verbatim. Assume that the field name is "Apple", what would the code look like? thanks

missinglinq · Sep 14, 2016

No modification is needed to this code...if the Field is named Apple, the message that appears will say

Following field is required:

Apple

If the Field is named Pear

Following field is required:

Pear

and so forth. Also, Focus will go the Field.

Linq ;0)>

EmergentGuru · Sep 15, 2016

OK Thanks, where do I put the code? do I put it in an event command in the property sheet? Thanks

Minty · Sep 15, 2016

The Code was shown in the correct place - the forms before update property;

Code:

[B]Private Sub Form_BeforeUpdate...[/B]

EmergentGuru · Sep 15, 2016

It worked and when I leave it blank I get Runtime Error 2467

Is there a way to make a validation text saying that this field must be entered? Thanks

EmergentGuru · Sep 15, 2016

On a related not, I put in another Post because I did not want to highjack this post. The new one is below.

"Can you make a field required perameters of another field (Possibly by iff statement)"

Thanks

EmergentGuru · Sep 16, 2016

Minty, When I don't put the data in, I then get the error message. The error message allows for "end" or " Debugger". I would prefer for those options not to exist for the end user and all I would really want is a statement saying that this field is required. The only option being to close the error message and resume data input. Thanks

Minty · Sep 16, 2016

Are you saying when you leave a value blank you get an error message?
If so when you press debugger it will highlight a line in your code, can you post up your code and show which line is giving you the problem.

EmergentGuru · Sep 16, 2016

well to test it, I only used the code and tag on one line. When I leave that cell/field it gives me the error message and yes with an option for a debugger button which then opens the sql.

EmergentGuru · Sep 16, 2016

Private Sub Room_Area_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

The CName = ctl.Controls(0).Caption is highlighted in yellow.

Minty · Sep 16, 2016

You have put the code against the control. You need to put it in the FORMS Before Update property, not the control.

EmergentGuru · Sep 16, 2016

ok I put it in the forms box and still get the error and the debug option. Thannks

static · Sep 16, 2016

If a control doesn't have a label it wont have a caption so get the control name instead

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
    
    On Error Resume Next
    CName = ctl.Controls(0).Caption
    
    Select Case Err.Number
    Case 2467: CName = ctl.Name
    End Select
    
    MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
    Cancel = True
    ctl.SetFocus
    Exit Sub
    End If
    End If
    Next ctl
End Sub

Add Required Fields to Forms and can i change required field Validation Text (1 Viewer)

EmergentGuru

Registered User.

missinglinq

AWF VIP

EmergentGuru

Registered User.

missinglinq

AWF VIP

EmergentGuru

Registered User.

Minty

AWF VIP

EmergentGuru

Registered User.

EmergentGuru

Registered User.

EmergentGuru

Registered User.

Minty

AWF VIP

EmergentGuru

Registered User.

EmergentGuru

Registered User.

Minty

AWF VIP

EmergentGuru

Registered User.

static

Registered User.

Similar threads

Users who are viewing this thread