Disappearing Forms

[Fred Worthington]

Greetings:

I have just updated my database by adding some new subforms. Trouble is,
whereas they appear normally in Design View, they appear as blank squares in
Form View. Only the new subforms are effected this way. Any ideas why this
is happening?

Thanks . . . Fred

 

[Van T. Dinh]

The Subform will appear blank if:

1. The RecordSource for the Subform (filtered by the LinkMasterFields /
LinkChildFields) is empty and

2. The RecordSource is not updateable or you have set the AllowAdditions
Property of the Subform is set to False.

Suggest you check the Recordset for the Subform.

 

[Fred Worthington]

Thanks for your response, Van,

I have checked and double-checked the record source and links for anomalies,
and can find none. My database requires a periodic review of data every 6
months. I created the 6 month review from scratch (which is a subform that
contains subforms), then created the subsequent review forms by copying the
tables and forms from the first then reasigning the record source for each
new form. I have had no problem with review periods for 12, 18, 24, and 30
months - everything functions perfectly. It is with the latest review
period (36 month) which was created the same way as its predecessors, that I
am having trouble. And, to further complicate this mystery, some of the
forms will occasionally appear (for apparently no reason) then disappear
again. When I open the subform by itself, all the nested forms show up just
fine - it's just when I open the subform from the master form that the
problem ocurrs. I tried deleting and reinserting the form to no avail.
Once, when I attempted to open a nested form with a command button I
received a message saying Access could not open any more tables. Am I
possibly straining the limits of Access (2000 SP-3) with too many tables and
forms, or maybe I need more RAM (currently, I have 512MB)?

Thanks . . . Fred

 

[Van T. Dinh]

(not sure if I follow your description???)

Have you been compacting and repairing the database regularly?

Use RecordCount in Form to count the number of Records in the Subform.

Open the RecordSource Query of the SubForm by itself and see if it is
updateable (whether the New button is enable or there is an empty New row in
the Datasheet of the Query).

BTW, I think your nested Subform arrangement is too complicate for normal
users in my experience. Personally, I stick with ONE level of Subforms.

 

[Fred Worthington]

Van,

Thanks again for your response.

I have been compacting and repairing on a regular basis.

The newly created tables and forms are empty (as were all the others when
they were first created), therefore, I'm not sure I understand why the form
won't appear even thought there is yet to be data entered (initial data
needs to be entered to "register" the subform with the master form by
displaying the primary key, but the form has to be visible to initiate that
first entry). None of the forms are based on queries except two that need
to display options in alphabetical order.

As I mentioned, even though this database is dense with forms and subforms,
it has functioned flawlessly until now.

Would an upgrade to Access 2003 possibly be of any value in resolving these
issues?

Thanks . . . Fred

 

[Van T. Dinh]

Do you meant the Main Form based on a Table (not Query) is blank???

Have you checked the AllowAdditions Property and RecordCount of Subforms?

Describe the relevant Table Structure, especially the PK / Linking Fields
and Describe the Forms / Subforms (using Form names) with RecordSource,
LinkMasterFields / LinkChildFields and the order of nesting.

I doubt Access 2003 will help sine it's more likely to be something wrong
with the set-up of the Forms / Subforms.

 

[Fred Worthington]

Van,

Thanks again for your response.

My main form is not blank. Neither is my first tier of subforms. It is the
subforms nested within subforms that are blank (and that is only in one
subform - the last one I created). Some are combo boxes that derive their
entry options from another table.

AllowAdditions Property is "Yes." If I am understanding correctly your
meaning of RecordCount, no records have been entered in the new subforms so
there should be none.

The Form structure consists of a Master form with numerous Subforms, most in
the form of combo boxes. Some of the subforms have forms nested within
them. All primary fields are linked (LinkMaster/LinkChild) by the ClientID
(an assigned number). There are no autonumbered fields (except in secondary
tables that contain lists of items accessed by multiple forms). The purpose
of this database is to track a client's medical information (i.e.,
BrandDrug, GenericDrug, Doctor, Pharmacy, MedicalConditions, etc.). All of
these items (e.g., over 1,000 drugs) are listed in separate tables. This
facilitates being able to update information every 6 months using separate
forms without having to replicate the record source for the corresponding
table (the form's record source). A typical record source string would be
as such: the BrandDrug form has as its record source the ClientBrandDrug
table which has as its record source (the complete list of drugs) the
BrandDrug table. The BrandDrug table is the record source for all the
ClientBrandDrug tables (e.g., 6 MonthClientBrandDrug, 12
MonthClientBrandDrug, 18 MonthClientBrandDrug, etc., etc.) which are in turn
the record source for all corresponding ClientBrandDrug subforms (e.g., 6
MonthClientBrandDrug, 12 MonthClientBrandDrug, and so on).

Since the company using this database wants to archive virtually everything
about each client every six months (keeping that data separate from the
previous 6 months), I replicate (in structure only) 15 tables and 15 forms
for each 6 month review. At the time of each review, the client's data is
updated on the primary form with archival data being entered in the review
form. This allows all the Queries and Reports (which are intended to return
a current profile) to function correctly without updating. My review period
forms (6 Month, 12 Month, 18 Month, etc.), which are designed just like the
primary form, are nested together on a single form and separated by page
tabs. It is the 36 Month Review form that will not display its nested
subforms (the review form itself is visible).

Please bear in mind that this set up has worked flawlessly for two years,
and I am only having a problem with the most recent review period (36
months). All prior review periods are functioning correctly, and I have
created the current review period the same way I created all the others.
Therein lies the mystery. This causes me to wonder about the growing depth
and breadth of this database (currently around 150 each, tables and forms,
as well as 50 to 60 each Queries and Reports) possibly exceeding the
capabilities of Access.

It may also be useful to note that, whereas the structure of this database
may seem complex (because of the number of times individual tables and forms
are replicated), it really is not. Sort of like the cockpit of a B-52 where
you have a lot of guages and dials because the aircraft has 8 engines. You
only need to learn how one set works, and all the rest are the same.

Hope this helps you visualize my situation a little better. Personally, I
am really beginning to question the limits of Access, because I am supposed
to go up to 48 months (3 more review periods than I currently have) and I
feel I need to address future problems now, while the database is still
functioning properly. It is possible that we need to find a better, less
convoluted way to archive data. Are there any known limits to the size and
complexity of an Access database? My current mdb file is around 17MB (after
compact and repair).

Thanks for hangin in there with me, Van . . . Fred

 

[Fred Worthington]

Van,

I failed to mention in my previous message that the 36 month review period
Subform displays all the nested subforms correctly when it is opened by
itself. However, once it is nested on the 36 Month Review page (where it is
linked to the Review Form, itself a subform of the Primary Form), all the 36
Month Review subforms go blank. I realized after posting the previous
message that knowing this would be important for you to consider as you
deliberate.

Thanks again . . . Fred

 

[Fred Worthington]

Van,

If you leave me to my own devices long enough, I might just figure this
thing out (or go mad trying).

I discovered an anomalie that seems relevant to your original diagnosis.
When I open the subform properies window and click the expression builder
button next to Link Child Fields and Link Master Fields (something I never
thought of doing before because ClientID was clearly displayed in both
fields), instead of displaying the Subform Field Linker, I get a message
that says: "The expression you entered refers to an object that is closed or
doesn't exist." So, I don't doubt (as you suspected) therein lies the
problem, however, I am still having trouble figuring out why the links are
not there. I have checked and double checked the Forms and Tables for
anomalies (they certainly appear to exist). I have tried deleting and
re-linking the subforms, but all to no avail. The subforms going blank
coinsides with the insertion of the superior form on the Tab Control Page.
Perhaps you can make something of this and offer some troubleshooting
suggestions.

Thanks a million . . . Fred

 

[Van T. Dinh]

Personally, I wouldn't have the Table Structure you described. The
separation of data display into 6 months' lots is the function of the
"Presentation Layer" of your database and NOT the function of data storage
but your application carries it through to the "Repository Layer".

I am not sure what you do with the data at the moment as it seems that every
6 months you have to move the data from one 6-month lot to the earlier
6-month lot??? Data that are 6 months old will be 12 months old in 6
months' time!

You Table Structure seems to store data in the Table names also, e.g.:

"6 MonthClientBrandDrug"
"12 MonthClientBrandDrug"

(6Months and 12 Months)

This is certainly not a good sign since Access / JET cannot process data in
Object names. Also in generally, Tables with same structure storing similar
data should be combined into one Table as per the Relational Database Design
Theory. Using your example of the cockpit of a B-52, there may be may many
gauges and dials but if the B-52 has logging facility, more likely that not,
it combines the data into a limited number of Tables, not one Table per
gauge or dial.

Perhaps, you should look the Relational Database Design Theory and see what
you can do.

There are limits on the number of nested levels of Subform (3 for A97, 7 for
A2K IIRC, not sure about A2K2 or A2K3) plus there are other limits you might
have hit. Check Access Help on "Specifications" for these limits. You can
have 32K Access objects in the database so the number of Forms & Reports,
etc ... shouldn't matter. The file size limit is 1 GB for A97 and 2 GB for
later version (using JET local Tables) so you are no where near the size
limit

Personally, I stick to the max. of ONE level of nested Subforms. Anything
more than that, users seem to be struggling to use the complex Form
efficiently in my experience. When I have a number of Subforms (of the same
nesting level), I generally arrange so that only 1 Subform visible at any
given time.

Remember that users don't have to understand the database structure /
relationships as we do as database developers. Also, people can only
concentrate / absorb a limited amount of information at a time so don't
present too much data on the screen at the same time.

 

[Fred Worthington]

Van,

Thanks for your comprehensive response. It sounds like I may be looking at
a significant overhaul of this database. If major changes are forthcoming,
now might be a good time to upgrade to the latest version of Access so as to
capitalize on the enhanced functionality during the revisions.

I'm sure I'll have further need of your good counsel, but prior to that I
need a little time to digest and research your suggestions (since some are
beyond my current level of expertise). I'll report back soon to inform you
of my progress.

Thanks again for your help. I really appreciate it.

Fred

 

[Van T. Dinh]

Suggest you get hold of a copy of the book "Designing Relational Database
Systems" by Rebecca Riordan published by MSPress. It is out of print but
there still copies around. Try Amazon / others and see if you can pick up a
second-hand.

I find the discussions about different aspects of Relational Database Design
in this book are really useful.

 

[Fred Worthington]

Thanks for the book suggestion, Van - I'll check it out. No doubt it will
be a big help.

While I still have your attention, and since you have gained a little
insight into the structure of my database, perhaps you would be so kind as
to render one more opinion. The most challenging aspect of this database,
and the one most likely to cause problems, is the way I am currently
archiving records at 6 month intervals. Can you suggest a better way to
save old data while maintaining current data in the main forms (so the
Queries and Reports will render current profiles). At this time, I don't
foresee a need to create global relationships with archived data. Its
historical value is simply to facilitate the manual review of individual
records. If archival data can be accessed by linking with the ClientID,
that should do the job. Seems like the easiest thing would be to just make
a copy of the original record before updating it, but I don't know a simple,
user friendly way to do this - hence my somewhat complex and flawed
solution. If you can just point me in the right direction (if there is
indeed a path), I would be most grateful.

Thanks again for your help, Van . . . Fred

 

[Van T. Dinh]

That's was my question in the second paragraph two posts back. Sure you can
use VBA code to run an Append Query to append the 6-month Records to the
12-month Table and then a Delete Query to delete the Records in the 6-month
Table. The point is that is absolutely inefficient. If you store all of
them in ONE table and simply use the "Presentation Layer", i.e. Forms to
separate them out in blocks of 6 months. This way, you don't even need to
move Records around!

You can set correct criteria (using criteria on a Date Field in the Table)
to select current 6- month Records, 12-month Record, etc... so showing only
"current" data in Queries / Forms / Reports is not a problem.

Now you see what I meant by "Repository Level" (storing data together) and
"Presentation Layer" (showing data in whichever block you want).

 

[Fred Worthington]

Van,

What you are saying sounds great. Now if I can just figure out how to
implement it. I understand the theory (as you state it) behind Repository
Level and Presentation Layer, but I'm somewhat at a loss as to how to set it
up. There is no specific reference to either in the online help files or
the Access manual I have. Perhaps a specific example would be easier for me
to understand. When a Client enters the program, they will be prescribed
several medications by a specific doctor from a specific pharmacy. When
they come back for their 6 month review, they may be taking different
medications prescribed by a different doctor from a different pharmacy. How
do I set my database up so I can generate reports based on the Client's
current information while saving the previous information? If I am
understanding you correctly, you mention storing current and archival data
in one table. If you can explain how to do that (please keep it simple), I
may just grasp the concept enough to figure the rest out on my own. Bear in
mind that my current set up involves listing items (i.e., medications,
doctors, pharmacies, etc.) in separate tables which are the record source
for the tables that are linked to the forms. I use combo boxes for data
entry. The use of Value Lists is impracticle because of the number of
medications (over 1,000) as well as doctors and pharmacies numbering in the
hundreds.

I feel like I'm just about to turn the corner on this. Thank you very much
for your patience . . . Fred

 

[Van T. Dinh]

If you have a Date Field [PrescribedDate] in the Table [ClientBrandDrug]
that has all Client-Drug Prescription Records then a simple Query with the
SQL String:

SELECT *
FROM [ClientBrandDrug]
WHERE [PrescribedDate]
BETWEEN DateAdd("m", -6, Date()) AND Date()

will select all Records for the last 6 months.

Similar Queries can be done for other periods. You can use these Queries as
RecordSources for the Forms / Subforms.

 

[Fred Worthington]

Van,

Believe it or not, I already have several Queries that use a date field to
group records. Trouble is, for those that don't have date fields (and they
are many), I will have to add it to a table with existing records. That
means I will have hundreds of records with empty date fields. And - on top
of that, I still have my original child/master linking problem. If I
totally revamp this database I may have trouble doing so without
compromising the data that has already been entered (over 2600 clients to
date). Is there a way to link two separate databases and use the ClientID
to retrieve associated records? Perhaps I could create a new database and
use the old one as an archive. What say ye?

