Form controls have dissapeared

[jlt199]

Hi,



I've been working on my first database. I thought I was almost there and just had to iron out some form crinkles however, I realised the the field I had set to my primary in one of my tables isn't there for all of the records. So I added an autonumber field to be the primary key field in that table, reset my table relationships accordingly and directed the combobox on the form to point to the new location.

However, in both form view and layout view all of my form controls won't display. I can see them in design view. Can anyone help me fix this please?

Thanks

[June7]

Suggest you provide the db for analysis, follow instructions at bottom of my post. Identify the objects involved.

[jlt199]

ExtendedTeamDatabase_v2_7.zip

This is the broken version, I can also upload the pre-broken version if it would help. Many thanks

I'm pulling my hair out, I hope it's not a start from scratch situation

[June7]

INNER join requires related records in both tables for any to display.

Change the RecordSource sql from INNER join to RIGHT: "Include ALL records from 'PatientRecords' and only those records from 'Patient' where the joined fields are equal."

A form can enter/edit data to only one table. You can include the Patient table to show the patient info but do not edit Patient info.

Set the patient info controls to Locked Yes and TabStop No.

Consider a form/subform arrangement instead if you want to be able to edit both tables.

Tables in query are not linked on the PK/FK fields. ID is designated the PK in Patient but you are linking on PatientPHN. If you want PatientPHN as the link then set it as PK and the ID field is unnecessary.

Your data structure is not normalized. Multiple similar fields in PatientRecords is an indicator. Also, there are circular relationships which can cause issues.

[jlt199]

Thanks, I'm not quite sure I know what a normalised design would look like for more than the simplest cases which are given in tutorials.

I've attached my attempt at a normalised entity relationship diagram for this diagram, would you be able to give me some feedback please?

entityRelationship.zip

Many thanks

[June7]

I see basically the same issues.

Patient management database has been topic of a number of threads. Here is one https://www.accessforums.net/access/...ent-38955.html

[Missinglinq]

...in both form view and layout view all of my form controls won't display. I can see them in design view...

Normalization aside (but don't misunderstand, it is important!) Controls don't appear in Form View or Layout View when three conditions exist at the same time:

• The Form is Bound to a Table or Query
• There are No Records in the underlying Recordset or the Recordset cannot be accessed
• The Form cannot have New Records added

The reasons that a Bound Form cannot have New Records added include:

1. The File's Read-Only Property has been set to Yes (has been ticked)
2. AllowAdditions for the Form is set to No
3. The underlying Query the Form is based on is Read Only
4. User doesn't have Read and/or Write Permission for the Folder where the Data resides or there is a connection problem with the network.
5. Folder the File resides in (in versions 2007/2010) not having been declared as 'Trusted'
6. Form's Recordset Type is set to Snapshot

Linq ;0)>

[jlt199]

Thank you for your responses, I have been through a bunch of tutorials on Normalization and have attempted to redesign the database to conform. However, it's not right and I can't figure out how to fix it.

I have compiled the following information regarding what I am trying to achieve:

• Each entry in the database:
  
  • Is completed on a single date
  • Pertains to a single patient
  • Contains information about a single program
  • Is attached to a single Clinic Physician
  • Will have the responses to at least one form, possibly several
  • A maximum of one instance for any one form
  • Each form has a different form name

• The patient:
  
  • Is attached to a single Family Physician
  • Can be attached to a Program without completing any forms
  • May be attending one or more programs
  • Will be attached to a single Clinic Physician per Program being attended

• Other considerations:
  
  • Allowed Form # depends on the Form Name
    
    • Different Forms have a different set of Form #s
    
    
  • Allowed Form Names depends on the Program being attended
    
    • Different Programs have a different set of Forms they use
    
    
  • Allowed Clinic Physician depends on Program being attended
    
    • Clinic Physicians may work in one or many different Programs
    
    
  • Form Responses depend on the Form Name
    
    • Number and style of responses will be different for each Form

My current attempt at a design is here but clearly it's not right

Problems I've identified

• No direct link between Patient and Program
• There are likely to be more than one Clinic Physician attached to a program, but only one attached to the Patient for that Program
• Given that each form type will have a different number of questions and a different style of response, how do I enter this into the database?

Is anyone able to give me a prod in the right direction please?

Many thanks

[June7]

Consider:

tblPrograms
ProgramID

tblPatients
PatientID
PhysicianID (family)

tblQuestions
QuestionID
Question
QuestionType

tblProgramClinician
PCID
ProgramID
PhysicianID (clinician)

tblPatientPC
PPCID
PCID
PatientID

tblEntries
EntryID
PPCID
DateCompleted

tblResponses
EntryID
QuestionID
Response (all responses entered as text)

This is attempt at fully normalized hierarchical structure. Unfortunately, it will not allow for a 'questionnaire' like bound form for data entry.

Why not have one table for all physicians, family and clinician?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[jlt199]

Thanks for your reply,

I've only just seen your response, so I will spend some time studying it. However, at first glance, I don't think your suggestion covers all of my conditions, for example, which Physicians belong to which programs and which program is each patient referred to.

I'm also worried that the tblResponses will very quickly become extremely large.

My second attempt is here, is it any closer? Many thanks

[June7]

In my suggested structure:

tblProgramClinician associates physicians with programs

tblPatientPC associates patient with program/clinician

That's my best shot at trying to design a structure for your data.

Your latest attempt seems to have same issues as your first from post 5.


If by large you mean number of records, nothing wrong with that.

[jlt199]

Thank you for your response, my understanding is that the structure you are suggesting is as below:




Assuming for a single entry a patient has filled out three different forms with 10, 15 and 5 questions respectively. The responses table will now have 15 records for that one entry. How come this doesn't matter? I was originally thinking of the responses table having a column for each question and then only populating the relevant columns in the table for each entry. Why would your approach be better? I'm not trying to be rude or argumentative, I'm truly trying to understand.

Your structure doesn't include the different types of forms in anyway. I have tried to edit your design to add this connection. Does this make sense?




Many thanks

[June7]

A field for each question is not a normalized structure. If you had to add a new question means editing table, queries, forms, reports. However, this does make for a nice questionnaire style input form.

It is a balancing act between normalization and ease of data entry/output. Non-normalized structure is more often problematic in data analysis than normalized structure. However, there are techniques to deal with those issues if they arise. Just depends if you want to consider them now or later if and when encountered.

In a normalized structure, tables are customarily narrow and long with few empty fields. The opposite is like an Excel 'flat file' worksheet. The number of records created in a process should not be a consideration. Relationships should drive db design.

I am not clear on the need for the various program forms.

This essentially exhausts my knowledge base on db design, especially as pertains to your situation.

[jlt199]

Thank you

One of the things we will be "pulling out" to analyse are the form responses. We will be doing this by form type and gender, age and program attended of the patient. I had assumed this would be easier if there was a field giving the form name. The only other way I can think to do this is to find a question unique to each form and search for that, but that doesn't seem as elegant

Thanks again