Enabling a control based on another

[MattioMatt]

Hi all,

I'm having some problems with enabling/disabling a comboxbox control based on the result of a checkbox on the same form.

I currently have a checkbox control (chkStatus) which is locked so that the end user cannot change it, I then also have a combobox (cmboDASStatus). I'm trying to get it so that a combobox control property isn't enabled if a checkbox proptery is false and I am doing this through the form's OnLoad Event.
It sounds quite simple to do but it isn't working, the combobox property is enabled no matter what the result of the checkbox is.

Here is the code I am using:

If Me.chkStatus = False Then
Me.cmboDASStatus.Enabled = False
Else
Me.cmboDASStatus.Enabled = True
End If

Any ideas of where I am going wrong? Because it seems so simple I cannot understand why cmboDASStatus is enabled no matter on the result of the checkbox.

 

[Minty]

I would put the code on the On_Current event as I suspect the data hasn't loaded for the check to work on load, also your code can be simplified;

Me.cmboDASStatus.Enabled = Me.chkStatus

 

[MattioMatt]

Hi Minty,

I've tried it in the On Current event and unfortunately seeing the same behavior. It's enabled either way.

I tried my existing code and your suggestion to simplify it.

If Me.chkStatus = False Then
Me.cmboDASStatus.Enabled = Me.chkStatus
End If

 

[JHB]

Have you checked that Me.chkStatus actually is false?
Does it work if you only have, (regardless if chkStatus is false or not):

Me.cmboDASStatus.Enabled = False

 

[isladogs]

Agree with Minty - Form_Current should work

Go to form design view and check you haven't locked your combo box as well!

NOTE:
Using Form_Load would only affect the startup state.
It wouldn't be updated when you change records

 

[MattioMatt]

Hi JHB

Yes it works fine and the control is disabled using your suggestion above without the if statement.

The checkbox is showing true/false as expected. To confirm it I've added an unbound text box to the form and put it's control source as:

=IIf([chkStatus],"YES","NO")

Again this gives me the correct result I am expecting.

 

[Minty]

My suggestion was all the code you needed. No If statement at all.

 

[MattioMatt]

My suggestion was all the code you needed. No If statement at all.

If I try without the If statement I get the following error

"Run-time error 94: Invalid use of Null"

 

[MattioMatt]

I've just tried unlocking it and adding the code to the checkbox before update event and it works fine using both my original code and the code you suggested Minty. So there has to be something not quite right with the On current or previously on load event? I just can't figure it out :S

 

[Minty]

That means your check box isn't False or True but "No Value". Ideally if you don't want nulls, you need to set a default value for your records either True Or False

 

[MattioMatt]

That means your check box isn't False or True but "No Value". Ideally if you don't want nulls, you need to set a default value for your records either True Or False

Hi Minty,

Apologies but I am not sure I understand? In the table, I see the checkbox checked if it is true and not checked and therefore false.
In the table design view the default value is set to false.
So I am therefore not sure where "No Value" would come from? With the unbound text box I put in place to give me a a "Yes" or "No" based on it being true or false is working so I'm confused by what you mean around the "No Value".

 

[Minty]

The error you described meant that the value of the checkbox was being returned as Null at the time the code was called.

In your code try adding the following;

Debug.Print "Status " & Nz(Me.chkStatus,"Null")

Me.cmboDASStatus.Enabled = Me.chkStatus

If you get the same error when you open the debug window in the immediate pane you should see what the database thinks your checkbox was.
Make sure that the check box has it's Triple State property set to No.

Finally make sure that the control focus is NOT set to cmboDASStatus, as you can't disable a control whilst it has focus.

 

[MattioMatt]

Minty,

Seem's you're spot on. I got the same run time error and in the immediate window it says "Status Null".

I'm not sure how or why it is happening as I can see check/no check in the table/query on which the form is based on.
The only other thought I've had is that the data in this table came from a spreadsheet in which the column was either Y, N or blank. The upload wouldn't work with the field being set as a checkbox so based on another thread in this forum I changed it to short text and back to Y/N post the successful upload so unless that has had something to do with it..

 

[Minty]

It probably has. Run a simple select query on your data, and set the criteria for the field to Is Null and see what records it returns. If you get some results then update the values accordingly to get rid of the nulls.

Make sure the underlying table is set to have a default value and that any forms that would allow data entry also have that value coming through.

 

[MattioMatt]

It probably has. Run a simple select query on your data, and set the criteria for the field to Is Null and see what records it returns. If you get some results then update the values accordingly to get rid of the nulls.

Make sure the underlying table is set to have a default value and that any forms that would allow data entry also have that value coming through.

Thanks Minty, I've run a select query and it hasn't returned any results which makes it even more strange! I was expecting it to return some results based on the previous activity and finding that the debug message was showing the status to be Null.

It returns appropriate results if I change the criteria from Is Null to either True or False.

 

[Minty]

Okay that's weird. Are you certain the controls control source is set correctly? And that you are referring to the right control name?

I noticed you mentioned locked in one post but enabled in another, they aren't the same property?

Is there any other code being run - maybe post it up?

Can you upload the offending form and some sample data in a stripped down version

 

[MattioMatt]

Hi Minty,

I think I've found the problem, and of course it is something that I should have spotted long before now but hadn't.
It works fine with your code in the On Current even if the form is showing a current record. Where it doesn't work is when the form is for adding a new record (I have button to open this form in add mode) and this is where I've been checking to see if it is working.

Of course it hasn't been working because there is no record for it to base the if statement on.
I think the reason I didn't see this sooner is because in the query behind the form I've added the chkStatus from another table that is linked to another table with the SO_ID. When the form opens it pulls the SO_ID and thus the chkStatus even through a record hasn't been created yet, therefore it is pre-populating it if that makes sense?

I guess the only way around this would be to use a Dlookup as opposed to what I am doing, but then I think I'd have the same problem?
Is there anyway of doing this on a form for entering a new record?

 

[Minty]

You can check if you are on a new record in the on current event and adjust the result accordingly.

If Me.NewRecord Then ....

 

[MattioMatt]

You can check if you are on a new record in the on current event and adjust the result accordingly.

If Me.NewRecord Then ....

When you say adjust the result accordingly, could you just go into what you mean?

 

[Minty]

If it's a new record do you want the combo enabled or not? Lets say you do;

Private Sub Form_Current()

      [COLOR="green"]         ' Check if we are on a new record [/COLOR]
       If Me.NewRecord Then
                Me.cmboDASStatus.Enabled = True
                Exit Sub          [COLOR="Green"]'We're done so exit the code here[/COLOR]
       End If
               [COLOR="green"]     ' We will only get to this if it's not a new record[/COLOR]
       Me.cmboDASStatus.Enabled = Me.chkStatus

End Sub