Continuous Forms hide fields per record that contain no data.

Is there a way to hide fields that contain no data per each record of the continuous forms?

I need to display different data types depending on the type of Record it is.

Record types = Attachment, Hyperlink, Note, Contact Name & Phone - each  needs to display only the appropriate fields for the record type.

Any ideas is greatly appreciated>

K
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
You can use a code like this in your forms On Current Event:
Private Sub Form_Current()

If Me.txtRecordType.Value = "attachment" Then
  Me.txtHyperlink.Visible = False
Else
  Me.txtHyperlink = True

End If
End Sub

Open in new window

Just change the name of your text boxes.
Flyster
Karen SchaeferBI ANALYSTAuthor Commented:
are you sure this is suppose to work on each record on a continuous form?
Jeffrey CoachmanMIS LiasonCommented:
Please Define: "appropriate fields"
...with a specific example...
Because you say: Record Types=
...but what you list are DataTypes and fields:
    Attachment, Hyperlink, Note, Contact Name & Phone

So are these record Types or Datatypes?

You also say:
"hide fields that contain no data"
...but then you say:
"display only the appropriate fields"

Again, a clear, graphical example is always helpful.

JeffCoachman
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Karen SchaeferBI ANALYSTAuthor Commented:
sorry Jeff.

I have a dropdown that contains:
RecID   Name
1 = Hyperlink
2=Attachment
3=Notes
4=Contact

Each of these items should allow for whether which field is displayed and I only want those fields that contain data to be displayed.

See my code that almost works in updating the record, but now I would like the form to display the appropriate fields.

Dim n As Integer
   On Error GoTo cboValType_AfterUpdate_Error

n = Me.cboValType.Column(0)

    Select Case n
        Case 1 ' HYPERLINK
            Me.Hyperlink.Visible = True
            Me.Hyperlink.SetFocus
            Me.lblNotes.Visible = False
            Me.Comments.Visible = False
            Me.lblContact.Visible = False
            Me.Comments.Visible = False
            Me.lblPh.Visible = False
            Me.PhoneNumber.Visible = False
            Me.ContactName.Visible = False
        Case 2   'ATTACHMENT
            Me.Hyperlink.Visible = True
            Me.Hyperlink.SetFocus
            Me.lblNotes.Visible = False
            Me.Comments.Visible = False
            Me.PhoneNumber.Visible = False
            Me.lblPh.Visible = False
            Me.lblContact.Visible = False
            Me.ContactName.Visible = False
        Case 3 'NOTES
            Me.Hyperlink.Visible = False
            Me.Comments.Visible = True
            Me.lblNotes.Visible = True
            Me.Comments.SetFocus
            Me.lblPh.Visible = False
            Me.PhoneNumber.Visible = False
            Me.lblContact.Visible = False
            Me.ContactName.Visible = False
        Case 4 ' PHONE CONTACTS
            Me.Hyperlink.Visible = False
            Me.lblNotes.Visible = False
            Me.Comments.Visible = False
            Me.lblContact.Visible = True
            Me.ContactName.Visible = True
            Me.lblPh.Visible = True
            Me.PhoneNumber.Visible = True
            Me.ContactName.SetFocus
        Case Else
    End Select

   On Error GoTo 0
   Exit Sub

cboValType_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboValType_AfterUpdate of VBA Document Form_frmValidatation_MainSub1"
End Sub

Open in new window


Normally I just hide the fields, but due to the form being a continuous form, I need each record to reflect only the applicable fields based on the ValidationRecID Type.

Hope this helps.

K
FlysterCommented:
No it won't. Sorry, forgot about that part.
Jeffrey CoachmanMIS LiasonCommented:
if there are different record "Types", ...with different fields that need to be visible, ...
Then why are they all in the same table?
To me this seems like un-normalized data.
Like having to hide a FirstName field in a Products table...

They should not be in the table if they are never visible for certain record Types.

Another example would be hiding a Gynecologist field for Male Patients.
In reality, "gynecologist" would be listed in the DoctorType table.
Then a Many to Many (tblPatientDoctors) would be used to only show the many doctor "types" for each Patient.
...without the need for "hiding" fields (hiding a Gynecologist field for Male Patients)

With your design there, you would always have to remember to edit this code with each new "Type",...or remember to edit the code if a field needed change its Hide/Show status.

To me this would be better if the different Record Types, were normalize into separate tables...
What you have there seems like an unorthodox design...

...I may not be fully understanding your design though...

As a solution, ...I am guessing that you could do something with a function and conditional formatting (to just make the text color white)
But again this seems like a lot of trouble to go through...

JeffCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Jeff

Welcome to my world, this latest project has really given me some challenges, trying to push Access to do things its is not capable of.  The individual who helped make the tables come from a non-normal background and when I try to explain the need for tighter normalization, it goes on deaf ears.  Also, the client is use to a excel environment and exist that the design work they way they want it.

So I told them I would research the issue, I am pretty sure that they will have to settle for a continuous form that will display empty fields when they are not applicable.

Thanks for all you comments.

K
Karen SchaeferBI ANALYSTAuthor Commented:
for you time, even though there is not really a solution for my project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.