Setting Control's property based on another's Value

StephenB

Registered User.
Local time
Today, 07:32
Joined
Apr 18, 2002
Messages
101
Hello, Seems like this should be simple, but can't find anything that helps.

I have a form with three fields: Field 1, Field 2, Field 3. Field 1 is a dropdown list/combo box with values "A" and "B" as options.

Rule: If Value "A" is selected in in Field 1, then Field 2 must become required and Field 3 must become not-required (optional).
If value "B" is selected in Field 1, then Field 3 must become required, and Field 2 must become not-required/optional.

I was poking around with the following, to no avail. I'm obviously barking up the wrong tree.

If [Field 1] = "A" Then
[Field 2].Properties.required = true
Else: [Field 2].Properties.Required =false

I'm trying to work something into the On Current Event on the form.

Thanks for any insight.
 
Last edited:
Hello, Seems like this should be simple, but can't find anything that helps.

I have a form with three fields: Field 1, Field 2, Field 3. Field 1 is a dropdown list/combo box with values "A" and "B" as options.

Rule: If Value "A" is selected in in Field 1, then Field 2 must become required and Field 3 must become not-required (optional).
If value "B" is selected in Field 1, then Field 3 must become required, and Field 2 must become not-required/optional.

I was poking around with the following, to no avail. I'm obviously barking up the wrong tree.

If [Field 1] = "A" Then
[Field 2].Properties.required = true
Else: [Field 2].Properties.Required =false

I'm trying to work something into the On Current Event on the form.

Thanks for any insight.


Note: Form's do not have fields. Tables have fields. Forms have controls.

AFAIK, a form control does not have a required property.

To do what you are asking, You will need to change the required property in the table design. To do this, you will have to close all forms/reports/queries that are using the table. Edit the table design for ALL records . Then you can Open the form to edit the data. I would not try this since it must be true for all records.

I normally handle the data validation in the form's before update event.

Example:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

' perform data validation
If IsNull(Me.CompanyName) Then

   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   DoCmd.GoToControl "CompanyName"
      
   Cancel = True
   Exit Sub 

End If

End Sub
 
Hello Coack. Thank you for the correction on fields v. controls. Just a few things...
-I can't make the field in the table required because it's not always required, it just can't be null when a certain selection is made on another field.
-The above code will always give me a warning if I leave the control blank, but that's not what I'm trying to accomplish.
-Perhaps if I put it this way, (to continue my format in the original post)...
Controls 2 and 3 need to be populated depending on the value selected in Control 1.
So if Value A is selected in Control 1, then Control 2 must be populated while Control 3 is optional.
If Value B is selected in Control 1, then Control 3 must be populated and Control 2 becomes optional.

Hope this helps clarify, and thank you again for your above post.
 
Here's what I ended up using.

Private Sub Form_AfterUpdate()
If [Source] = "PCA" And IsNull([PCA]) = True Then
MsgBox "You selected "PCA" as the Source, but did not enter an ID in the PCA field. Please enter one.", vbExclamation, "PCA"
[PCA].SetFocus
Else:
End If

If Source = "RMA" And IsNull([RMA]) = True Then
MsgBox "You selected "RMA" as the Source, but did not enter an ID Number in the RMA field. Please enter one.", vbExclamation, "RMA"
[RMA].SetFocus
Else:
End If

End Sub

I'm happy with the results of the above, but feel free to recommend a better method for those coming behind me.
 
Some broad-brush ideas.

In a form, you cannot make something required or not. But you can put a BEFORE_UPDATE event in the form to make that VBA code look at the values and decide whether the record is good to go. If it is not, the form's BEFORE_UPDATE allows you to cancel it. You could then put up a message box saying "Fill in box 6, dummy!" (Or however polite you want to be...)

As to how I generally do this, I make the TAB ORDER of the controls occur such that by default, the important control gets focus first. In that control's LOST_FOCUS event, check its value or do something like this:

CONTROL_B.ENABLED = ( CONTROL_A = "A")
CONTROL_C.ENABLED = ( CONTROL_A = "B")

You could also do the same kind of statements for the .VISIBLE property and maybe could set some default value for the non-enabled control.

I use a Message Box (with OK_ONLY option) in the BEFORE_UPDATE code to test what is going on and to say "You cannot store this record until you have a value for C." (Or B, or whatever.) I also fix it so that when you click the OK button on the message box, which exits it, I do a .SETFOCUS on the box that needs to be filled in.
 
Thanks Doc. I can definately see how I was barking up the wrong tree trying to set the required property. I'll keep your tips in mind.
Thanks again.
 

Users who are viewing this thread

Back
Top Bottom