ENABLE DISABLE CONTROLS IN A FORM

[DOMINIC KENBUNGO]

Hi all. A form is designed with button disabled using the form's ONLOAD EVENT. I need to ENABLE this button using the AGE value computed in textbox TEXT11 if the age value is between 18 and 35. I have tried this but it doesn't work.

Private Sub Form_Current()
If Text11.Value < 18 Then
Command14.Enabled = False
Else
Command14.Enabled = True
End If
End Sub

Private Sub Form_Load()
Command14.Enabled = False
End Sub​

The database is attached here.

 

[The_Doc_Man]

First, an alternative formula:

Command14.Enabled = CLng( Text11 ) BETWEEN 18 and 35

That has the same effect as your verbal description and in fact does more than the code you showed us, because that code doesn't test for 35.

Second, what error are you seeing? OR what does it do that you don't want it to do? OR what does it NOT do that you want it to do? When I opened it I didn't see anything particularly wrong. Saying "It doesn't work" is virtually the same as going to the doctor and saying "I don't feel well" but then giving him no other details like where it hurts or what are your symptoms.

Third, a suggestion (not a critique): In the long run it is easier to remember what is going on if you name your controls with something more meaningful that Text11 or Command14. It is NOT wrong - but in the long term, maintenance is easier if the names are meaningful.

 

[gemma-the-husky]

What format is text11. (In the properties for the control)

