Show/Hide a control based on whether or not 4 other controls have contents

[brucemc777]

Thank you for considering my confusion!

I can't believe this is so hard, so that probably means there is a very simple solution i am too tunnel-focused to see. I am re-learning Access after a long time away, living in the land of Excel for the past decade or so.

In the simplest example, let's say we have our form frmForm. On it are five controls - four textboxes bound to a table and one command button. Each time a textbox is updated or changed i want to check the other three. If all four have contents; i.e., <>"" (or <> vbNullString if Access uses that also, IsNull does not work for this) i want to make the command button visible, but if any are blank, then not visible.

To check the text property of each would be ideal, but i get an error message that i cannot check that property unless that control has focus, and it seems a little absurd for me to write a routine that sets focus on each control, checks it, then moves on to the next just to accomplish this - not that it is hard to do, but i've got to be missing something obvious here...

Thank you for all your help!

 

[CJ_London]

Just check the control rather than the text property

Btn.visible = len(nz(ctl1))<>0 and len(nz(ctl2))<>0 etc

Would need to put this code in the after update of each control plus the current event

Or create a function and call the function from each of those events

 

[The_Doc_Man]

Just to explain, .Text is the property that shows you the contents of a text-oriented control (Text Box, Combo Box, etc) while you are actually entering data into it one character at a time. When you TAB out or hit ENTER or click on another box, the behind-the-scenes operation evaluates and stores the .TEXT input into the control's .VALUE property. When you no longer have focus on the control in question, there IS no .TEXT property. This is because it is a dynamic property that can appear or disappear as needed. For a related example, if you have an unbound text box then it has no .OLDVALUE property because it doesn't get a "previous" value when the form's _Current event occurs. Access has LOTS of properties that are sensitive to the context in which they are being used.

As to deciding when to make the button visible, that is (in a sense) up to you. If you want people to be able to enter data in random order for the four controls (i.e. don't care which one you fill in last), then you have a tricky problem. You need to run that test for the four non-empty text boxes once for each text box. CJ's code suggestion IS correct. The question is where you put it. CJ suggests the control.AfterUpdate event, and I don't disagree, though I have also profitably used the control.LostFocus event. (There are those who disagree with that choice.) But if you want the command button to become visible as soon as all four controls are filled AND they can be filled in any order, then you need to test that condition once for each text box and once for the form's _Current event. Which is why I might make a subroutine to perform this test and call it from the four different text box controls and once more from the form _Current event.

 

[brucemc777]

Just check the control rather than the text property

Btn.visible = len(nz(ctl1))<>0 and len(nz(ctl2))<>0 etc

Would need to put this code in the after update of each control plus the current event

Or create a function and call the function from each of those events

VERY NICE!

Thank you,
-Bruce

 

[brucemc777]

@The_Doc_Man: Many thanks for the education! I had thought Text was simply a property of a text-oriented control prior to the text being written to a record, after which it became a "value". I truly appreciate learning what it actually is.

Thank you,
-Bruce