Okay - apologize in advance for the length, but I want to make sure
all you knowledgeable and helpful people have all the details you need
to hopefully point my newbie rear in the right direction.
I've got a fairly complex (a lot more complex than originally
intended) database with three main tables. The database is designed
to track information about certain files. The tables are set up with
one to one relationships, primarily because one of the tables contains
a number of memo fields, and I was getting a number of corruption
errors before I seperated the memo fields. I recently added the third
"one-to-one" table, because it contains certain details that are
non-confidential so I wanted a table everyone could access, plus it
makes it alot easier for me to manipulate (rather than deal with 50 +
fields in one table)
The tables and primary/foreign keys are set up thusly:
[Cases] [Cases-memos] [Cases-Details]
-------- -------------- ----------------
[CARecID] <----> [CAMRecID]
[CARecID] <--------------------------> [CADRecID]
[Cases].[CARecID] is the primary key (an autonumber field)
[Cases-memos].[CAMRecID] is a foreign key (number, long)
[Cases-details].[CADRecID] is a foreign key (number, long)
One-to-One Relationships have been defined, with cascading update and
delete.
Anyways, my database is split into a front and back end, with the back
end residing on a network server (Novell). Two main people in my
office use the DB - myself (via a Windows XP machine) and an assistant
(via Windows NT). The assistant does most of the data entry, while I
just review data.
For some time, adding new cases was done via a form, which called data
from a query combining two tables. The vast majority of the info to
be input resided in the [Cases] table, with 3 memo fields residing in
[Cases-memos]. New cases could be added, and everything worked fine,
with the memos fields plugging data into a matching new record in the
[Cases-memos] table. I recently revised the form to also link data
residing in [Cases-details].
Since that revision, the form works perfectly on my machine - just
like the old one. If I create a new case in [Cases], corresponding
entries are created in [memos] and [details], and everything works
fine. HOWEVER, when my assistant tries to create new cases, she gets
an error reading "You Cannot Add or Change a Record Because a Related
Record is Required in Table 'Cases'".
I suspect the problem is the order in which new data is being saved to
the tables - Access probably needs to create a record in [Cases]
first, and for whatever reason is not doing this. I'm stumped as to
why it happens only on her machine, not on mine.
Any ideas ?
If there's some vb or macro way to make sure the tables are saved in
order and that new records are created sequentially in each of the
tables before saving the data, I'd really appreciate some help on how
to fix that.
j
