Default records tables design (1 Viewer)

[Bee]

Hi,

I have a table that stores default information called 'dat HouseType'. The information on this table appears on a form depending on the value users select from a combobox on another form; however, there will be situations where users need to change some of the fields for certain records in the default form. When that happens I don't want the changes to be stored in the 'dat HouseType' table because those records are default and should not change.

The combobox that pulls default records from 'dat HouseType' is currently storing the PK of this table in a main table called House. The PK links the two forms to show default records for each House selected in the main form.

I thought about creating another table that stores records pulled from 'dat HouseType'. Then take away the FK connection in between the 'dat HouseType' and the main table and make a new FK connection in between the new table and the main table. However, this will be storing every record saved and that will create a lot of repetition. I also can't think of how I am going to link this new table to the House table as my combo is currently pulling records from the 'dat HouseType' and it won't update the new table's FK in the main table.

Any suggestions will be very much appreciated, I can explain further if the above is vague.
B

 

[The_Doc_Man]

When that happens I don't want the changes to be stored in the 'dat HouseType' table because those records are default and should not change.

You are describing a variable default data set that depends on some selector value as to which of several defaults really applies to your problem.

My best guess would be to APPEND a record to your main storage table ("House"?) by copying the indicated record from your "House Type" table. The append query could be run behind the scenes in an OnClick event or an AfterUpdate of the control that selects the house type. You could trigger the append any of several ways. A Macro running a query could do it. A DoCmd.Execute pointing to a query could do it. In the latter two cases, the query would have to reference the open form's selector control.

OR... depending on the complexity of this form, if the number of fields to be copied is not very big, just open a recordset to the house-type table, find the record you want, and copy the fields to populate the table. Make the form based on "House" - not bound to "House Type" - then you never have to worry about the form updating your default data.

Be sure that you look into ways to cancel the current record just in case someone changes their mind. There are button wizards that would let you create a button-click control to undo the current record. (In essence, discard all changes.) Perhaps you would do best to disable updates of the record until someone clicks one of the special buttons. Disallow normal navigation on this form if you are in "new house entry mode" or something like that.

I also can't think of how I am going to link this new table to the House table as my combo is currently pulling records from the 'dat HouseType' and it won't update the new table's FK in the main table.

Since you are planning to change something, you probably do not really want a link in the true sense. You want a copy and then let the data in the main table diverge from its cloning source. Your confusion might stem from the fact that it LOOKS like you want a linkage - but it is really a temporary linkage at best. Once you have modified the contents of the form, you NO LONGER want that linkage. So consider ways to get the data that do not require a link at all. Instead, just pick up the data you want and plunk it down in your form.

 

[Bee]

You are describing a variable default data set that depends on some selector value as to which of several defaults really applies to your problem.

My best guess would be to APPEND a record to your main storage table ("House"?) by copying the indicated record from your "House Type" table. The append query could be run behind the scenes in an OnClick event or an AfterUpdate of the control that selects the house type. You could trigger the append any of several ways. A Macro running a query could do it. A DoCmd.Execute pointing to a query could do it. In the latter two cases, the query would have to reference the open form's selector control.

OR... depending on the complexity of this form, if the number of fields to be copied is not very big, just open a recordset to the house-type table, find the record you want, and copy the fields to populate the table. Make the form based on "House" - not bound to "House Type" - then you never have to worry about the form updating your default data.

Be sure that you look into ways to cancel the current record just in case someone changes their mind. There are button wizards that would let you create a button-click control to undo the current record. (In essence, discard all changes.) Perhaps you would do best to disable updates of the record until someone clicks one of the special buttons. Disallow normal navigation on this form if you are in "new house entry mode" or something like that.



Since you are planning to change something, you probably do not really want a link in the true sense. You want a copy and then let the data in the main table diverge from its cloning source. Your confusion might stem from the fact that it LOOKS like you want a linkage - but it is really a temporary linkage at best. Once you have modified the contents of the form, you NO LONGER want that linkage. So consider ways to get the data that do not require a link at all. Instead, just pick up the data you want and plunk it down in your form.

Thanks The Doc Man.
Would this approach eliminate repetition of records that don't need to change?

 

[The_Doc_Man]

What I am suggesting is that the records not needing to change are not kept in the same table as the ones that DO need to change. The invariant, or reference, records are in a separate table. They do not need to be changed (and should only rarely change for any reason). They act as templates for the records that DO need to change. No, you don't get rid of the records. But this is NOT repeat NOT a case of duplication. The reference table elements are merely starting points. You need no duplication here. The contents of the table that allows changes might start out as a duplicate for each new entry but as you make changes, that duplication is no longer real.

 

[Bee]

Cool, that clarifies it. Thanks.