WHY CAN'T I ADD A NEW RECORD TO MY TABLE HELP!!!

[chubby]

Background
OS Win NT 4.0
Access ver 2000
# of users 22

-6 tables
-All tables have a primary key
-Enforce referential intergity set
a. Cascade update related field set
b. Cascade delete Related Records set

-Type of form used (TAB FORM)
-Record source (Query)

Problem- The other day I split up my 135 field table up into 6 tables. Now I can't add, or save a record, what is the problem PLEASE...

 

[LittleSmudge]

WHY ?

As in WHY did you split up the table ?
If all of the fields are part of the same relation then they belong in the same table If they are not part of the same relation then the relationships between the tables will form a key part of the discussion and the solution to your current problem.

How do these tables link together ?


G LS

 

[chubby]

I split up the table because I need to add about more fields, in access you can only have 255 fields in one table.

Second, I linked each table by the Case no# field. No two cases have the same number so that takes care of the primary key answer.)

Third, I was wondering would I get better performance by splitting up the table?

I was was thinking of not using a tab form and just split up the tab form into 6 forms and generate a couple of 100 case numbers with blank fields.

bottomline is if I wanted to expand the database wouldn't I still need to split it up? I need to add about 200+ fields to what I already have....

 

[LittleSmudge]

Issue 1 - Okay fair cop YOU have come up with a good reason to split it in two - but why SIX

Issue 2 - CaseNo becomes the Prime key - but that doesn't tell me ( explicitly ) how you have linked them. Do you mean EVERY table has a CaseNo primary key and they are all linked together in the Relationship diagram in One-One links ?
Are these links all back to one central point ( a Star pattern ) or one off the next ( Daisy chain ) ?

Issue 3 - Quite the reverse I would suspect.


More questions, I know - but it is getting us closer to a good solution to your problem.


G LS

 

[chubby]

Yes every table has the case # in it. The main table
(the incident table) is the only table with a primary key. All the other tables are linked to the incident table star type connection. (NOT Daisy chain)

I can get it to work but only if the end user adds the case no. to each form manually when needed. I wanted it where my end user adds a new record to the main form the incident form that all they would need to do, the other five sections would automatically be linked. So if they needed them they wouldn't have to add a case no. to that form to just to link up to the incident form.

Is there any way to make my six tables act like one? When I had one table it was so easy to add records now it's a pain...

 

[LittleSmudge]

Yes there is - but before the definative answer - a few more questions.

Scenarion:-
User opens main form and moves to a new record and fills in the data ( inc. the prime Key ). User then wants to fill in data on the first suplimentary form ( called ? frmOne ? )
How do you propose that they do this ?
Once this data is added - how do you propose that they move to the next suplimentary form ? ( Beck to Main and off to the second or directly from One to Two etc.

Are these suplimentary forms too large to set up as subForms on the main form ?



G LS

 

[chubby]

Your right, there are no subforms.
Every table has it's own form attached to it. Then every table is linked by Case #. At present, I had my one of the end users add about 30 new records to each table. That meant adding 30 case numbers to each table (6), then I re-linked the tables. There has to be a better way...