By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,214 Members | 1,516 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,214 IT Pros & Developers. It's quick & easy.

How to disable certain fields in a form

100+
P: 418
tblExpDetails has various fields including two Yes/No fields and one Category field. frmExpDetails is based on this table.

I would like to add some restrictions wWhen an end user is using frmExpDetails to add data:

1. Restrict selection of one check box at a time. i.e., if one check box is selected the end user will not be able to check the other. If he/she changes her mind she could edit his/her selection but only one check box can be selected.

2. Based on a category selected, certain fields are enabled. For example, if one selects "Payroll" as a category, fields such as, PayrollDate, PayrollNo and PayAmount are enabled. Other fields such as "InvoiceNo" "GrossAmount" are disabled.

Can it be done? Your assistance is much appreciated.

Thanks.
Jul 22 '09 #1

✓ answered by ajalwaysus

I assume you mean you changed Category to a ComboBox, not a Text field, since you mention you have a value list, if this is the case this code should do the trick:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Catergory_AfterUpdate()
  2.     If Me.Category.Value = "AP" Then
  3.         Me.ApAmount.Enabled = True
  4.         Me.ApAmount.SetFocus
  5.         Me.PayAmount.Enabled = False
  6.     ElseIf Me.Category.Value = "PayRoll" Then
  7.         Me.ApAmount.Enabled = False
  8.         Me.PayAmount.Enabled = True
  9.         Me.PayAmount.SetFocus
  10.     End If
  11. End Sub
  12.  
If this isn't a ComboBox, I really recommend one, so you aren't relying on the user to enter in the correct format of Category.
Because if a user entered "Pay Roll" as a category, the code wouldn't work, because "PayRoll" <> "Pay Roll"

Hope this helps.
-AJ

Share this Question
Share on Google+
24 Replies


Expert 100+
P: 266
This can definitely be done.

1. In regards to the check box, you would probably want to check the enabling/disabling of the check boxes on the "After Update" event.
An if statement can be used to determine the status of one, and disable the other.

2.After the update of the category field, you can enable certain fields and disable others, just do a check on the value of the field after update.
But I would recommend actually setting the fields that can be used to visible, and those which cannot, to not visible. This way you save room, and you don't offer more fields than the user needs.

Let me know if you have any question.

-AJ
Jul 22 '09 #2

mshmyob
Expert 100+
P: 904
Instead of check boxes why don't you use the option buttons since that is the function of option buttons. That way you do not have to fool around with code for enabling and disabling, etc.

cheers,

@MNNovice
Jul 22 '09 #3

Expert 100+
P: 266
@mshmyob
Nice catch, should have seen that. I concur.

-AJ
Jul 22 '09 #4

100+
P: 418
Ok I change the check box to option list. That solves problem 1.

2.After the update of the category field, you can enable certain fields and disable others, just do a check on the value of the field after update.
But I would recommend actually setting the fields that can be used to visible, and those which cannot, to not visible. This way you save room, and you don't offer more fields than the user needs.
I am not sure I quite understand this one. Can you please elaborate a little? More specifically, how can I restrict visibility AfterUpdate?

Thanks.
Jul 22 '09 #5

100+
P: 675
1) Two checkboxes have 4 possibilities: NN NY YN YY. You state that YY is not valid, but NY and YN are valid. You do not say whether NN is a valid entry.
a) If NN is not valid, then the checkboxes can be inside a frame as suggested by mshmyob. Access will allow only one to be checked. RadioButtons would then be preferrable, as this is the normal way to display a "Choose One" list.
b) If NN is valid, then each checkbox's OnClick need to set the other checkbox to False.
Expand|Select|Wrap|Line Numbers
  1. Private Sub chk1_Click()
  2.     chk2 = False
  3. End Sub
  4. Private Sub chk2_Click()
  5.     chk1 = False
  6. End Sub
  7.  
2)Many ways to do this. The correct choice may depend on whether all fields should be visible, or only the fields that can be edited should be displayed.
ajalwaysus suggests one idea, and suggests only showing the fields to be edited. A Tab Control would do this also, and would be evident that all fields were not visible. However, if too many fields were displayed for several conditions, they should probably all be visible but disabled.

2.After the update of the category field, you can enable certain fields and disable others, just do a check on the value of the field after update
This might work better if it were on the 'Change' event. The AfterUpdate event occurs after the control loses focus. So the user has to click something else to fire the AfterUpdate. This might be something that they really don't want to select, because it won't be enabled/visible for the condition they have selected. Confusing. Also, as this control will now have the focus, it can't be disabled. The focus has to be moved first. To what? Programmer's choice, but should be user's choice.
Jul 22 '09 #6

Expert 100+
P: 1,287
The AfterUpdate event of an option group definitely triggers immediately.
Jul 22 '09 #7

100+
P: 675
The option group is not the choice here.
2. Based on a category selected,
so I assumed that this was another control, probably a ComboBox. Does AfterUpdate trigger when changed also?
Jul 22 '09 #8