Thanks . . . Fred

If you have a Date Field [PrescribedDate] in the Table [ClientBrandDrug]
that has all Client-Drug Prescription Records then a simple Query with the
SQL String:

SELECT *
FROM [ClientBrandDrug]
WHERE [PrescribedDate]
BETWEEN DateAdd("m", -6, Date()) AND Date()

will select all Records for the last 6 months.

Similar Queries can be done for other periods. You can use these Queries as
RecordSources for the Forms / Subforms.

--
HTH
Van T. Dinh
MVP (Access)

Van,

What you are saying sounds great. Now if I can just figure out how to
implement it. I understand the theory (as you state it) behind Repository
Level and Presentation Layer, but I'm somewhat at a loss as to how to

set

it
up. There is no specific reference to either in the online help files or
the Access manual I have. Perhaps a specific example would be easier

for

me
to understand. When a Client enters the program, they will be prescribed
several medications by a specific doctor from a specific pharmacy. When
they come back for their 6 month review, they may be taking different
medications prescribed by a different doctor from a different pharmacy. How
do I set my database up so I can generate reports based on the Client's
current information while saving the previous information? If I am
understanding you correctly, you mention storing current and archival data
in one table. If you can explain how to do that (please keep it

simple),

I
may just grasp the concept enough to figure the rest out on my own.

