Results 1 to 5 of 5
  1. #1
    hnkford is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4

    Control visibility of two report fields based on criteria of one - VBA

    I am trying to create a report where two fields are hidden or visible based on the criteria of one. FundingStatus is either empty or contains the term "No Funding" based on a query. (If it matters the query uses a calculated date to determine if the field has "No Funding", i.e, today's date would be "No Funding"). If the field contains "No Funding" then FundingStatus should be visible and ProjectedFundEndDate should be invisible. If FundingStatus is empty then FundingStatus should be invisible and ProjectedFundEndDate should be visible. I have written the VBA code in Report Load multiple ways and below is the closest I have come to getting something to work. The code below returns FundingStatus as either true or false correctly but always shows ProjectedFundEndDate as false. I would apprecaite any guidance you can offer to get this to work





    Private Sub Report_Load()


    If FundingStatus = "No Funding" Then
    ProjectedFundEndDate.Visible = False
    FundingStatus.Visible = True
    ElseIf FundingStatus <> "No Funding" Then
    FudningStatus.Visible = False
    Else
    ProjectFundedEndDate.Visible = True


    End If






    End Sub


    Thank you,

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Note the spelling mistake:
    Private Sub Report_Load()




    If FundingStatus = "No Funding" Then
    ProjectedFundEndDate.Visible = False
    FundingStatus.Visible = True
    ElseIf FundingStatus <> "No Funding" Then
    FudningStatus.Visible = False
    Else
    ProjectFundedEndDate.Visible = True




    End If
    Try this in the Open event of the report:
    Code:
    If Me.FundingStatus = "No Funding" Then
          Me.ProjectedFundEndDate.Visible = False
          Me.FundingStatus.Visible = True
    Else
         Me.FundingStatus.Visible = False
         Me.ProjectFundedEndDate.Visible = True
    End If
    This should work if your report only has one record, if multiple try to move the code to the Format event of the appropriate report section that hold the controls (any header or footer or the detail section).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    hnkford is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2011
    Posts
    4
    Hello,

    That code returns FundingStatus as true or false as requried but ProjectedFundEndDate is return as always true. I'm going to see if I can use conditioanl formatting as a work around.

    Thank you for your help

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just notice you had another spelling mistake on that control, if you put Option Explicit at the top of the module is should catch all these:
    If Me.FundingStatus = "No Funding" Then
    Me.ProjectedFundEndDate.Visible = False
    Me.FundingStatus.Visible = True
    Else
    Me.FundingStatus.Visible = False
    Me.ProjectFundedEndDate.Visible = True
    End If
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Conditional Formatting won't set visibility. Could set forecolor same as backcolor.

    Could just have IIf() expression in textbox that returns Null when condition met.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-31-2020, 04:20 AM
  2. Replies: 1
    Last Post: 09-20-2019, 08:00 AM
  3. Replies: 5
    Last Post: 01-21-2018, 02:34 PM
  4. Replies: 8
    Last Post: 12-13-2016, 02:11 PM
  5. Replies: 3
    Last Post: 01-13-2011, 03:53 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