Solved Can I hide controls in a record in Detail View (1 Viewer)

JohnPapa

Registered User.
Local time
Today, 02:07
Joined
Aug 15, 2010
Messages
622
My "theoretical" idea of having multiple titles and subforms is possible, but a bit of a nightmare.

What you mention is possible ie

Title
Subform
Title 2
Subform 2
Title 3
Subform 3

Unfortunately, the CanGrow and CanShrink for forms works only in preview and print. So for this setup to work you need to calculate the position of everything (Top property) and adjust when you add or delete records. Truly a nightmare.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:07
Joined
May 21, 2018
Messages
6,371
I assume you mean if the titles are static you could set the number of labels and subforms. Which is fine if the data is rarely changing. Is that the case?
 

JohnPapa

Registered User.
Local time
Today, 02:07
Joined
Aug 15, 2010
Messages
622
The titles do not need to be static. They are dentist definable and the items of the subform which follows, essentially is linked the title.

The titles are in one table and the subform items in another table.

The data (medical history questions) will be set by the dentist and once set it will rarely change. Every dentist will do his own questions. After some thought, it will not work because based on the changing content of the subforms, what follows the changing subform needs to be moved up or down. Not practical.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:07
Joined
May 21, 2018
Messages
6,371
My "theoretical" idea of having multiple titles and subforms is possible, but a bit of a nightmare
Explain to me how you would do this "theoretically"? I am very good at coding "theoretical" solutions, but I can only think of one way to do that. It would only be viable for a small number of titles and a known max amount of titles.

Lets say 10 max titles and corresponding groups.

1. Make 10 labels and 10 subforms (all start off hidden)
2. Position the first label and first subform.
3. Get the count of records for the first subform and resize.
4. Read the Title from the table and set the caption of the label
5. do this for each label and subform

That should work, but it is a lot of effort for not much gain.

However, I believe your solution is even more complicated. There is likely a junction table something like
PatientID_FK key to patient table
ResponseID_FK Key to response table (only need to store yes answers)

When you select a response it adds it to the junction table. It deletes it when you unselect.
Commonly called a "checklist form"
Here is an example
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Feb 19, 2002
Messages
36,387
Don't even think about hardcoding the number of detail items in a group. That's the kind of thinking that created this bad design in the first place.

There are two aspects to a questionaire - the definition and the recording of answers.

Here's the example if the same question can be used in multiple sections. It takes 5 tables because you have to define the qutstionaire, the groups, the questions. Then you need two junction tables to put them together.

tblQuestionaire
QuestionaireID (autonumber, PK)
QName

tblGroups
GroupID (autonumber, PK)
GName

tblQuestions
QuestionID (autonumber, PK)
QText

tblQuestionaireGroups ''' junction table
QGID (autonumber, PK)
QuestionaireID (FK, UniqueIDX fld1)
GroupID (FK, UniqueIDX fld2)
GroupSeq

tblQuestionaireQuestions ''' junction table
QQID (autonumber, PK)
QGID (FK, UniqueIDX fld1)
QuestionID (FK, UniqueIDX fld2)
QuestionSeq

Now that those are defined, we get to the recording of answers part.

tblPatientQuestionaire
PQID (autonumber, PK)
PatientID (FK)
QuestionaireID (FK)
DateAnswered

tblPatientAnswers
PAID (autonumber, PK)
PQID (FK, UniqueIDX, fld1)
QQID (FK, UniqueIDX, fld2)
PatientAnswer ''' this can be defined as text for simplicity but it could get much more complicated

When you assign a questionnaire to a patient, you use the selected QuestionnaireID to select all the related QQID's and append them to tblPatientAnswers.

This structure will allow you to define an infinite number of questionnaires with an infinite number of reusable questions which are assigned to an infinite number of groups to keep logically related questions together. You use the sequence numbers in the two junction tables to control the order in which the groups and the questions within a group appear.

The data (medical history questions) will be set by the dentist and once set it will rarely change.

The outline above will allow the dentist to define his own questions and create his own questionnaires. You can offer templates to help get them started. But, you will never have to worry about the layout after that.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:07
Joined
May 21, 2018
Messages
6,371
Don't even think about hardcoding the number of detail items in a group.
I do not think there was any mention of limiting the responses (details). The suggestion was that if it was possible to limit the Max number of Titles (in turn the max number of groups), then you could fake this.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Feb 19, 2002
Messages
36,387
Not sure why you would ever want to fake something you know how to do correctly but whatever.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:07
Joined
May 21, 2018
Messages
6,371
Not sure why you would ever want to fake something you know how to do correctly but whatever
Well for example, because Access is limited to form and controls that basically came out in 1995 with limited updates since then. So if you want to build a checklist that is user friendly then you need to do something that I show in thread #24. If access had something like an unbound grid control this would be pretty trivial. I call that a fake since you are faking a checklist and no way to do that with a bound control.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Feb 19, 2002
Messages
36,387
Except that this seems to be a typical questionnaire that doesn't need to be implemented as anything other than a subform. If the questions are always Y/N, that simplifies the Answers part of the app. The OP is having presentation problems because he has combined the presentation layer with the data layer as if he were using a spreadsheet. We have simpler models for questionnaires than I posted but the one I posted accommodates the grouping the OP wants. The model can be simplified if it accommodates one and only one questionnaire. I think that is shortsighted so I wouldn't do it but at least it would be a normalized solution and it gets the design part of the solution down from 5 tables to 3. If the questions don't ever occur except in one single group, you can use a two table model. That is as simple as it gets and it still doesn't require a special control with checkboxes. A bound subform solves the problem.
 

Users who are viewing this thread

Top Bottom