Make Control Visible Depending on Value of Another Field

[JChase]

Hi all.

I'm relatively new to access and not a programmer.

I have a database of forest pest species, with a main "Search" form that operates quite nicely as a dashboard for the whole database (there's a separate data entry form that works well as well).

On the main search form, I'd like to have certain controls visible/invisible depending on values of other fields. Two cases in particular are:

1) make a Label visible only if a Checkbox is checked. (in the attached screenshot, I'd like to get the "Invasive" label to disappear if the checkbox next to it is not checked (would even be nice to have the checkbox itself disappear)). Remember - this is just search form that's not editable, so the checkbox will still be visible on my data entry form.

2) make a text box ("Order") invisible if value in a separate text box ("Pest Category") = "Pathogen". I tried to accomplish this one using the following code in the After Update for the "Pest Category" text box:

Private Sub Text67_AfterUpdate()
If Me.Text67 = "Pathogen" Then
Me.Text138.Visible = False
Else
Me.Text138.Visible = True
End Sub

It does nothing... again, I'm not a programmer.

I appreciate any feedback! Also, if anyone can recommend a good beginner guide to MS Access coding (VBA or SQL) that would be great!

Cheers.

Justin

 

[JChase]

My Apologies. See attachment for screenshot.

Justin

 

[NauticalGent]

Good morning,

I typed a response to this yesterday, but in the process I accidentally hit the “undo” button on my @#$&! IPad, which is directly below the comma. I got frustrated and decided to go to bed because I had an early flight and I figured SOMEONE would surely respond...

Seeing that this one has slipped through the cracks, I will attempt a solution.

In my opinion, using the AfterUpdare on your control (textbox) is the right way to go. And I think your syntax is correct, however, I do not have access to Access while I am here in the states so I cannot test this for you.

The only difference I could suggest would be:

If Me.Text67[COLOR="Red"].Value[/COLOR] = "Pathogen" Then

...but your original code SHOULD work.

A word to the wise: Avoid using terms like

It does nothing...

when asking for assistance on these type of forums. It doesn’t give anyone attempting to offer assistance anything to go on and leads to additional questions and frustration on both parts. There are a few sticky’s on this forum regarding getting your answers quicker. One member, Splikel I beleive even had something to this effect on his/her signature. I try to keep it in mind while posting questions!

Now that I have mildly slapped you hand, let me offer an idea as to why this may not be working. For the AfterUpdate event to fire, the control’s value would have had to have been changed for its original value AND the control would have had to been exited (either with the tab key, mouse click elsewhere on the form, etc).

If you are in fact doing these things and your still not getting the results you are looking for, reply with some more details and I am sure we can help you get it sorted out.

Best of luck...

 

[Minty]

You may need to add the same code to the Current event of the form to make it fire when you search to a different record ?

 

[Gasman]

For reference try
https://books.google.co.uk/books?id=90a4cBCnyhMC&pg=PA382&lpg=PA382&dq=change+button+text+default+access+2007+button&source=bl&ots=lMSHcOj7O8&sig=uy1N2W5SrE0LuKJP9qdIf0Emea0&hl=en&sa=X&ved=0ahUKEwjB1YvYrMDWAhWDI1AKHRFxCYkQ6AEIWjAH#v=onepage&q=change%20button%20text%20default%20access%202007%20button&f=false

As a novice myself, you might want to start giving the controls meaningful names, as when you come back to it after a while Text138 etc is going to mean very little.?

 

[NauticalGent]

Further to what Gasman has suggested, the most valuable book regarding Access VBA I have purchased (and I have purchased a few) is Access 2010 VBA Inside and Out by Andrew Couch: https://www.amazon.com/Microsoft-Access-2010-Programming-Inside-ebook/dp/B00JDMPGAA

I have a PDF version of it on my computer at work and have used it almost on a daily basis. It covers everything from debugging tips to advanced topics like Class Modules and SQL Server upsizing. It assumes you have mastered Access Basics(which I have not) but it is still easy to follow and offers hundreds of coding examples.

If reading is not your thing, there is a series of YouTube videos you can try. Two of my favorites are by Steve Bishop and codekabinett.com (Philipp Stiefel). Steve has over 100 videos that takes you from cradle to grave while Phil sticks to good coding practices. Good stuff...

And finally for Gasman...you are far from a novice but your humility is second to none!

 

[Gasman]

And finally for Gasman...you are far from a novice but your humility is second to none!

Why thank you NauticalGent, nice of you to say so, praise indeed.
However I do really consider myself still a novice, perhaps just not a basic novice anymore.?

I have learnt a lot from the people on this forum and find myself constantly coming back with more questions as the problems/challenges get a little harder and I get a little more adventurous with Access.

 

[JChase]

Wow, thanks for all the help, folks!

So, NauticalGent's first suggestion (adding ".Value") to my AfterUpdate expression worked! It makes complete sense too, and I'll definitely be able to use that for other things.

I appreciate the hand slap as well, since you were polite about it!

Naples, Italy?! So jealous!

I'll check out those books as well, guys. Thanks!

Minty, I've made a note to self to consider your comment on the Current Event. I'll get back to you if this proves useful.

Thanks again all!

Justin
Fredericton, NB, Canada (Not Italy)

 

[isladogs]

Surprising that adding .Value made any difference
It's the default 'value' so doesn't normally need to be included in code

 

[NauticalGent]

Surprising that adding .Value made any difference
It's the default 'value' so doesn't normally need to be included in code

I agree Col which is why I put a disclaimer. My money would be that while Justin (OP) was testing, he made no change to the initial text box value and therefore the After Update event didn’t fire (?), but just so happened to make a change AFTER adding .Value to his code and ga e the illusion of making the difference.

Who knows what mischief the Access Gnomes are up to at times...