Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152

    jzwp11 Code - Visible/Hide fields


    Hello.

    I have the following code which works great (with the help of jzwp11).
    However I want to hide fields upon two options in the combo box. hence the same code but two different Combobox IFs.
    I have tried to use one lot of code and saying "None" or "Instantaneous from main boiler" but it debugs. But this one at the minute only works when you select "Instantaneous from boiler option not "None"

    Code:
    If Me.WaterHeatingSystem = "None" Then
        Me.WaterHeatingSystemInstallYear.Visible = False
        Me.WaterHeatingSystemRenewYear.Visible = False
        Me.WaterHeatingControls.Visible = False
        Me.Hotwatercylindersize.Visible = False
        Me.Hotwatercylinderinsulation.Visible = False
        Me.Coldwaterstoragetank.Visible = False
        Me.Heatingheadertank.Visible = False
        Me.whsPlease.Visible = True
    Else
        Me.WaterHeatingSystemInstallYear.Visible = True
        Me.WaterHeatingSystemRenewYear.Visible = True
        Me.WaterHeatingControls.Visible = True
        Me.Hotwatercylindersize.Visible = True
        Me.Hotwatercylinderinsulation.Visible = True
        Me.Coldwaterstoragetank.Visible = True
        Me.Heatingheadertank.Visible = True
        Me.whsPlease.Visible = False
    End If
    If Me.NewRecord Then
        Me.WaterHeatingSystemInstallYear.Visible = False
        Me.WaterHeatingSystemRenewYear.Visible = False
        Me.WaterHeatingControls.Visible = False
        Me.Hotwatercylindersize.Visible = False
        Me.Hotwatercylinderinsulation.Visible = False
        Me.Coldwaterstoragetank.Visible = False
        Me.Heatingheadertank.Visible = False
        Me.whsPlease.Visible = True
    End If
    If Me.WaterHeatingSystem = "Instantaneous from main boiler" Then
        Me.WaterHeatingSystemInstallYear.Visible = False
        Me.WaterHeatingSystemRenewYear.Visible = False
        Me.WaterHeatingControls.Visible = False
        Me.Hotwatercylindersize.Visible = False
        Me.Hotwatercylinderinsulation.Visible = False
        Me.Coldwaterstoragetank.Visible = False
        Me.Heatingheadertank.Visible = False
    Else
        Me.WaterHeatingSystemInstallYear.Visible = True
        Me.WaterHeatingSystemRenewYear.Visible = True
        Me.WaterHeatingControls.Visible = True
        Me.Hotwatercylindersize.Visible = True
        Me.Hotwatercylinderinsulation.Visible = True
        Me.Coldwaterstoragetank.Visible = True
        Me.Heatingheadertank.Visible = True
    End If
    If Me.NewRecord Then
        Me.WaterHeatingSystemInstallYear.Visible = False
        Me.WaterHeatingSystemRenewYear.Visible = False
        Me.WaterHeatingControls.Visible = False
        Me.Hotwatercylindersize.Visible = False
        Me.Hotwatercylinderinsulation.Visible = False
        Me.Coldwaterstoragetank.Visible = False
        Me.Heatingheadertank.Visible = False
    End If

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Did you try this?


    Code:
    IF Me.WaterHeatingSystem = "None" OR  Me.WaterHeatingSystem = "Instantaneous from main boiler" THEN
        Me.WaterHeatingSystemInstallYear.Visible = False
        Me.WaterHeatingSystemRenewYear.Visible = False
        Me.WaterHeatingControls.Visible = False
        Me.Hotwatercylindersize.Visible = False
        Me.Hotwatercylinderinsulation.Visible = False
        Me.Coldwaterstoragetank.Visible = False
        Me.Heatingheadertank.Visible = False
        Me.whsPlease.Visible = True
    Else
        Me.WaterHeatingSystemInstallYear.Visible = True
        Me.WaterHeatingSystemRenewYear.Visible = True
        Me.WaterHeatingControls.Visible = True
        Me.Hotwatercylindersize.Visible = True
        Me.Hotwatercylinderinsulation.Visible = True
        Me.Coldwaterstoragetank.Visible = True
        Me.Heatingheadertank.Visible = True
        Me.whsPlease.Visible = False
    End If

  3. #3
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Just tried that one no luck!

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What did or did not happen when you tried it?

    I assume that you are still using this code in the on current event of the form, correct?

  5. #5
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by jzwp11 View Post
    What did or did not happen when you tried it?

    I assume that you are still using this code in the on current event of the form, correct?
    Yeah I am still copying the codes exactly where they were placed.
    Just changing the combobox and fields names to suit.

    The code you gave me only works when None is selected not for the other option (nothing comes up or debugs, just fields stay visible)

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The on current event of the form applies as you are viewing existing records. So as you scroll through records the controls should hide or be visible depending on what was selected in the combo box when the record was created or last change. The on current event of the form will not execute when a value is changed or selected in the combo box for the current record on which you are working. If you want the controls' visible property to change in response to a change in the combo box, you must ALSO change the code in the After Update of the combo box.

    Additionally, if you want controls visible/hidden when the current record is also a new record, then you need to update the code in that section of the on current event that starts with:If Me.NewRecord Then

    So in other words, you will have to change the code in 3 different areas (two areas within the on current event of the form and in the after update event of the combo box).

  7. #7
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by jzwp11 View Post
    The on current event of the form applies as you are viewing existing records. So as you scroll through records the controls should hide or be visible depending on what was selected in the combo box when the record was created or last change. The on current event of the form will not execute when a value is changed or selected in the combo box for the current record on which you are working. If you want the controls' visible property to change in response to a change in the combo box, you must ALSO change the code in the After Update of the combo box.

    Additionally, if you want controls visible/hidden when the current record is also a new record, then you need to update the code in that section of the on current event that starts with:If Me.NewRecord Then

    So in other words, you will have to change the code in 3 different areas (two areas within the on current event of the form and in the after update event of the combo box).
    Not sure I understand.
    I changed this code also on the Afterupdate part, but still not having any luck?

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Can you post a copy of your database (with any sensitive data removed)?

  9. #9
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by jzwp11 View Post
    Can you post a copy of your database (with any sensitive data removed)?
    Attached - But I have removed all records (i.e. All Address and tenant data, also network address')

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I did not see an attachment.

  11. #11
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Sorry now attached! Didn't realise it doesnt allow .rar

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'll have to take a look at the database tonight since I only have Access 2003 here at work.

  13. #13
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You didn't mention which form, so I assumed you meant the internal survey form.

    I noticed that you had: "Instantaneous from main boiler" in your code snippet below, but that is not an option in your spaceheatingsystem combo box; it is, however, an option in the waterheatingsystem combo box, so VBA would ignore the section after the OR in the IF..THEN below.

    If Me.SpaceHeatingSystem = "None" Or Me.SpaceHeatingSystem = "Instantaneous from main boiler" Then

    Also, I would recommend having all of the me.newrecord related commands all within the one IF me.newrecord...THEN...ELSE...END IF rather than having them scatter in between other sections of the code.

    I tried to clean up stuff as best I could for the water heating system stuff. The modified DB is attached.

  14. #14
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Thanks jzwp11!
    Lol o yeah that bit of code should not have been in that section lol.
    Is there a way to make all the fields appear on open though?

    Thanks - Sorted. I just changed all the new records to "True"

  15. #15
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The way the code in the on current event of the form is set up now where you have code preceeding the IF me.newrecord... is not ideal. Some of that is partly my fault since I didn't realize the large number of controls on your form. Basically all of the code has to be embedded within the IF me.newrecord THEN..ELSE....END IF. All of the code that currently resides outside of the IF me.newrecord THEN...ELSE...END IF statement must be copied to the section between the ELSE and END IF


    IF me.newrecord THEN
    statments to make all controls visible

    ELSE

    statements that make controls visible or not when the current record is an existing record and certain values have been selected in the associated combo boxes

    END IF

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-06-2011, 04:38 AM
  2. Report showing non-visible fields
    By AKQTS in forum Forms
    Replies: 2
    Last Post: 09-21-2010, 09:40 AM
  3. Setting fields visible/invisible
    By col5473 in forum Forms
    Replies: 1
    Last Post: 09-17-2010, 09:14 AM
  4. Cannot hide fields in a datasheet
    By desgordon in forum Forms
    Replies: 2
    Last Post: 08-19-2009, 03:24 PM
  5. how to hide the fields in subform
    By tinytree in forum Forms
    Replies: 3
    Last Post: 05-01-2009, 07:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums