Specifying record source in a form control based on selection in another control in same form

[skylark_on_siege]

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

Any assistance would be greatly appreciated!

 

[TonyT]

Hi Skylark,

You might want to rethink your table designs, to allow for better linking
together of Units and their owners in particular, as you aren't showing any
links at present, and also a way to resolve the either/or nature of owner or
partnership.
Also I would suggest you rename all the ID fields to UnitID, OwnerID etc etc
as this will cause you headaches in the future!

anyway, to answer your question;

Private Sub cbxOwnerShipType_AfterUpdate()
Dim strSQL as String

If Me.cbxOwnerShipType = "Single" Then
strSQL = "SELECT * FROM tblOwners"
ElseIf Me.cbxOwnerShipType = "Multiple" Then
strSQL = "SELECT * FROM tblPartnerships"
Else: strSQL = ""
End If

Me.cbxOwnerLookUp.RowSource = strSQL

End Sub

hope that helps,

TonyT..

 

[Marshall Barton]

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

You definitly should rethink the table relationships.
Instead of thinking about single owners and partnership
owners, try thinking in terms of owner. An owner is an
owner, the percentage of ownership may vary, but still be an
owner.

OTTOMH, I suggest that table tblOwners include the
percentage field and a foreign key (RecNum) to
tblPartnerships (Null if not in a partnership). I don't see
how tblPartnerMembers does anything besides get in the way.

 

[Skylark]

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.

Thanks!

-Skylark

 

[Marshall Barton]

Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.

 

[Skylark]

It has been bothering me, as well, and I am certainly open to
suggestions...I have been wracking my brain on this for over a week. I
have tried a number of alternatives, and I seem to wind up back with
the same type of problem. Perhaps if I explain, it would help.
Basically I have a table Owners, that has no dependencies - it is the
first thing that must be entered in the database when we receive a new
management contract. Just about every other table in the database (with
the exception of category tables, and selection lists) has some
dependency on the Owner table.

Here is an example:

We have a management agreement for the property at 123 Pleasant St.
John Smith has a 50% ownership share in the property, and Jane Jones
has a 50% share.

We have another management agreement for 456 Shady Ln., but this
property is solely owned by John Smith.

So, there would be a record for both John Smith, and Jane Jones in the
Owners table. However, there would be a record in the Partnership table
(little more than an ID, a friendly name), and a third table
Partnership members with a unique record for each partner (multiple
field primary key consisting of Owner ID, and PartnershipID).

In the Paradox database, there is a list box in the Units form, that
occurs before you reach the Owners lookup field. The list box has
choices of Single Owner or Partnership. Once that selection has been
made, when you enter the Owner lookup field in the Units form, you are
presented with a list of records from the Owners table or a list of
records from the Partnership table, respectively. Since I need to be
able to report on Units by their ownership interests, I need to
reproduce this functionality in the Access database as well.

Any thoughts are appreciated.

-Skylark

Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.
--
Marsh
MVP [MS Access]

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.

 

[Marshall Barton]

Let me try to express my concerns this way. With a single
field in the units table for owner id (and presumable an
owner type field), how will you create a query that joins
the units table to the owners or partnerships table. The
owner id field is not well defined and can not be used as a
foreign key. I think you need to have two fields, one that
is a foreign key to owners and another that is the foreign
key to parnerships. Your form will have to deal with how
you switch between them (based on the owner type combo box),
either by enabling or making visible one or the other text
box.

 

[TonyT]

Hi Skylark,

Could you not just have your owners table, and then the property table
linked to it, with the field percentage owned, that way John Smith occurs
just once in owners, and property table has 3 records, 2 for 123 Pleasant
Str. OwnerID & 50% for each owner and 1 record for 456 Shady Lane at 100%
linked to John Smith again.
You should be able to ascertain all the information you want that way, I
think.

TonyT..

It has been bothering me, as well, and I am certainly open to
suggestions...I have been wracking my brain on this for over a week. I
have tried a number of alternatives, and I seem to wind up back with
the same type of problem. Perhaps if I explain, it would help.
Basically I have a table Owners, that has no dependencies - it is the
first thing that must be entered in the database when we receive a new
management contract. Just about every other table in the database (with
the exception of category tables, and selection lists) has some
dependency on the Owner table.

Here is an example:

