Full Version: Hide Or Show Fields In Form Based On Field In Same Record
UtterAccess Forums > Microsoft® Access > Access Forms
frankivalli
I am having partial success with this function but this code will only work on the first record....

QUOTE
If me.MaintenanceType.value = "daily" then
me.MaintenanceFrequency.Visible = false
ElseIf Me.MaintenanceType.value = "hourly" then
me.MaintenanceFrequency.Visible = true
End if


I placed this in the onload event of the form and it only works for the first record... Any ideas or direction would be appreciated
doctor9
frankivalli,

I would set this up as a new private subroutine in the form's code module. Then, call the subroutine from the form's Current event as well as the MaintenanceType control's AfterUpdate event. There's no need to call it from the form's Load event. The Current event fires after the form loads it's records, as well as every time the user navigates to a different/new record.

Hope this helps,

Dennis
frankivalli
Thank you doctor!
I am having some trouble referring to my form in a module... What is the proper syntax for this?
doctor9
frankivalli,

If the code is residing in the form's Code Module, you can use "Me." to refer to controls on the form as I've done in my previous post. In fact, when you type "Me." in the form's code module you should see a list of choices appear as you're typing.

The easiest way to do this is to open your form in Design View, open the Property Sheet, and select the Event tab. Click on the white area to the right of "On Current" and you'll see a small button with three dots on it to the right of where you clicked. Click this button and you'll see a dialog box appear with three options. Select Code Builder and you'll see this in the Visual Basic Editor:

CODE
Private Sub Form_Open(Cancel As Integer)

End Sub


There you would add your code that calls the subroutine that you will place elsewhere in this same module.

If you need this level of advice, I'd strongly recommend picking up a book that introduces you to the basics of Access, either at your bookstore or your library. That will help you get up to speed much more quickly, I think.

Hope this helps,

Dennis
BruceM
frankivalli, the UtterAccess Newcomer's Reading List is another source to help get you started.

A specific question about the code you showed initially is what should happen if MaintenanceType is not one or the other of the two choices. Even if you don't expect that to happen, you may want to decide what to do if it does happen: show MaintenanceFrequency or not. If the default is to show it you could just do:

Me.MaintenanceFrequency.Visible = (Me.MaintenanceType <> "Daily")

In other words, show it if the MaintenanceType is something other than "Daily".

This UA link has a little information about procedures. Since you are looking to do something (show or hide controls) rather than return a value (a math calculation, for instance) you would use a Sub. The link shows a very basic sub, but one that uses an argument (in parentheses if you look at the sub). Most likely you do not need an argument for this sub, so keep the parentheses and put nothing between them. Give the procedure a helpful name such as ShowFreq().

In the Current event and anywhere else it makes sense:

Call ShowFreq

To be clear, I agree with Dennis's observations and suggestions. I'm just adding a few extra details.
frankivalli
I have no issues making the field invisible. The problem occurs when moving from record to record. The first record seems to be affecting all subsequent records. I have tried nested if statements, case statement and no luck.
azolder
QUOTE
I am having some trouble referring to my form in a module... What is the proper syntax for this?
Are you referring to a standard module? There are any number of ways. I prefer passing the form as an argument
CODE
Public Sub YourProcedure(frm as Form, other arguments)
   '<<< your code >>>
   '<<< refer to controls with frm.Control(strControlName) >>>
End Sub
You can call it from your Form module using
CODE
YourProcedure Me, other arguments
BruceM
QUOTE
The first record seems to be affecting all subsequent records.

Is it a continuous form? If so, format changes, including whether or not the control is visible, apply to all records. Conditional formatting provides some options, but cannot be used to set the Visible property.

If it is not a continuous form, what procedure are you using, and how and where are you calling it? John suggested a public procedure, which typically would be in a standard code module. For this situation where the procedure applies to a specific form I would place the procedure in the form's code module and use the Me prefix as you would in any other sub.

Either way you need to call the procedure in the form's Current event and in the After Update event of any controls that contain data that should affect the Visible property.
dmhzx
This is a rehash of what the others have said, but hopefully will help a bit.
First just move your existing code to the form's Current event.

And navigate through the records.
That should solve your 'first record' problem.
If it does, open up you code window for that form again.
Hit Ctrl-End, and after the last end sub
Type in

sub VisHide and press enter.

Paste the code from the form current into the sub VisHide

Go back to your form current code and delete the code.
Type in its place
Call VisHide.

And test navigating through the records.
Once you get that working, go to the after update event of Maintenance type and put in

Call VisHide

That's in essence what you've been guided to do.

If it IS a continuous form, then it IS going to look unsightly, BUT you will see that the visible/hide changes on ALL the records in the display to match whichever record is selected. So it will all actually WORK ok
frankivalli
Thank you for all the help. Placing the code in the on-current event and using a single form seems to have done the trick.
dmhzx
It should be OK until someone changes the maintenance type or adds a new record.
Then you need to revisit.
doctor9
That is why I recommended creating a separate subroutine and calling that subroutine from both the control's AfterUpdate and the form's Current events.

Dennis
dmhzx
And why I added a 'how to do it' to your suggestion Dennis: thumbup.gif
azolder
Back to Post 1:
CODE
If me.MaintenanceType.value = "daily" then
   me.MaintenanceFrequency.Visible = false
ElseIf Me.MaintenanceType.value = "hourly" then
   me.MaintenanceFrequency.Visible = true
End if

Are you wanting to set the control's property for each record separately? If so move the code to the Form's On Current event.
CODE
Private Sub Form_OnCurrent()
   If me.MaintenanceType.value = "daily" then
      me.MaintenanceFrequency.Visible = false
   ElseIf Me.MaintenanceType.value = "hourly" then
      me.MaintenanceFrequency.Visible = true
   End if
End Sub
The form's Open and OnLoad events only occur once as the form is starting up. The OnCurrent event occurs as the form's focus moves from one record to the next.

Another question I have: Are 'hourly' and 'daily' are the only values MaintenanceType can have? If so then you can simplify the expression:
CODE
Private Sub Form_OnCurrent()
   Me.MaintenanceFrequency.Visible = (Me.MaintenanceType.Value = "hourly")
End Sub
If MaintenanceType can take more than two values consider using a Select Case statement.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.