Linked Tables

[davegauthier]

I have 7 tables to track client data. The 1st table uses AutoNumber to create the 'Client ID' number. The other 6 tables are linked to this table, and in turn each other, through the 'Client ID' field.

All information entered in all 7 tables is done so on one form. This form has a tab control to separate the input fields.

The problem is this. If I enter information in the form, but no field in one or more of the tables gets filled (table gets left completely empty because the fields were not relevant to that record), the record does not save itself to where it can be viewed in the form anymore.

I have programmed a command button to copy the 'Client ID' from the 1st table to the rest, and so far this works, but I would like to have this done automatically without any action required by the user. I tried the 'OnDirty' for the ClientID field on the form, but I do not think that works. Mybe 'OnUpdate'?

Any suggestions?

 

[llkhoutx]

If there's no data for a subform, I would not expect or want a record to be saved.

Have you created Relationships for your tables.

I don't think records will be created in subforms with no data in that instance eother.

If there's no data, why do you need or want a record?

You say:

If I enter information in the form, but no field in one or more of the tables gets filled (table gets left completely empty because the fields were not relevant to that record), the record does not save itself to where it can be viewed in the form anymore.

That indicates to me that the subforms are not properly linked to the parent and need to be requeried when the parent record changes.

 

[Pat Hartman]

It is extremely rare to have two tables in a 1-1 relationship. Seven tables smacks of a design flaw. Do some reading on normalization or post your structure here and we'll give you some help with it.

 

[davegauthier]

First, sorry for the double-post.

It was advised to me to not have just one table with all the information stored, because once there were many records in that table, it entire database would run slower. So, I split the data into 7 sub-categories.

The problem is, when I enter some info that gets stored in 3 of the 7 tables, then exit the database, then return to the database, the record does not show in the form anymore. The only remedy I have found, through trial and error, is to take steps necessary to make sure each table has a the same ClientID. This also allows me to add data that can be stored in the other tables at a later time.

How can I post my structure to be looked at?

Thanks, Dave

 

[Pat Hartman]

The advice to split the table into 7 tables was faulty. That doesn't normalize the data. If you have so many columns that you feel the need to split the data, you almost certainly have repeating groups and THOSE need to be split out.

The reason that your query isn't working properly is because it is using inner joins. Change the query to use Left Joins. Also, the tables should not be linked - a-->b-->c-->d, etc. There should be six separate relationships:
a-->b
a-->c
a-->d
etc.

 

[davegauthier]

I can asure you there is no repeated data. There is simply a lot of data needed for each client.

The relationships are set up
a-->b
a-->c
a-->d
a-->e
a-->f
a-->g

How can I screen shot my Relationships window to post and be sure it is done properly?

With regards to the query, are you referring to the SQL Query for my form recordsource? What is the best way to change from Inner Joins to Left Joins?

 

[Pat Hartman]

How can I screen shot my Relationships window to post and be sure it is done properly?

I use SnagIt to capture screen images. It is inexpensive and filled with useful features.

What is the best way to change from Inner Joins to Left Joins?

In SQL view, change "Inner" to "Left". In QBE view, double click on the center of the join line. You'll see a box with three choices. If you get the properties box instead of the join box, close it and try again.