Accessing multiple forms with the same Primary Key.

[wtucker]

I'm running into an issue with the database I'm trying to create. I have about 200 fields (and thousands of records), so I split them into about 8 different tables based on category, which all share the same PK. I only want to have to write the PK once, and store it in each table.
I've been trying to create a form with information from multiple tables (I'll need to make about 6 of these), but I've been getting the error "The Microsoft Access database engine cannot find a record in the table 'Contracts' with key matching field(s) 'ClientID'".

I originally wanted to have a main form with buttons to open other forms to the same record.
It would work like this:
Open main form.
For new customer, fill in information (including PK based on unique client ID), click buttons to open additional forms to fill out for new record (but same PK).
For existing customer, click buttons to open additional forms to correct record.



I know there has to be a better way to do this, but I'm lacking in technical experience. Hopefully someone can help me solve this in terms I can understand.

[ranman256]

docmd.openform "form1",,,"[key]=" & me.txtID
docmd.openform "form2",,,"[key]=" & me.txtID

[John_G]

Try using a main form - subform setup, with each of the subforms on its own tab, being sure to set the parent - child links to the PK field in each subform.

[ssanfu]

I know there has to be a better way to do this, but I'm lacking in technical experience. Hopefully someone can help me solve this in terms I can understand.

Maybe you could post a picture of the relationships or your dB with just a few records in each table.
It sounds like you have non-normalized table structures.

<>snip I have about 200 fields (and thousands of records), so I split them into about 8 different tables based on category, which all share the same PK. I only want to have to write the PK once, and store it in each table.

So you have a main table with 8 related tables? Each of the 8 tables has a foreign key field that links to the main table PK field?

[June7]

This is 1-to-1 relationship and a form/subform structure is still relevant. As John_G suggested, subforms can be placed on pages of Tab control or use a Navigation Form. The forms can all be in Single View.

I agree that 200 fields is a lot and the db may not be normalized. However, 1-to-1 might be justified. I have a database that uses 1-to-1 relationship extensively. It is to track laboratory test data of construction materials. Each sample can have multiple tests designated but only one of each test. Each test has its own table to hold the raw data used to calculate reported results. We have over 200 tests to select from (usually less than a dozen are required). The primary purpose of database is to facilitate generation of test results reports. Very little actual aggregate data analysis takes place. This structure makes generating the results reports easier. The output varies in structure according to material being tested so there is no 1-size-fits-all report. I do have to graph some data and all of the graphs require use of UNION queries to rearrange data. Of course, on the rare occasion a new test is defined, modifying the db to accommodate is not a simple effort.