Results 1 to 7 of 7
  1. #1
    jaystewart86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2

    Hiding Empty Cells in Form


    Hi everyone Thanks so much for the help.

    I'm looking to see if anyone can help me w/ hiding cells that are empty. I'm creating a new database for my job and well we are needing to pull some data for a Ledger Card, But not everyone has the same information i.e some fields are blank and some will have data etc. Attached is the form.
    Attached Thumbnails Attached Thumbnails Capture.jpg  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    why hide them as opposed to disabling them? I think the appearance your form would take based on visible/not visible would look bizarre in some cases.
    If that doesn't help, provide more info around the question. Not knowing if data input is still required in this situation, I hesitate to recommend a report instead, which might provide the look you need, but is not useful if this isn't about paper forms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jaystewart86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2
    Hi Micron,

    Thanks for the reply.

    So the ledger card form is used to call which month a student has paid. In the sample, i have attached I'd only want the cells up to 6/17 to show and the rest of the year up to this month no-show. and or flipped if a student is only been paying for let's say 5 months. and started on 10/17 I only want those cells that have payment amounts to show.Click image for larger version. 

Name:	Capture.jpg 
Views:	15 
Size:	59.4 KB 
ID:	32211

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So this is based on whether or not a field has data in it, and you don't need to "unhide" a control to add/edit data?
    Then you'll need to loop through all the textboxes in the form load event (the Open event occurs first, but has no access to data). I would put this into a standard module (not form/report module) because you'll likely need it many times going forward (you could then call it from anywhere in the db):
    Code:
    Public Function IsNullEmpty (ctl as Control) As Boolean
    IsNullEmpty = false
    If IsNull(ctl) or ctl = "" then IsNullEmpty = True
    End Function
    The in your form load event, something like

    Code:
    Dim ctl as Control
    For each ctl in me.controls
     If ctl.Type = acTextbox And IsNullEmpty = True then ctl.visible = false
    Next
    You probably don't really need = True, but it's more obvious as to the intent if I include it. Also, the function doesn't deal with a user having entered only spaces into the control. I've been meaning to modify it for that purpose, but haven't gotten around to it. In your case, it seems irrelevant.
    EDIT: It's ControlType, not just Type.
    As for spaces, if that's all the control contains, Access will remove them when the control loses the focus, so they seem to be irrelevant. It's the possibility of empty strings that one might need to worry about.
    Last edited by Micron; 01-17-2018 at 02:22 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I agree with Micron on it looking bizarre if hidden if there is no value. What about if the value is null then set the fill color to a light gray and set the text box to locked.

    Code:
    Private Sub txtFieldName_BeforeUpdate()
        Dim lngGray As Long 
        If IsNull(Me!txtFieldName.Value) Then 
            Me!txtFieldName.Locked = True
            lngGray = RGB(211, 211, 211)
        Else
            something
        End If
    End Sub

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The form appears to be non-normalized data structure. Does this reflect the actual data structure? Do you have a field for each month? You show 3 years worth. That is 36 fields. How many years do you want this db to cover? Table has a limit of 255 fields.

    Conditional Formatting can control Enabled state of textbox and combobox controls. The current month and year could be criteria.
    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.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by jaystewart86 View Post

    ...I'd only want the cells up to 6/17 to show and the rest of the year up to this month no-show...
    If the Control for 7/17 isn't Visible, how do you enter the data for it when the time come?

    June7 has hit the nail on the head...your data is non-normalized.

    You should be using two Tables, here, with Fields something like this:.

    Student Table
    Student
    Start Date
    Contract (presumably a unique Field, and probably the Primary Key)
    Balance

    Payments Table
    Month/Year
    Amount Paid
    Contract Field (as the Foreign Key)

    The Student Table would be used as the RecordSource for a Main Form, and the Payments Table as the RecordSource for a Subform, with the Contract Fields being used to connect the Main Form/Subform.

    There would only be Records, in the Subform, for months when payments had been made, and you could add news payments as needed.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. need help on hiding empty space on report
    By nishant.dhruve in forum Access
    Replies: 2
    Last Post: 08-18-2016, 11:06 AM
  2. Replies: 7
    Last Post: 07-15-2015, 03:42 PM
  3. Replies: 6
    Last Post: 05-07-2014, 11:15 AM
  4. Hiding an Empty Sub-report
    By ophirw in forum Reports
    Replies: 16
    Last Post: 10-04-2011, 09:37 AM
  5. Hiding fields that contains empty records
    By sakthivels in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 07:06 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