Check Box to Gray Out Areas

[emilyebba]

Hi,

I am totally new to Access and am enjoying learning to use this program.

I am building a database of Businesses. On one form we will enter in all the relevant info (name, address, type of business, etc) there are two sections. The first section is Property Owner and the second section is Business Operator.

In most cases the Property Owner and Business Operator are the same so you would not need to enter in additional Business Operator data.

I am hoping to have the Business Operator section "grayed out" until you tick a check box (labeled: Check here if Property Owner and Business Operator are different) which would then allow you to enter in Business Operator info if you need to. This is so you can easily move onto the next record and not tab through the Business Operator section since it would be empty.

I am not having luck using the Event Procedure. Any help would be advised. The name of my files are:

BusOpFirstName
BusOpLastName
Tick Box is called PropBusOwnSame and it is a Yes/No.

Thank you kindly!!!

 

[boblarson]

In the AfterUpdate event of the checkbox you can put:

Me.BusOpFirstName.Enabled = Me.PropBusOwnSame
Me.BusOpLastName.Enabled = Me.PropBusOwnSame

If only the two controls you can set their Disabled property to NO in design view and then have this code to enable/disable them. The one caveat here is that if you check the box, put data in those fields and then uncheck the box it would still retain that data. If that is wanted to be cleared you could use a modified version:

Me.BusOpFirstName.Enabled = Me.PropBusOwnSame
Me.BusOpLastName.Enabled = Me.PropBusOwnSame
If Not Me.PropBusOwnSame Then
   Me.BusOpFirstName = Null
   Me.BusOpLastName = Null
End If

 

[emilyebba]

Hi thank you for your response. That worked on the first four records I have enterend into this database to 'test'. When I clicked next for record 5 I got this error: Run time error '94' Invalid use of Null. I also did not understand what you said about: "If only the two controls you can set their Disabled property to NO in design view and then have this code to enable/disable them." Where is that? Thank you so much!!!

 

[emilyebba]

Oh I should add the "debugger" highlighted in yellow this line:
Me.BusOp1FirstName.Enabled = Me.PropBusOwnDIFF

Thanks!

 

[boblarson]

The error 94 means that somehow a null is being used where it shouldn't. So, if your fields for the BusOp name parts have NO NULLS set in the table then you won't be able to set them to null like I showed. If you have No Nulls but empty strings allowed you can change the = Null part to vbNullstring (which is an empty string).

Also, to set the disabled property in design view you would open the form in design view and then you click on the control and in the data tab you can set ENABLED to NO.

 

[emilyebba]

Im using Access 2003 and am totally new so bear with me. I cant find where to set the No Nulls. I looked in the table?

Thanks

 

[boblarson]

Sorry, no nulls would be the REQUIRED property. Allow Zero Length refers to the empty strings.

 

[emilyebba]

Hi I have done everything you have said and when I go to click on the next record I still get that same error? Thanks.

 

[boblarson]

Can you upload a copy of your database (with fake data)? Just remember to run COMPACT AND REPAIR first and then right-click on the file and select SEND TO > COMPRESSED FOLDER to zip it before uploading here.

 

[emilyebba]

Here you go. Thank you!!! SO much!!!

 

[boblarson]

To solve that error set the DEFAULT VALUE of the checkbox to FALSE.

And, since you have other stuff there for the Operator I would use a TAG and code to iterate through the controls. I'll upload a revised sample as soon as I'm done with lunch.

 

[boblarson]

Okay here you go. First I put the word BusOp into the TAG property of each of the controls we want enabled/disabled by the checkbox. That property is found by selecting the control and clicking on the OTHER tab in the properties dialog. Next I modified the form's code to have a new function in it:

Function SetBusOp()
Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.Tag = "BusOp" Then
        ctl.Enabled = Me.PropBusOwnDIFF
    End If
Next
End Function

And then I call that in the checkbox After Update event and in the On Current event:

SetBusOp

 

[emilyebba]

Thank you SO MUCH for all your help. I dont understand what you want me to do and why? Can you explain? Thanks!

 

[boblarson]

Thank you SO MUCH for all your help. I dont understand what you want me to do and why? Can you explain? Thanks!

The example should give you some idea about it but basically instead of using code to do each control, we use a loop through the form's control collection and, using the TAG property of the control, we can do things conditionally. So, in this case I put BusOP in the TAG property of each of the controls that you want to become available when the checkbox is checked. The code I gave with the Dim ctl As Control and following does loop through all of the controls on the form and will make the applicable controls enabled or not depending on the check box's value.

Does that help explain it?

 

[emilyebba]

You are basically giving another option to perform the same checkbox function yes? Either will work? Is the latter a better way for Access to do this? Thanks.

 

[boblarson]

You are basically giving another option to perform the same checkbox function yes? Either will work?

Yes

Is the latter a better way for Access to do this? Thanks.

Yes, it is more efficient and requires less code to modify later if there are changes. In fact, if you end up adding a field and control to the form you can just put the word BusOp in the tag property of that control and not even touch the code.

 

[emilyebba]

Thank you. My last question is if you used this modified version would you need to set the additional parameters you listed for the first way (i.e. NO NULLS set in the table, set the disabled property in design view , DEFAULT VALUE of the checkbox to FALSE ...) Your help has been amazing. THANKS!

 

[boblarson]

1. The "Required" property in the table for those fields should be set to NO.

2. Yes, you would set the "Enabled" property for those BusOps controls to NO in design view.

3. Yes, you need to set the DEFAULT VALUE property of the checkbox to FALSE.

 

[emilyebba]

Thank you so much!

 

[emilyebba]

Mr. Larson,

Hi I am writing again on the same thread because what I am trying to do is quite similiar. All of your advice has worked great and my database is working wonderfully!

Okay I have another checkbox on my form that automatically is ENABLED on the form. What I would like to do is if the user wants to uncheck the box (making the customer inactive) I have a Date field that is currently grayed out that I would then like to have not gray. So this is effectively the opposite of my original request:

Checkbox for Active on the form is ENABLED
InactiveDate Box is grayed out

When you uncheck the box for "Active" I would like the InActiveDate to become ENABLED.

I have tried at length to figure this out but am unable to.

I also did not have success using the Tag function. So I am going back to the original code which is working fantastic.

Can you advise on how to enable a grayed out area when you uncheck the checkbox?

Thanks!!