Microsoft Access: Hiding form fields with Null values

Access VBA
Microsoft Access
Microsoft Access forms
NULL values
Hello, I have a table that has Name F1 F2 F3 F4 Wes x Bob x x Mark x x and so on. Certain names have a check mark "x" and others do not. I need to be able to pull up someones name and see a summary of what they have "x"'s in. I created a Columnar form that has each name as a seperate sheet; listing all of the F1/F2 etc with X's and null values Ie. Wes F1 <Blank box> F2 "x" F3 <Blank Box> F4 <Blank Box> Next sheet has Bob etc The problem is, I don't want to print out a form that has 50 null text boxes. How can I make it so that if an F1/F2 field is null, it won't display itself on the form (or take up space on a printed copy)? I have been playing around with visual basic with the following equation: If IsNull(F2) then me.F2.Visible=False Else me.F2.Visible=True EndIf But when I do this, the form comes up as normal with blank boxes included... As a side note: When I click the empty F2 box, edit it, delete everything (returning it to null), and click outside I get the error "You cannot hide a field that is the focus". I'd appreciate any assistance, thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

Run the report through a query and for the criteria use “is not null” and like “F1″ or F2”. Another thing you can do is use the suppress blank spaces in your report design. Good luck!


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Wesleycrozier
    Thank you JuJu, If I use a query and an "Is not null" criteria, it doesn't show a record if it doen't have an F2 value. That doesn't work for my purposes because I need to see only the fields that have values; just because F2 is null, doesn't mean the entire record has null values. I'm not very experienced with access though, how do I "suppress blank spaces" in the report design? Thank you for your assistance, Wes
    30 pointsBadges:
  • Randym
    I think what you might want is simply to show F1, F4, etc when those are marked with X and not show them when they are not marked. You might create a function where you pass in the values and return one string that has them all stringed together when they have an X in it: Public Function getmarked(F1 As Variant, F2 As Variant, F3 As Variant, F4 As Variant) As String Dim MarkedString As String If F1 = "X" Then MarkedString = MarkedString & "F1, " If F2 = "X" Then MarkedString = MarkedString & "F2, " If F3 = "X" Then MarkedString = MarkedString & "F3, " If F4 = "X" Then MarkedString = MarkedString & "F4, " getmarked = Left(MarkedString, Len(MarkedString) - 2) ' Get rid of the last ", " End Function This function can work for both forms and reports, you just need to have a field's control source set to =GetMarked(F1, F2, F3, F4)
    1,740 pointsBadges:
  • Randym
    I should add that you probably will need to insert this function in a query that the form is based on. Because on a form, any unbound field is applied to all rows
    1,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: