Solved Can I hide controls in a record in Detail View

[JohnPapa]

A simple question, with perhaps a simple answer.

In the detail section of a form I have a checkbox and based on whether this checkbox is checked or not, I would like to show or hide other controls (Textbox, Checkbox etc) on the same record.

Is this possible? When it comes to a textbox I can maybe change the font color to the color of the background and in this way "hide" it, but cannot do the same with a checkbox.

As an example if the form detail has say 10 records and the checkbox is checked on two of the rows, I would like to hide the rest of the controls. This row is basically a Title and I do not want to offer the user options which are not valid.

Thanks

 

[JohnPapa]

To help I attach a real example. When the Title is checked I do not want the options to appear on the right.

Any suggestions to rewrite are welcome.

 

[JohnPapa]

The same problem in the report...

 

[MajP]

The report should be simple using sorting and grouping. But, I do not know your table structure. My guess you have a table of
Titles and a table of TitleSelections. Where each selection is related to a title. Then you link the data on TitleID. If not post your table structure or better yet an example DB.


With the form it is not as easy. Unfortunately there is no way to do a continuous form with a continuous subform. How many total "groups" do you have?
Also do you really have a yes field and a no field (and each is a yes no field). That is kind of confusing, unless you can choose both yes and no at the same time, which I doubt. That should be a single field. You can do that display but it needs to be an option group tied to a single field. That will require a little bit of code.

 

[JohnPapa]

The report should be simple using sorting and grouping. But, I do not know your table structure. My guess you have a table of
Titles and a table of TitleSelections. Where each selection is related to a title. Then you link the data on TitleID. If not post your table structure or better yet an example DB.


With the form it is not as easy. Unfortunately there is no way to do a continuous form with a continuous subform. How many total "groups" do you have.

Thanks for the reply. What appears on the form is in a single table. Maybe that is the problem.

 

[MajP]

If it is in a single table then the Titles should not be a single record but a column. If that is the case, you need to repeat the header for each question in the group. You would have something like:

At Present are you? Question 1
At Present are you? Question 2
....
Do you suffer from? Question xx
Do you suffer from? Question xyz
....

 

[JohnPapa]

Are you saying that the titles should be in another table which will be linked (1-to-many) to the questions table?

 

[MajP]

Are you saying that the titles should be in another table which will be linked (1-to-many) to the questions table?

That would be more proper and could provide more flexibility because the same question could be asked in different groups. However if the only information about the group is the question then just repeat the Titles as column for each record. But the Titles should not be a record by themselves.
Your table then is

GroupTitle (ex. Do you suffer from? Repeated for each row in group)
ResponseQuestion (Migraines)
Response (Yes or No)
MedicalHistory
Visible

This design will solve the report issue. You can get it exactly how you are asking using Sorting by (GroupTitle) in its own header, then have the responses in the detail section.

There is no easy way to do it in a form. That is an unfortunate limitation of access. You can add conditional formatting to disable controls and "hide" the other ones, but that is still not ideal.

Is the user entering data? The simplest is a single form with a subform. The user would see each question per group but only one group at a time. They would have to move to the next group after completing the responses. IMO this is no more difficult then scrolling down a long list.

 

[JohnPapa]

Basically the easy way out is to remove Titles and incorporate the title in the question, as you suggest.

 

[The_Doc_Man]

Ideally, you need not less than two tables for this, maybe three (one of them being a list of things to be asked).

First, a table with patient's personal information with a patientID field as the primary key.
Second, a list of questions to be asked with a questionID field as the primary key. The text of the questions goes here. The Titles would go here so that you could use GROUP BY if needed.
Third, a junction table that lists Patient ID, Question ID, and the spaces for answers.

This junction table would look like:
patientID, questionID, YNAnswer (as a Y/N field), Remarks field, other fields that might be relevant to the question. (Like for the "do you drink alcohol" question, you might exhibit the quantity as 1 beer/day, 1 case of Bourbon / day, etc.)

If this screening needs to include historical comments, then the junction table would have to include a date field of "when updated." However, here is the way you keep this from bloating your DB. If the answer to the question is NO and there is no other information like "remarks" or "quantity" then you don't store ANYTHING for that question/patient combo. But you use LEFT JOIN (or RIGHT JOIN depending on specifics) such that when you retrieve the patient's entries, you get NULL in the answers, and then use NZ( [YNAnswer], 0 ) and NZ( [Remarks],"" ) and such.

 

[MajP]

Basically the easy way out is to remove Titles and incorporate the title in the question, as you suggest

Not sure if you interpret that correctly. The title gets repeated in its own column. However, I think it is just as easy and more flexible to create the small Title table.
Also as @The_Doc_Man suggests there may be more tables associated such as linking these results to a patient. Or possibly to a visit for a specific patient (more complicated).

 

[JohnPapa]

Thanks for the reply. It appears to be the correct way to solve the problem.

But still it would not be possible to group the questions in the form, like in the case of a report.

 

[MajP]

With a report using sorting and grouping. My table
tblQuestionairre tblQuestionairre

questionID	GroupTitle	Question	Response
1​	Do you suffer from?	Head aches	No​
2​	Do you suffer from?	Stomach aches	Yes​
3​	Do you suffer from?	High blood pressures	No​
4​	Do you suffer from?	Diabetes	Yes​
5​	At present are you?	Pregnant	No​
6​	At present are you?	Taking medication	Yes​

Report. If you use the wizard it will ask what to group on. I grouped on GroupTitle.

 

[MajP]

But still it would not be possible to group the questions in the form, like in the case of a report.

There is no clean way to do it that I can think of. As I said you can disable and enable rows using conditional formatting, but I still think that will not look good and still be confusing. As I said if it was me I show the user each group of questions per view. You could come up with a slick navigation. Once they complete a group of questions it automatically moves to the next group or at least prompts the user.

 

[JohnPapa]

Yes, the report would work well.

 

[MajP]

FYI, if this is part of a bigger database you may want to run this by the folks here first. These types of "checklist" databases can look very simple at first, but can be a little tricky to create correctly. You may likely get several small "many to many" junction tables. Each table may be very little, but you may need more tables than you were expecting.

 

[Pat Hartman]

The problem is caused because you are violating normal forms. You are attempting to make a row serve as a group header as well as a detail. The correct solution is multiple tables as the others have suggested. At least two. You would use three to implement a m-m relationship if the same question can appear in multiple groups. On the form you need a mainform for the group header and a subform for the details. That fixes the problem but changes the presentation.

As for the report, you can have the presentation you want, again, by using a mainform and a subform. But since reports work differently than forms, the "list" can all flow the way you want to see it.

 

[JohnPapa]

Thanks Pat. The code was written in a haste 20 years ago and as pointed out it violates basic db guidelines.

Because I need to be (would prefer to be) backwards compatible, as a first step I will remove the Title functionality and maybe in the future I will introduce the proper 2-table setup.

 

[JohnPapa]

I really want to show on the form the title and the items that belong to the title and as a kludgy way of implementing this is to decide that the medical history can have a limit of 10 titles (or some other number) and for each title to have a subForm, in this case 10.
The titles and subForms will appear as needed. Not very proud of the setup, but at the end of they day it will be user friendly.

 

[MajP]

Again to be clear, the following is unfortunately not possible

Title
Subform
Title 2
Subform 2
Title 3
Subform 3

But there is no reason to be kludgy. You can have as many titles as you want, but only one at a time

Title X
Single Subform

[move to next title] [Move to previous title]

Then you have a single subform linked to the title by titleID. Now the user simply moves through the different titles. You can add checks so that they cannot move to the next section until all answers filled in. You can also move automatically to the next section if all questions are answered. Do not over think this.

If you can post the table with the questions, we can probably demo you some options and make some table recommendations.