UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Expression, Any Versions    
 
   
Quinto1
post Apr 4 2018, 08:07 AM
Post#1



Posts: 90
Joined: 23-April 16



I need to make a control field required. iif control field X has the word "Source" then field y is require, data entry is made with a form.

Thank you
Go to the top of the page
 
Doug Steele
post Apr 4 2018, 08:13 AM
Post#2


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Easiest way would be to use VBA in the form's BeforeUpdate event.

Something like

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  If InStr(Nz(Me!FieldX, vbNullString), "Source") > 1 Then
    If Len(Trim(Me!FieldY & vbNullString)) = 0 Then
      MsgBox "You've got 'Source' in FieldX: that means you must provide a value for FieldY"
      Me!FieldX.SetFocus
      Cancel = True
    End If
  End If
  
End Sub
Go to the top of the page
 
LPurvis
post Apr 4 2018, 08:58 AM
Post#3


UtterAccess Editor
Posts: 16,335
Joined: 27-June 06
From: England (North East / South Yorks)


Of course, if this is a fundamental business requirement, there's nothing stopping you creating a Validation rule at the database level.
You could chose to maintain the form's BeforeUpdate check as a more user-friendly presentation, with the validation rule there to prevent it absolutely.
("Validation Rule" as a phrase assumes you're using an Access UI on the backend, if not then it's a Constraint.)

Cheers
Go to the top of the page
 
Quinto1
post Apr 4 2018, 09:27 AM
Post#4



Posts: 90
Joined: 23-April 16



Sorry, I was not able to get that to work.
I am not that familiar with code.
Can you look at the attached that shows the fields names and see if I can get get it.

Thank you very much

Attached File(s)
Attached File  Capture.JPG ( 294.24K )Number of downloads: 6
 
Go to the top of the page
 
Doug Steele
post Apr 4 2018, 09:30 AM
Post#5


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Unfortunately, that doesn't show me what the controls on the form are named.

Replace FieldX and FieldY in my code with the appropriate names. (If there are spaces in the names, which is always a bad idea, make sure you put square brackets around the name, as in Me.[Name of Control with Spaces])
Go to the top of the page
 
Quinto1
post Apr 4 2018, 09:37 AM
Post#6



Posts: 90
Joined: 23-April 16



Yes, the names in the solid blue or white are the control names taken from the Table, Query, Form
Go to the top of the page
 
Quinto1
post Apr 4 2018, 09:41 AM
Post#7



Posts: 90
Joined: 23-April 16



ReferralSource and EmployeeReferral

THe employeereferral would be the name of the employee making the referral and the selection in ReferralSource would be Employee Referral
Go to the top of the page
 
Doug Steele
post Apr 4 2018, 10:36 AM
Post#8


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


So did you change the names according?

It's not obvious to me which is "FieldX" and which is "FieldY", but you probably want

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  If InStr(Nz(Me!ReferralSource, vbNullString), "Source") > 1 Then
    If Len(Trim(Me!EmployeeReferral & vbNullString)) = 0 Then
      MsgBox "You've got 'Source' in ReferralSource: that means you must provide a value for EmployeeReferral"
      Me!FieldX.SetFocus
      Cancel = True
    End If
  End If
  
End Sub

or

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  If InStr(Nz(Me!EmployeeReferral, vbNullString), "Source") > 1 Then
    If Len(Trim(Me!ReferralSource & vbNullString)) = 0 Then
      MsgBox "You've got 'Source' in EmployeeReferral: that means you must provide a value for ReferralSource"
      Me!FieldX.SetFocus
      Cancel = True
    End If
  End If
  
End Sub


Go to the top of the page
 
Quinto1
post Apr 4 2018, 12:18 PM
Post#9



Posts: 90
Joined: 23-April 16



I tried different version but was not able to get to work
If "Employee Referral" is selected in the field [ReferralSource] then the name of the employee referring an applicant must be entered in [EmployeeReferral] field if the referral or the source of the applicant was due to other recruiting strategy than the [EmployeeReferral] is null or empty

THank you for trying to help

Quinto

Go to the top of the page
 
Doug Steele
post Apr 4 2018, 12:47 PM
Post#10


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You mention

QUOTE
If "Employee Referral" is selected in the field [ReferralSource]


That sounds as though you're using a combo box. If that's the case, what's the bound column in the combo box: is it a number, or the text? (Note that the code is looking for "Source", not for "Employee Referral"!)
Go to the top of the page
 
Quinto1
post Apr 4 2018, 01:55 PM
Post#11



Posts: 90
Joined: 23-April 16



Yes I should have know that that might make a difference.
The selections come from tblSource column one and the field name is source, only one column
The form Field is Label17
Go to the top of the page
 
Doug Steele
post Apr 4 2018, 02:04 PM
Post#12


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Have you changed the code so it isn't looking for the word Source in ReferralSource?

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  If Nz(Me!ReferralSource, vbNullString) = "Employee Referral" Then
    If Len(Trim(Me!EmployeeReferral & vbNullString)) = 0 Then
      MsgBox "You've selected Employee Referral: that means you must provide a value for EmployeeReferral"
      Me!FieldX.SetFocus
      Cancel = True
    End If
  End If
  
End Sub
Go to the top of the page
 
Quinto1
post Apr 5 2018, 02:09 PM
Post#13



Posts: 90
Joined: 23-April 16



THank you for trying to help me
I tried every way and I cannot make it work.
I will leave it as is and depend on the data entry person to do it.

Quinto

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 10:34 PM