I just now read in the help that you cannot modify the LinkChildFields and LinkMasterFields properties at runtime. It actually looks like it would let you, if only you could change them both at once. (The error I got was about changing the number of linked fields.)
Now that I know this, I'm a bit frustrated because my table design nearly requires this method. I hope you'll bear with me and let me explain:
I have a table called Entities. Everything goes in here: users, customers, the System (my company), vendors.
Each entity record has as many fields as are common to each kind of entity, such as DisplayName, DateCreated, Enabled, and that sort of thing.
Each different kind of entity has its own table with its custom information: Login name and password for users, customer addresses, etc. You get the idea.
The reason I designed the database this way is because
1) my forms have several places where they need to capture a value that could be a different kind of entity. For example, an issue can be assigned to a specific user in my company but could also be assigned to one of our vendors. There are others similar to this.
2) I can specify an owner for each entity and end up with a hierarchical tree: these users belong to this company, but these other users belong to this other company. (This is a valid scenario, we hire 3rd-party companies to do programming and they could conceivably need access as users.)
3) This database structure is very tolerant of new additions and new kinds of entity types when compared to other methods I could envision. For example, all the forms which do a lookup to the entities table will automatically see the new kind without a single code change. Changes that are required are more likely to be in the queries and form/listbox/combobox sources to include or exclude the new type, but how quick a change is that?
My main data entry forms are working great because for the most part one simply selects the entity one wants from the entities table. Forms that deal with a specific kind of entity pull from the specific table.
However, now I am working on the "entity maintenance" forms. I have an unbound listbox with all entities in it. Later I will add options to sort and filter this box. In the meantime, when an item is selected, I had planned to switch the subform to the correct form which contains all the data entry controls specific to that kind of entity.
It is actually working for 3 of my 4 kinds of entities, except that I'm running into a problem with Access's oh-so-wonderful auto-link feature. (This is Access 97, and no I can't use 2000 right now.) Even though I set the LinkMasterFields and LinkChildFields properties correctly at design-time (and the link field names are uniform across all my tables, "EntityID" for master and "Entity" for Child), when I change the SourceObject property at runtime, Access butts in and changes these based on the relationships in my database. They are fairly complex and I end up with incorrect values for linking. Maybe I can prune these to end up with what I want, but what I'd rather have is to stop Access 97 from automatically (and incorrectly) filling in these properties. Can I do this? Should I bother trying deleting the relationships entirely to see if Access then leaves well enough alone?
Should I put a subform for each entity type on my form, and simply hide the incorrect ones?
Should I make four separate forms to edit all the data about an entity, including the common data that sits in my Entities table, one for each entity type? So much for going to one place to see all entities.
Did I make a mistake in my fundamental database design to begin with?
Anyone? Anyone?
-E²
Now that I know this, I'm a bit frustrated because my table design nearly requires this method. I hope you'll bear with me and let me explain:
I have a table called Entities. Everything goes in here: users, customers, the System (my company), vendors.
Each entity record has as many fields as are common to each kind of entity, such as DisplayName, DateCreated, Enabled, and that sort of thing.
Each different kind of entity has its own table with its custom information: Login name and password for users, customer addresses, etc. You get the idea.
The reason I designed the database this way is because
1) my forms have several places where they need to capture a value that could be a different kind of entity. For example, an issue can be assigned to a specific user in my company but could also be assigned to one of our vendors. There are others similar to this.
2) I can specify an owner for each entity and end up with a hierarchical tree: these users belong to this company, but these other users belong to this other company. (This is a valid scenario, we hire 3rd-party companies to do programming and they could conceivably need access as users.)
3) This database structure is very tolerant of new additions and new kinds of entity types when compared to other methods I could envision. For example, all the forms which do a lookup to the entities table will automatically see the new kind without a single code change. Changes that are required are more likely to be in the queries and form/listbox/combobox sources to include or exclude the new type, but how quick a change is that?
My main data entry forms are working great because for the most part one simply selects the entity one wants from the entities table. Forms that deal with a specific kind of entity pull from the specific table.
However, now I am working on the "entity maintenance" forms. I have an unbound listbox with all entities in it. Later I will add options to sort and filter this box. In the meantime, when an item is selected, I had planned to switch the subform to the correct form which contains all the data entry controls specific to that kind of entity.
It is actually working for 3 of my 4 kinds of entities, except that I'm running into a problem with Access's oh-so-wonderful auto-link feature. (This is Access 97, and no I can't use 2000 right now.) Even though I set the LinkMasterFields and LinkChildFields properties correctly at design-time (and the link field names are uniform across all my tables, "EntityID" for master and "Entity" for Child), when I change the SourceObject property at runtime, Access butts in and changes these based on the relationships in my database. They are fairly complex and I end up with incorrect values for linking. Maybe I can prune these to end up with what I want, but what I'd rather have is to stop Access 97 from automatically (and incorrectly) filling in these properties. Can I do this? Should I bother trying deleting the relationships entirely to see if Access then leaves well enough alone?
Should I put a subform for each entity type on my form, and simply hide the incorrect ones?
Should I make four separate forms to edit all the data about an entity, including the common data that sits in my Entities table, one for each entity type? So much for going to one place to see all entities.
Did I make a mistake in my fundamental database design to begin with?
Anyone? Anyone?
-E²
