Form field Control Source

[Timothy Andrepont]

I have developed a Mainform with subforms. All is working except an auto-fill into a field that is not being recognized.

I have attached a zipped fill with snap shots and the database.

Can you explain where I went wrong placing commands?

You help will be greatly appreciated.

Timothy

 

[Cronk]

Is there any reason you use the name of assets as the primary key? It would be normal practice that an Asset table would have AssetID autonumber primary key and Asset name text

I find it very difficult to follow your design, when you use ID in text fields and do not have consistency in the naming of primary and foreign keys.

That aside, your relationships give the relationship between tAssInspec and tbl_01_BS as AssetID and MS_PS_ID, yet the linkage between the form and sub form uses an additional field.

 

[Timothy Andrepont]

Cronk,

1. I seems the problem was generated by longer text field size in tAssets than in linked tables. The minute the field in tAssets was longer than text fields size than the linked tables the problem surfaced. I have made sure text field size were the same for linked fields.


2. "Is there any reason you use the name of assets as the primary key? It would be normal practice that an Asset table would have AssetID autonumber primary key and Asset name text."

The unique asset ID is text in other database and records. It has been suggested that I make the database primary key of tAsset Autonumber. This would of course mean I would have to change all linked foreign key to number and would have to create a new field in tAssets for the unique text ID. What do you suggest?
​

3. As I work to begin cleanup, I noticed that my numbered tables (01 through 13) do not all have the same relationship with the tAssets.PS_ID. In tables 06 through 13 it is "one to one" while in tables 01 and 04 it is the expected "one to many". I show this in attatched Zip (20131123_01.pdf). I have also attached the updated database. Note: I am presently only working on Forms and Relationships. Other portions of this structure are just their.

You help is greatly appreciated.

Timothy

 

[Cronk]

I know there is not a small amount of pain now to change to ID's but believe me, it will avoid so much more in the future. You will have cases where a user wants to change to change the name of the asset for whatever whim and then your referential integrity is shot. Use hidden ID numbers.

It's generally best to get your table design including relationships clearly designed before commencing form design which if the table design is "right" just falls into place.

One to one relationships beg the question of why there are separate tables.

A table should represent one entity and all its attributes using IDs to lookup tables if the entries share a sub set of a common set. For simplicity now and in the future, I wlll name the table with the name of the entity, the PK and FK's in tables will always be named EntityNameID.

Where an entity has an attribute with more than one value, store the values in a related table.

I see no reason why you need include any number in the table name. Eg tbl_06_PC instead of tblPC or even better tblAssetWeights. Then again PC might mean something specific to you.

My views are based on some years of developing systems where I come back to them years later and I've forgotten the detail. Simple design and clear nomenclature helps enormously.

I have no idea what secDetail13_ID is and can offer no comment on its relationship.

 

[Timothy Andrepont]

Cronk,

Thanks for your learned wisdom. I plan this week to make the changes you suggested.

Do you have any explanation for the relationships variance?

3. As I work to begin cleanup, I noticed that my numbered tables (01 through 13) do not all have the same relationship with the tAssets.PS_ID. In tables 06 through 13 it is "one to one" while in tables 01 and 04 it is the expected "one to many". I show this in attatched Zip (20131123_01.pdf).
​

Thanks again for your help.

Timothy

 

[Timothy Andrepont]

Cronk,

As I am optimizing the database-structure, may I ask another question?

Table, tInspection, currently has two fields: Visit_ID and VisitNote. (Any other fields in this table will be removed.) I have so far made Visit_ID the Primary Key and it is a date field. All records in this data base will have unique dates.

Thanks for your continued guidance.

Timothy