Updating/editing two tables in forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help: I am trying to manage land and properties in a small town.
I have 2 tables: Owners and Property
One owner may have one property or own several properties

The form: frmOwnersAndProperty has both tables on it: The Main form is
Owners with the subform Property attached

If the owner has only 1 property and sells it I just keep the same OwnerID
number and type in a new owner, but if the Owner has several properties it
gets cumbersome. I have to get out of the form, got to the Owners table and
add an new owner then close that table go to the Property table and find the
property that has sold and find the new owner in the drop down list. Is
there any way that I can make this process easier. If I could somehow click
in the subform at that one perticular Lot or CondoUnit that has sold (i.e.
Lot 345), and then that would take me to another screen where I could enter
the new owners for that perticular Lot or CondoUnit. I have no clue how to
go about doing this or if it is even possible. Many Thanks
 
well, you need to think first about what entities you need to track in your
database. since one person may own several properties, i would be inclined
to track Owners as separately from Properties - that is, a person is a
distinct entity, not an attribute of a Property. that being the case, it
doesn't matter whether a person owns one property or twenty - you should
have one record in the Owners table for each person, and NOT change one
person's record to another person. remember the record represents a "real"
person, who does not turn into somebody else when property changes hands.

recommend three tables, as

tblOwners
OwnerID (primary key)
FirstName
LastName
(other fields that describe an owner specifically - not property owned.)

tblProperties
PropertyID (primary key)
StreetAddress
LotNumber
(whatever fields specifically describe a property - not the owner of a
property.)

tblPropertyOwners
PropOwnerID (primary key)
PropertyID (foreign key from tblProperties)
OwnerID (foreign key from tblOwners)
(any other fields that describe the circumstances of this person owning this
property)

your main form/subform setup will work, with some modifications. leave
tblOwners as the RecordSource of the main form. set the RecordSource of the
subform as tblPropertyOwners. set the subform control's LinkChildFields and
LinkMasterFields properties to OwnerID. (the first setting refers to the
foreign key field in the subform's table, and the second setting refers to
the primary key field in the main form's table.) in the subform, bind a
combo box or a listbox to the foreign key field PropertyID. set the
RowSource of the control to pull from tblProperties.

each property that a person owns will be listed as a separate record in the
subform. if the property is sold, the property record is deleted from the
previous owner subform records, and added to the new owner's subform
records.

from an "ease of data entry" standpoint, there are other ways to set the
forms up; exactly how you do it depends on how the business process "flows".
but don't confuse the choice of forms configuration with the requirements of
proper table design - they're separate issues.

hth
 
Back
Top