Expert 100+
P: 266
I guess the first few questions to clarify all this should be:
1. Is the Category field a drop down?
(I ask, because if you are dictating what can and can't be enable/visible to the user based on Category, you want to make sure the values are exactly what you want them to be. Also, if this is a drop down, the after update triggers right after you select a value from the list.)

2. How many fields are you wanting to enable and disable based on category? (This will help understand if you should break them out on tabs, or if you should keep them all on one form)

This code is how you disable a field:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Category_AfterUpdate()
  2.     Me.Field1.Enabled = False
  3. End Sub
  4.  
This code is how you make a field not visible:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Category_AfterUpdate()
  2.     Me.Field1.Visible= False
  3. End Sub
  4.  
The issue with visible vs. enabled is a programmer's choice that makes it easiest on the user. I feel that an equal amount of programming will be needed for either choice, but I usually choose to make fields not visible, that way I don't get questions from the user like "Why can't I use this field?". Your call.
Jul 22 '09 #9

100+
P: 418
ajalwaysus:

To expand on my form. The category is a drop down list of two items: "AP" and "Payroll". If AP is selected I would like "ApAmount" to be enabled for editing otherwise it can be displayed but grayed out. If Payroll is selected I would like PayAmount to have the same features.

(Now, why I have two Amounts? Well, AP comes as a gross amount but Payroll is entered as a Net Amount. This means that there are other formula entered which calculates the net amount for each AP transaction. This is irrelevant for this question I have now.)

I have changed the category from Check Box to text box with a value list as a drop down list for "AP" and "Payroll"

I understand the codes for disable/visible as you have explained. However, I would need to know exactly where I should add these codes to?

Thanks.
Jul 22 '09 #10

Expert 100+
P: 1,287
Sorry, I got confused about the category. The AfterUpdate event of a combo box is triggered when a selection is made from a dropdown, but not when a value is typed in, until the focus changes. The change even happens every time a letter is typed into or deleted from the combo box, which will make things difficult. Perhaps a list box is more appropriate in this case.
Jul 22 '09 #11

100+
P: 675
From Access 2003 Help
The AfterUpdate event is triggered when a control or record is updated. Within a record, changed data in each control is updated when the control loses the focus or when the user presses ENTER or TAB.
But
The Change event occurs when the contents of a text box or the text portion of a combo box changes. It also occurs when you move from one page to another page in a tab control.
I know I'm getting off-topic here, but I have been assuming that a 'Change' was not actually made until the control lost focus. Sort of a 'pending' change. AfterUpdate would make the change permanent for the control, but not the record if the control is bound.

Can you explain the difference between Change and AfterUpdate for ComboBoxes as I am confused?
Jul 22 '09 #12

Expert 100+
P: 266
I assume you mean you changed Category to a ComboBox, not a Text field, since you mention you have a value list, if this is the case this code should do the trick:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Catergory_AfterUpdate()
  2.     If Me.Category.Value = "AP" Then
  3.         Me.ApAmount.Enabled = True
  4.         Me.ApAmount.SetFocus
  5.         Me.PayAmount.Enabled = False
  6.     ElseIf Me.Category.Value = "PayRoll" Then
  7.         Me.ApAmount.Enabled = False
  8.         Me.PayAmount.Enabled = True
  9.         Me.PayAmount.SetFocus
  10.     End If
  11. End Sub
  12.  
If this isn't a ComboBox, I really recommend one, so you aren't relying on the user to enter in the correct format of Category.
Because if a user entered "Pay Roll" as a category, the code wouldn't work, because "PayRoll" <> "Pay Roll"

Hope this helps.
-AJ
Jul 22 '09 #13

100+
P: 418
It is a combobox with a value list. Thanks for the code, I think I know what I have to do. I shall let you know if I need further help.

I appreciate your help.
Jul 22 '09 #14

Expert 100+
P: 266
@OldBirdman
The difference, is that the:
- OnChange event triggers as soon as you begin entering info, it trigger with the first letter you type, which does not help in this case because you would like them to completly type the value before you evaluate it.

-AfterUpdate waits until you make a selection in a combobox, or tab out of a text box before it triggers. This way you can evaluate the entire value of the field.

Hope this clears it up.
-AJ
Jul 22 '09 #15

100+
P: 418
ajalwaysus:

Here is the code I used but somehow it's not working. I mean when i select Payroll as a category and tab out of cboCategory, my cursor moves to Type / cboVendor etc.

By the way, I goofed up. CategoryID is a combo box with tblCategory in its row source and not a value list as I said earlier.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CategoryID_AfterUpdate()
  2.     If Me.CategoryID.Value = "AP" Then
  3.         Me.ApAmount.Enabled = True
  4.         Me.ApAmount.SetFocus
  5.         Me.Amount.Enabled = False
  6.  
  7.     ElseIf Me.CategoryID.Value = "Payroll" Then
  8.         Me.ApAmount.Enabled = False
  9.         Me.Type.Enabled = False
  10.         Me.cboVendor.Enabled = False
  11.         Me.Amount.Enabled = True
  12.         Me.Amount.SetFocus
  13.  
  14.     ElseIf Me.CategoryID.Value = "Allocation" Then
  15.         Me.ApAmount.Enabled = False
  16.         Me.Type.Enabled = False
  17.         Me.cboVendor.Enabled = False
  18.         Me.Amount.Enabled = True
  19.         Me.Amount.SetFocus
  20.     End If
  21. End Sub
Can you please look over these codes and tell me what I am doing wrong? Do I need to make reference to the subform for these fields? Thanks.
Jul 22 '09 #16

Expert 100+
P: 266
The row source makes a difference if you have multiple columns in your combo box. If you are displaying the Category name, but there is an ID behind it, then you need to set the code to run off the ID, not the name

i.e.
Expand|Select|Wrap|Line Numbers
  1. Me.CategoryID.Value = 1
  2.  
If this doesn't make sense, please post the rowsource code for the dropdown list.

Also, if you have a subform, I need to know where the Category is on the form (Form or SubForm?), as well as all the controls you are enabling and disabling.

Finally, I goofed, SetFocus should be on the last line under each part of the IF statement.

-AJ
Jul 22 '09 #17

100+
P: 675
Finally, I goofed, SetFocus should be on the last line under each part of the IF statement.
No! Because tabbing from the combobox might result in a control having the focus that will be changed to .Enabled = False, the correct order should be:
Expand|Select|Wrap|Line Numbers
  1. Me.ctl1.Enabled = True 'At least one control should be enabled
  2. Me.ctl1.SetFocus       'This will work, as it was just enabled, above
  3. Me.ctl2.Enabled =      'Enable/Disable remaining controls
  4. Me.ctl3.Enabled =      'Enable/Disable remaining controls
  5. ...
  6.  
If the SetFocus is the last line of code, then an error will probably result. The 'Category' has changed, and the combobox tabbed away from. Focus is now on a control that will probably be set .Enabled = False.
Jul 22 '09 #18

Expert 100+
P: 266
No, because you are only setting focus on a field that has been enabled. You set the focus after you have enabled/disabled all the fields you need to, just as a courtesy to the user so you insure they start on the first field they need to enter data in, because you can't be certain where the cursor will end up after you have disabled some of the fields.
Jul 22 '09 #19

100+
P: 418
ajalwaysus:

It worked. This is great!!!! thank you. Here is the complete code that worked for my purpose.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CategoryID_AfterUpdate()
  2.     If Me.CategoryID.Value = 1 Then
  3.         Me.ApAmount.Enabled = True
  4.         Me.Type.Enabled = True
  5.         Me.cboVendor.Enabled = True
  6.         Me.txtEchoAmount.Enabled = True
  7.         Me.Amount.Enabled = False
  8.         Me.Type.SetFocus
  9.  
  10.  
  11.     ElseIf Me.CategoryID.Value = 2 Then
  12.         Me.ApAmount.Enabled = False
  13.         Me.Type.Enabled = False
  14.         Me.cboVendor.Enabled = False
  15.         Me.txtEchoAmount.Enabled = False
  16.         Me.Amount.Enabled = True
  17.         Me.Date.SetFocus
  18.  
  19.     ElseIf Me.CategoryID.Value = 3 Then
  20.         Me.ApAmount.Enabled = False
  21.         Me.Type.Enabled = False
  22.         Me.cboVendor.Enabled = False
  23.         Me.txtEchoAmount.Enabled = False
  24.         Me.Amount.Enabled = True
  25.         Me.Date.SetFocus
  26.     End If
  27. End Sub
Jul 22 '09 #20

Expert 100+
P: 1,287
Except, if you type the value into the combo box rather than click it, the event happens after you move the focus elsewhere. You would need to move it somewhere safe before you try to disable controls.
Jul 22 '09 #21

100+
P: 418
ChipR:

I type just "P" to select Payroll and it's working okay. I believe I don't quite understand when you say, " You would need to move it somewhere safe before you try to disable controls". What do you suggest?
Jul 22 '09 #22

Expert 100+
P: 266
@MNNovice
One final note, would be to make sure your ComboBox is set "Limit To List = Yes", or else you will need to code for when someone types in their own value.

And to ChipR's post, point taken.

Good discussion! =)
-AJ
Jul 22 '09 #23

Expert 100+
P: 1,287
Had to test this real quick to make sure it was as I expected, and it's not!

I typed a value in a combo box, then clicked on a text box that was to be disabled in the AfterUpdate of the combo. The text box became disabled, but the focus remained in the combo. The text box to be disabled never got focus.

So, it is basically safe to disable any control in the AfterUpdate event, unless you specifically move the focus to it first, or it's in a subform where it might have focus.
Jul 22 '09 #24

100+
P: 418
ajalwaysus:

I set the value "Limit to List" for CategoryID
Jul 22 '09 #25

Post your reply

Sign in to post your reply or Sign up for a free account.