How to disable certain fields in a form

[MNNovice]

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.

[ajalwaysus]

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

[mshmyob]

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

[ajalwaysus]

@mshmyob
Nice catch, should have seen that. I concur.

-AJ

[MNNovice]

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.

[OldBirdman]

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.

Private Sub chk1_Click()
    chk2 = False
End Sub
Private Sub chk2_Click()
    chk1 = False
End Sub
 

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.

[ChipR]

The AfterUpdate event of an option group definitely triggers immediately.

[OldBirdman]

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?

[ajalwaysus]

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:

Private Sub Category_AfterUpdate()
    Me.Field1.Enabled = False
End Sub
 

This code is how you make a field not visible:

Private Sub Category_AfterUpdate()
    Me.Field1.Visible= False
End Sub
 

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.

[MNNovice]

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.

[ChipR]

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.

[OldBirdman]

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?

[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:

Private Sub Catergory_AfterUpdate()
    If Me.Category.Value = "AP" Then
        Me.ApAmount.Enabled = True
        Me.ApAmount.SetFocus
        Me.PayAmount.Enabled = False
    ElseIf Me.Category.Value = "PayRoll" Then
        Me.ApAmount.Enabled = False
        Me.PayAmount.Enabled = True
        Me.PayAmount.SetFocus
    End If
End Sub
 

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

[MNNovice]

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.

[ajalwaysus]

@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

[MNNovice]

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.

Private Sub CategoryID_AfterUpdate()
    If Me.CategoryID.Value = "AP" Then
        Me.ApAmount.Enabled = True
        Me.ApAmount.SetFocus
        Me.Amount.Enabled = False
 
    ElseIf Me.CategoryID.Value = "Payroll" Then
        Me.ApAmount.Enabled = False
        Me.Type.Enabled = False
        Me.cboVendor.Enabled = False
        Me.Amount.Enabled = True
        Me.Amount.SetFocus
 
    ElseIf Me.CategoryID.Value = "Allocation" Then
        Me.ApAmount.Enabled = False
        Me.Type.Enabled = False
        Me.cboVendor.Enabled = False
        Me.Amount.Enabled = True
        Me.Amount.SetFocus
    End If
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.

[ajalwaysus]

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.

Me.CategoryID.Value = 1
 

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

[OldBirdman]

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:

Me.ctl1.Enabled = True 'At least one control should be enabled
Me.ctl1.SetFocus       'This will work, as it was just enabled, above
Me.ctl2.Enabled =      'Enable/Disable remaining controls
Me.ctl3.Enabled =      'Enable/Disable remaining controls
...
 

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.

[ajalwaysus]

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.

[MNNovice]

ajalwaysus:

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

Private Sub CategoryID_AfterUpdate()
    If Me.CategoryID.Value = 1 Then
        Me.ApAmount.Enabled = True
        Me.Type.Enabled = True
        Me.cboVendor.Enabled = True
        Me.txtEchoAmount.Enabled = True
        Me.Amount.Enabled = False
        Me.Type.SetFocus
 
 
    ElseIf Me.CategoryID.Value = 2 Then
        Me.ApAmount.Enabled = False
        Me.Type.Enabled = False
        Me.cboVendor.Enabled = False
        Me.txtEchoAmount.Enabled = False
        Me.Amount.Enabled = True
        Me.Date.SetFocus
 
    ElseIf Me.CategoryID.Value = 3 Then
        Me.ApAmount.Enabled = False
        Me.Type.Enabled = False
        Me.cboVendor.Enabled = False
        Me.txtEchoAmount.Enabled = False
        Me.Amount.Enabled = True
        Me.Date.SetFocus
    End If
End Sub

[ChipR]

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.

[MNNovice]

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?

[ajalwaysus]

@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

[ChipR]

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.

[MNNovice]

ajalwaysus:

I set the value "Limit to List" for CategoryID