UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Has Stopped Populating From A Query, Access 2010    
 
   
CatherineOz
post Dec 5 2017, 06:24 PM
Post#1



Posts: 303
Joined: 13-February 00
From: Sydney, Australia


Part of my breast cancer data base has stopped working and I cannot find the cause, and therefore the solution.

This is the scenario:

  • Open frmLookUpFollowUp and enter a patient surname plus or minus the first name. ([censored] defaults to female)
  • Click the patient name
  • frmAddFollowUp opens but the form is not populated
The form pulls data items from several tables as a summary of the patients history and details. It’s purpose is to provide a quick way of updating the patient record. The alternative is to use the whole patients record (which is what I am having to do at the moment) which is 5 pages long.

I have attached a cut down version of the database with 20 made up patients (surnames include Duck, Green, Smith, James, Drew – see tblPatientMaster for others)

Any help would be most gratefully received.

Christmas greetings

Catherine


Attached File(s)
Attached File  Database1.zip ( 1.58MB )Number of downloads: 6
 
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2017, 06:59 PM
Post#2


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


This might be the problem.

Attached File  DataEntry.png ( 24.17K )Number of downloads: 7


Read about what the "Data Entry" property controls on a form.
This post has been edited by GroverParkGeorge: Dec 5 2017, 07:00 PM

--------------------
Go to the top of the page
 
CatherineOz
post Dec 5 2017, 07:48 PM
Post#3



Posts: 303
Joined: 13-February 00
From: Sydney, Australia


Thank you George. It has certainly improved the situation but not completely resolved it. It would seem that patients who do not have a date in the NED (No Evidence of Disease) field, do not populate the frmAddFollowUp form. I can't emulate this in the cut down database.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 06:50 AM
Post#4


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


That would be consistent with what we know about queries using INNER JOINs. If the required field(s) are not present in one of the tables, no records are returned. And that means your form can't be populated for that patient.

The solution is likely to be the classic approach to forms design in Access. Most of the time it is much better to base each main form on one table, or on a query on a single table. As soon as you start joining in additional tables in a query for a form that needs to be updated, you often run into exactly this problem. Either that, or such queries are not updateable making them inappropriate for use in a data entry form.

The response is to rethink the form. Eliminate the multiple table query as the form's recordsource. Redesign so that the main form is based on the "one" side table in the relationships, with subform(s) for the "many" side table(s) needed to complete the record. I know that you wanted to "simplify" by going this route, but an unfortunate consequence of that approach is often other problems are generated along the way.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 07:11 AM
Post#5


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


I also apologize for not pointing this out earlier.

Another BIG part of the problem is that the tables in this database are not properly normalized. The main table, called "tblPatientMaster" is one wide "spreadsheet" style table. That's a problem for a lot of reasons.

I strongly suggest you stop now. Spend some time studying normalization and the proper design of tables in a relational database. We have some good starter articles in our Newcomers Reading List.

Figure out how to resolve that "spreadsheet" style table into a set of relational tables that work with your data. Then, I think you'll find form design problems like this one are much more readily fixed.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 07:59 AM