Disappearing Forms

[Fred Worthington]

Evi,

Thanks for your response. I have saved your tip so I can refer back to it
when I am ready to implement the date field revision. I want to conduct a
little more research (such as Van's book) before I make any changes to this
database.

Thanks . . . Fred

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)



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