if the format is blank, it will most likely be treated as text, so the test (note that you don't need the .value)

If Text11 < 18 Then will fail testing for a number. With text, you would need
If Text11.Value < "18" Then but really I imagine you actually want text11 to be formatted as a number. (maybe Format Fixed, Zero decimals)

This is similar to the observations @The_Doc_Man just made.
In reality, you don't want to be soring numbers as text, and then converting them to numbers. If "Age" is a number, then have it treated it as a number consistently, and then you will pick up as errors values that don't represent numbers.

 

[DOMINIC KENBUNGO]

What format is text11. (In the properties for the control)

if the format is blank, it will most likely be treated as text, so the test (note that you don't need the .value)

If Text11 < 18 Then will fail testing for a number. With text, you would need
If Text11.Value < "18" Then but really I imagine you actually want text11 to be formatted as a number. (maybe Format Fixed, Zero decimals)

This is similar to the observations @The_Doc_Man just made.
In reality, you don't want to be soring numbers as text, and then converting them to numbers. If "Age" is a number, then have it treated it as a number consistently, and then you will pick up as errors values that don't represent numbers.

First, an alternative formula:

Command14.Enabled = CLng( Text11 ) BETWEEN 18 and 35

That has the same effect as your verbal description and in fact does more than the code you showed us, because that code doesn't test for 35.

Second, what error are you seeing? OR what does it do that you don't want it to do? OR what does it NOT do that you want it to do? When I opened it I didn't see anything particularly wrong. Saying "It doesn't work" is virtually the same as going to the doctor and saying "I don't feel well" but then giving him no other details like where it hurts or what are your symptoms.

Third, a suggestion (not a critique): In the long run it is easier to remember what is going on if you name your controls with something more meaningful that Text11 or Command14. It is NOT wrong - but in the long term, maintenance is easier if the names are meaningful.

Hallo,
Thanks for reply.
1. We are intending to toggle command14 either enable or disabled depending on the result computed and displayed in text11.
2. text11 is computed using the expression =DateDiff("yyyy",[DOB],DateAdd("m",6,Date())) which returns an integer value.
3. I have tried migrating my code from the OnCurrent event of the form to the OnClick event of text11. It works fine though I still want the action in (1) to take place when the value in text11 changes without necessarily clicking in the textbox (text11). What is your suggestion?

The code:
Private Sub Form_Load()
Command14.Enabled = False
End Sub

Private Sub Text11_Click()
If Text11.Value < 18 Or Text11.Value > 35 Then
Command14.Enabled = False
Else
Command14.Enabled = True
End If
End Sub

 

[Rene vK]

Text11_AfterUpdate?

 

[The_Doc_Man]

@Rene vK - that works as one way to catch what is desired.

@DOMINIC KENBUNGO - Here is an article about events on a form that are control-specific.

Order of events for database objects - Microsoft Support

support.microsoft.com

There is a .Change event, but that fires every time you add or remove a single character, so there is a lot of overhead. My own favorite is based on the idea that while the control has focus, you don't want any more overhead than necessary, so I prefer the .LostFocus event for a control. It fires as the control is losing focus, and that means the content of that control is becoming stable. If you click out of the text box, you implicitly trigger a

The other thing is that you are working too hard.

If Text11.Value < 18 Or Text11.Value > 35 Then
    Command14.Enabled = False
 Else
    Command14.Enabled = True
End If

can be replace with Command14.Enabled = CLng( Text11 ) BETWEEN 18 AND 35

First, you don't need .Value hardly ever in that situation because the default property for a textbox IS the .Value property.

Second, you don't need an IF/THEN/ELSE/END IF when a simple Boolean expression will return the true/false value you want and it is (in my opinion) easier to read.

 

[DOMINIC KENBUNGO]

Hallo,
Thanks for reply. On trying to implement your suggestion get this error:

What is to be done in this case.

 

[arnelgp]

see also this

 

[Rene vK]

@The_Doc_Man I put in the question mark because it is a bit unclear to me if Text11 ever gets focus. I cannot figure out the sequence of events and what goes on after opening the form. The other thing for the question mark was see if OP is figuring out his programming. (i didn't open his file)

 

[The_Doc_Man]

Hallo,
Thanks for reply. On trying to implement your suggestion get this error:


View attachment 88767

What is to be done in this case.

Here is a link to the BETWEEN operator. By any chance does the content of Text11 contain quoting characters or parentheses?

Between…And operator

Office VBA reference topic

docs.microsoft.com

Here is a link to type conversions.

Type conversion functions (VBA)

Office VBA reference topic

docs.microsoft.com

I see nothing wrong other than that I can't see what (if anything) comes behind the "35" as your error box is in the way.

However, please clarify something. As noted by Rene vK, it is possible that you never actually get focus on Text11 because it is computed. If so, then NO control-related events would occur for it. The "Click" event makes no sense in this context for a purely computed control. We need to find a usable event that WILL occur, which could be Form_Current. Your control would "blink" once (very quickly) because of doing it this way.

Since you have defined the control as the result of a computation, you have to pick an event where the content of the controls has already been computed, and there is no guarantee for Form_Open or Form_Load to do what you want. If the result of the computation is correct, there is no reason to ever click the control. So there is something deeper going on here that needs attention. I'm just not sure what it is at the moment. In any case, I don't know what is wrong with that line unless there is a hidden (non-printing) character involved.

 

[The_Doc_Man]

@The_Doc_Man I put in the question mark because it is a bit unclear to me if Text11 ever gets focus. I cannot figure out the sequence of events and what goes on after opening the form. The other thing for the question mark was see if OP is figuring out his programming. (i didn't open his file)

An interesting point. On re-reading the question, I realize you could be quite right - that the person never gives focus to that control, since it is computed.

 

[gemma-the-husky]

2. text11 is computed using the expression =DateDiff("yyyy",[DOB],DateAdd("m",6,Date())) which returns an integer value.

Yes, but if text11 is not formatted as a numeric control, the contents will be a string, and testing

if text11 = 18 will either error or fail, since text11 will be the string "18" and not the number 18

 

[DOMINIC KENBUNGO]

see also this

Thanks. This solves my problem.
How can this code be rewritten so that the control is only enabled if text11 has values ranging 18 to 35. OTHERWISE THE COMMAND BUTTON REMAINS DISABLED.

 

[DOMINIC KENBUNGO]

see also this

Thanks. This work fine. Kindly rewrite the code if the command is enabled if text11 value ranges from 18-35. Outside this range the command remains DISABLED.

 

[The_Doc_Man]

The problem isn't that the code needs to be rewritten. It needs to be placed in an event that is guaranteed to fire when the form displays that record. If you leave the Text11 computation exactly as-is, but move that test to the Form_Current event routine, you should get your desired result. The trick is that if you never click on Text11, you never get a click event. Tabbing through it does not cause a click event to fire. This didn't work because the code was in the wrong event slot.

 

[Pat Hartman]

FYI,
Reference controls using Me.controlname, NOT controlname.Value
The Me. tells Access that the control is defined in the current class module and that saves the interpreter from looking through multiple modules to find the definition. It also gives you intellisense.

ALWAYS give your controls meaningful names IMMEDIATELY after creating them. Do it BEFORE you write code or add event procedures. Doing it now, which you should still do, will be annoying because you have to keep track of the old name because as soon as you rename the control, you will loose the association with any existing event procedures.

 

[arnelgp]

here is another try

 

[DOMINIC KENBUNGO]

here is another try

Thanks. This solves my database question.