We have a management agreement for the property at 123 Pleasant St.
John Smith has a 50% ownership share in the property, and Jane Jones
has a 50% share.

We have another management agreement for 456 Shady Ln., but this
property is solely owned by John Smith.

So, there would be a record for both John Smith, and Jane Jones in the
Owners table. However, there would be a record in the Partnership table
(little more than an ID, a friendly name), and a third table
Partnership members with a unique record for each partner (multiple
field primary key consisting of Owner ID, and PartnershipID).

In the Paradox database, there is a list box in the Units form, that
occurs before you reach the Owners lookup field. The list box has
choices of Single Owner or Partnership. Once that selection has been
made, when you enter the Owner lookup field in the Units form, you are
presented with a list of records from the Owners table or a list of
records from the Partnership table, respectively. Since I need to be
able to report on Units by their ownership interests, I need to
reproduce this functionality in the Access database as well.

Any thoughts are appreciated.

-Skylark

Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.
--
Marsh
MVP [MS Access]

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.


(e-mail address removed) wrote:

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

Marshall Barton wrote:
You definitly should rethink the table relationships.
Instead of thinking about single owners and partnership
owners, try thinking in terms of owner. An owner is an
owner, the percentage of ownership may vary, but still be an
owner.

OTTOMH, I suggest that table tblOwners include the
percentage field and a foreign key (RecNum) to
tblPartnerships (Null if not in a partnership). I don't see
how tblPartnerMembers does anything besides get in the way.

 

[Skylark]

Well, this would cause me a problem in the current design as Units is
related to Tenants, by UnitID as a foreign key in each Tenant record.
Having multiple instances of the Unit to specify Ownership shares would
require the Tenant-Units relationship to change.

However, since in this version of the database, as I do not need to
treat the "Partnership" as a discreet entity for accounting purposes
(which was required in one of the former databases) one of the options
I had considered was to show individual ownership records linked to the
Unit by share, but it seems I would still need a 3rd table
(OwnershipShares) to achieve this.

I was thinking something along the lines of:

tblOwners
OwnerID (primary key)

tblUnits
UnitID (primary key)

tblOwnershipShares
UnitID - foreign key to Units table
OwnerID - foreign key to Owners table
%Ownership

tblTenants
TenantID (primary key)
UnitID (foreign key to Units table)

with the primary key of tblOwnershipShares made up of the 2 fields
UnitID + OwnerID

Then I could use a subform which lists the individual ownership
interests in my Units form. Tenants would only be indirectly related to
Owners, by the Units table.

Is this a valid concept or will it just get me into more trouble?

Again, the help is much appreciated.

-Skylark

Hi Skylark,

Could you not just have your owners table, and then the property table
linked to it, with the field percentage owned, that way John Smith occurs
just once in owners, and property table has 3 records, 2 for 123 Pleasant
Str. OwnerID & 50% for each owner and 1 record for 456 Shady Lane at 100%
linked to John Smith again.
You should be able to ascertain all the information you want that way, I
think.

TonyT..

It has been bothering me, as well, and I am certainly open to
suggestions...I have been wracking my brain on this for over a week. I
have tried a number of alternatives, and I seem to wind up back with
the same type of problem. Perhaps if I explain, it would help.
Basically I have a table Owners, that has no dependencies - it is the
first thing that must be entered in the database when we receive a new
management contract. Just about every other table in the database (with
the exception of category tables, and selection lists) has some
dependency on the Owner table.

Here is an example:

We have a management agreement for the property at 123 Pleasant St.
John Smith has a 50% ownership share in the property, and Jane Jones
has a 50% share.

We have another management agreement for 456 Shady Ln., but this
property is solely owned by John Smith.

So, there would be a record for both John Smith, and Jane Jones in the
Owners table. However, there would be a record in the Partnership table
(little more than an ID, a friendly name), and a third table
Partnership members with a unique record for each partner (multiple
field primary key consisting of Owner ID, and PartnershipID).

In the Paradox database, there is a list box in the Units form, that
occurs before you reach the Owners lookup field. The list box has
choices of Single Owner or Partnership. Once that selection has been
made, when you enter the Owner lookup field in the Units form, you are
presented with a list of records from the Owners table or a list of
records from the Partnership table, respectively. Since I need to be
able to report on Units by their ownership interests, I need to
reproduce this functionality in the Access database as well.

Any thoughts are appreciated.