Bear

in
mind that my current set up involves listing items (i.e., medications,
doctors, pharmacies, etc.) in separate tables which are the record source
for the tables that are linked to the forms. I use combo boxes for data
entry. The use of Value Lists is impracticle because of the number of
medications (over 1,000) as well as doctors and pharmacies numbering in the
hundreds.

I feel like I'm just about to turn the corner on this. Thank you very much
for your patience . . . Fred

 

[Van T. Dinh]

I don't know as I can't see your database.

Perhaps the best way is for you to read the book I mentioned (which has
design paradigms, user interface design, etc...) and a general Access book
(have you read any???) and decide which is right for your requirements.

Perhaps now you know that it is vital to get the Database / Table Structure
correct at the beginning since Queries / Forms / Reports, etc... depend on
the Table Structure being properly designed and implemented.

 

[Evi]

If I've understood you correctly, you can fill in your empty date fields
using an update query. You can certainly carry that information from another
table if it has a common field and one

So if both tables contained the same ClientID you would add them both to the
query grid, link ClientID, put the date field from the table with blanks
(Table1) into the grid. Make the criteria Is Null. In the UpdateTo row type

Table2!MyDateField

substituting the real names of your table and field.
Evi

Van,

Believe it or not, I already have several Queries that use a date field to
group records. Trouble is, for those that don't have date fields (and they
are many), I will have to add it to a table with existing records. That
means I will have hundreds of records with empty date fields. And - on top
of that, I still have my original child/master linking problem. If I
totally revamp this database I may have trouble doing so without
compromising the data that has already been entered (over 2600 clients to
date). Is there a way to link two separate databases and use the ClientID
to retrieve associated records? Perhaps I could create a new database and
use the old one as an archive. What say ye?