-Skylark

Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.
--
Marsh
MVP [MS Access]


Skylark wrote:

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.


(e-mail address removed) wrote:

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

Marshall Barton wrote:
You definitly should rethink the table relationships.
Instead of thinking about single owners and partnership
owners, try thinking in terms of owner. An owner is an
owner, the percentage of ownership may vary, but still be an
owner.

OTTOMH, I suggest that table tblOwners include the
percentage field and a foreign key (RecNum) to
tblPartnerships (Null if not in a partnership). I don't see
how tblPartnerMembers does anything besides get in the way.

 

[Marshall Barton]

Again, off the top of my head.

I like this table design. The foreign keys are all well
defined so you can get from one entity to any related entity
to answer just about any question about the related data.

But the proof in this pudding is if it answers your original
question ;-)
--
Marsh
MVP [MS Access]

Well, this would cause me a problem in the current design as Units is
related to Tenants, by UnitID as a foreign key in each Tenant record.
Having multiple instances of the Unit to specify Ownership shares would
require the Tenant-Units relationship to change.

However, since in this version of the database, as I do not need to
treat the "Partnership" as a discreet entity for accounting purposes
(which was required in one of the former databases) one of the options
I had considered was to show individual ownership records linked to the
Unit by share, but it seems I would still need a 3rd table
(OwnershipShares) to achieve this.

I was thinking something along the lines of:

tblOwners
OwnerID (primary key)

tblUnits
UnitID (primary key)

tblOwnershipShares
UnitID - foreign key to Units table
OwnerID - foreign key to Owners table
%Ownership

tblTenants
TenantID (primary key)
UnitID (foreign key to Units table)

with the primary key of tblOwnershipShares made up of the 2 fields
UnitID + OwnerID

Then I could use a subform which lists the individual ownership
interests in my Units form. Tenants would only be indirectly related to
Owners, by the Units table.

Is this a valid concept or will it just get me into more trouble?

Again, the help is much appreciated.

-Skylark

Hi Skylark,

Could you not just have your owners table, and then the property table
linked to it, with the field percentage owned, that way John Smith occurs
just once in owners, and property table has 3 records, 2 for 123 Pleasant
Str. OwnerID & 50% for each owner and 1 record for 456 Shady Lane at 100%
linked to John Smith again.
You should be able to ascertain all the information you want that way, I
think.

TonyT..

It has been bothering me, as well, and I am certainly open to
suggestions...I have been wracking my brain on this for over a week. I
have tried a number of alternatives, and I seem to wind up back with
the same type of problem. Perhaps if I explain, it would help.
Basically I have a table Owners, that has no dependencies - it is the
first thing that must be entered in the database when we receive a new
management contract. Just about every other table in the database (with
the exception of category tables, and selection lists) has some
dependency on the Owner table.

Here is an example:

We have a management agreement for the property at 123 Pleasant St.
John Smith has a 50% ownership share in the property, and Jane Jones
has a 50% share.

We have another management agreement for 456 Shady Ln., but this
property is solely owned by John Smith.

So, there would be a record for both John Smith, and Jane Jones in the
Owners table. However, there would be a record in the Partnership table
(little more than an ID, a friendly name), and a third table
Partnership members with a unique record for each partner (multiple
field primary key consisting of Owner ID, and PartnershipID).

In the Paradox database, there is a list box in the Units form, that
occurs before you reach the Owners lookup field. The list box has
choices of Single Owner or Partnership. Once that selection has been
made, when you enter the Owner lookup field in the Units form, you are
presented with a list of records from the Owners table or a list of
records from the Partnership table, respectively. Since I need to be
able to report on Units by their ownership interests, I need to
reproduce this functionality in the Access database as well.

Any thoughts are appreciated.

-Skylark

Marshall Barton wrote:
Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.
--
Marsh
MVP [MS Access]


Skylark wrote:

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.


(e-mail address removed) wrote:

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

Marshall Barton wrote:
You definitly should rethink the table relationships.
Instead of thinking about single owners and partnership
owners, try thinking in terms of owner. An owner is an
owner, the percentage of ownership may vary, but still be an
owner.

OTTOMH, I suggest that table tblOwners include the
percentage field and a foreign key (RecNum) to
tblPartnerships (Null if not in a partnership). I don't see
how tblPartnerMembers does anything besides get in the way.