Thanks . . . Fred

If you have a Date Field [PrescribedDate] in the Table [ClientBrandDrug]
that has all Client-Drug Prescription Records then a simple Query with the
SQL String:

SELECT *
FROM [ClientBrandDrug]
WHERE [PrescribedDate]
BETWEEN DateAdd("m", -6, Date()) AND Date()

will select all Records for the last 6 months.

Similar Queries can be done for other periods. You can use these

Queries

as
RecordSources for the Forms / Subforms.

--
HTH
Van T. Dinh
MVP (Access)



set for pharmacy.
How simple), Bear in
the

 

[Fred Worthington]

Van,

I have ordered the book you recommended, and I look forward to reading it.
I'm sure it will help.

In answer to your question regarding other texts, I have several, none of
which has been able to shed any light on my problem (according to the text,
I have done everything right). It is from these books and extensive use of
this newsgroup that I have developed this database. John Vinson helped me
(through this newsgroup) set up my current table structure (which, I
acknowledge, doesn't necessarily mean I did it right). What baffles me is
that, up until I encountered the linking problem in my 36 month review, this
database has performed flawlessly and continues to do so with the
aforementioned exception. Several people in the organization for whom I
created this database, who are experienced Access users, have commended me
for the design and usability of this database. Of course, I recognize that
this praise, well intentioned though it may be, could very well be
misguided.

It might be useful for you to know that database design is not what I
primarily do. I am a multimedia producer by trade. I got involved in
database design because I live in a small town where technical resources are
minimal, and my multimedia customers also need databases, so, by
conscription, I am trying to rise to the challenge. My problem stems from
the fact that I don't work on databases all the time, and the skills and
knowledge required to master database design are very different from those
applied to multimedia. Multimedia skills are like riding a bike -
relatively easy to re-master. However, to me, database design skills (if
you don't keep them honed to a sharp edge) are like having to go back to
college six months after you graduate. It's like comparing the talent of a
knife thrower who can pin a fly to the wall from 30 feet, and the skill of a
surgeon who can operate on your brain with a scalpel. A bad multimedia
production can't do nearly the harm that a bad database design can. I have
a lot of respect for you pros!

For the time being, I reckon we'll just wait and see how I do with the book.

Thanks again . . . Fred