Combo Boxes

[Gary]

Is there a way that I can make a combo box pull data from multiple fields.

Thanks

 

[Larry Linson]

Is there a way that I can make a combo box pull data from multiple fields.

Surely... the Row Source of a Combo Box may contain many Fields. But it can
only be _bound_ (Control Source) to one. If you want to set the values from
other Fields in the selected Record of the Combo's Row Source, you can do it
with VBA code in the AfterUpdate event of the Combo, or the BeforeUpdate
event of the Form.

Larry Linson
Microsoft Access MVP

Larry Linson
Microsoft Access MVP

 

[Graham Mandeno]

Hi Gary

You mean you want values from multiple fields to appear in the one column?

Like: Firstname="Fred", Lastname="Smith", Combo shows: "Fred Smith"

To do this you concatenate the two fields using a string expression in your
RowSource query. For example:

Select Firstname & " " & Lastname as Fullname from Clients;

Of course, combo boxes can and usually do have more than one column, and
that is reflected by the number of columns (fields) in your rowsource query.
For example:

Select ClientID, Firstname & " " & Lastname as Fullname, City, Phone
from Clients order by Firstname, Lastname;

This would give you four columns. You set the ColumnCount property to
match.

The BoundColumn property indicates which column will hold the Value of the
combo box.

The ColumnWidths property sets the widths of the individual columns. A
width of zero hides the column completely (common for such fields as
autonumber IDs). The first column with a non-zero width will be the one
displayed when the combo is not dropped down.

The Column property of the combo returns the data in any column - for
example, with the above RowSource, cboName.Column(2) would return the City
(third column) of the currently selected client (note the numbering starts
at 0).

 

[Gary]

Larry,
What I am trying to do is search three different fields that are labeled
"Head Tenant" ; "Co-Tenant" & Co-Tenant" from one combo box and have the
result entered into a form. The way I am doing it now is I have a box for
each field and if I don't know which type of tenant I am looking for I might
have to do this 3 time till I find the correct field that the name resides
in. I would sure save a lot of work if I could search all 3 fields a one
time. The other issue I thought about was what if I had several people with
the same name...could I be made to show all of them I could choose the
correct one. I thought I could do this by have a multiple column combo box,
and have the second column searchable by SS#.

Thanks for any help you can give me.
Gary

 

[Gary]

Graham,
What I am trying to do is search three different fields that are labeled
"Head Tenant" ; "Co-Tenant" & Co-Tenant" from one combo box and have the
result entered into a form. The way I am doing it now is I have a box for
each field and if I don't know which type of tenant I am looking for I might
have to do this 3 time till I find the correct field that the name resides
in. I would sure save a lot of work if I could search all 3 fields a one
time. The other issue I thought about was what if I had several people with
the same name...could I be made to show all of them I could choose the
correct one. I thought I could do this by have a multiple column combo box,
and have the second column searchable by SS#.

Thanks for any help you can give me.
Gary

Hi Gary

You mean you want values from multiple fields to appear in the one column?

Like: Firstname="Fred", Lastname="Smith", Combo shows: "Fred Smith"

To do this you concatenate the two fields using a string expression in
your RowSource query. For example:

Select Firstname & " " & Lastname as Fullname from Clients;

Of course, combo boxes can and usually do have more than one column, and
that is reflected by the number of columns (fields) in your rowsource
query. For example:

Select ClientID, Firstname & " " & Lastname as Fullname, City, Phone
from Clients order by Firstname, Lastname;

This would give you four columns. You set the ColumnCount property to
match.

The BoundColumn property indicates which column will hold the Value of the
combo box.

The ColumnWidths property sets the widths of the individual columns. A
width of zero hides the column completely (common for such fields as
autonumber IDs). The first column with a non-zero width will be the one
displayed when the combo is not dropped down.

The Column property of the combo returns the data in any column - for
example, with the above RowSource, cboName.Column(2) would return the City
(third column) of the currently selected client (note the numbering starts
at 0).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Is there a way that I can make a combo box pull data from multiple
fields.

Thanks

 

[Larry Linson]

The values in the Row Source come from a Query, SQL Statement, or a Table
(there's also a Value List but that doesn't apply in this case). You can
create three Queries to retrieve the names in each of the Fields of
interest, use the UNION ALL operator to, er, "unite" them, and use that as
the Row Source...

You can't "search" more than one Field in a Combo, but in this case, you
have created the new Field that will hold whatever kind of tenant was
retrieved. You can still bring along (1) the ID Field to be "bound" and (2)
other Fields of interest -- by appropriate setting of Column Widths in the
Properties of the Combo, those additional fields will show up in the list
from which you select. Once you have selected, they are accessible as the
Column properties of the List or Combo Box.

The fact that you have essentially the same information in three separate
Fields should raise a red flag for you to carefully review and probably
revise your Table design / layout.

Chances are reasonably good that instead of having three tenant fields, you
should have a related Tenant Table, identifying each record in a Data Field
as being the different types of tenant.

Larry Linson
Microsoft Access MVP

 

[Graham Mandeno]

Yes... interesting... and then what do you do if a property has more than
two co-tenants? ...and what is one individual is a head tenant or co-tenant
for several different properties?

Ah, the joys of database design

What you have here is a many-to-many relationship between properties and
tenants.

The proper way to do this, and avoid tears/suicide in the future, is to have
separate tables for these two entities. Properties contains ONLY data which
are attributes of the property, including a primary key (unique) which could
be an AutoNumber field if you don't have a "natural" unique key. Tenants
contains ONLY data which are attributes of the tenant, such as name,
address, contact info, credit rating, SSN, etc. This table also needs a
primary key, for which you could use the SSN, but I have heard there are
some problems with doing that (I'm not from the USA so I don't know first
hand) so I suggest you use an AutoNumber instead.

Now, to relate tenants to properties you need what is called a "junction
table". This table contains a PropertyID (primary key) value and a TenantID
(primary key value) and any other data that pertains to that particular
tenancy - for example, whether or not this is the "head tenant", date of
sign-up, etc.

The Properties table has a one-to-many relationship to the TenantsProperties
table via PropertyID. The Tenants table has a one-to-many relationship to
the TenantsProperties table via TenantID.

Now you can easily query all the tenants of a particular property, even if
there are 10 of them, and you can easily query what properties a particular
tenant holds, even if he is the head tenant for one and the 16th co-tenant
for another.

To get back to your combo box, all you need is a simple query based on your
Tenants table. Form there you can list all the related property records.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
What I am trying to do is search three different fields that are labeled
"Head Tenant" ; "Co-Tenant" & Co-Tenant" from one combo box and have the
result entered into a form. The way I am doing it now is I have a box for
each field and if I don't know which type of tenant I am looking for I
might have to do this 3 time till I find the correct field that the name
resides in. I would sure save a lot of work if I could search all 3
fields a one time. The other issue I thought about was what if I had
several people with the same name...could I be made to show all of them I
could choose the correct one. I thought I could do this by have a
multiple column combo box, and have the second column searchable by SS#.

Thanks for any help you can give me.
Gary

Hi Gary

You mean you want values from multiple fields to appear in the one
column?

Like: Firstname="Fred", Lastname="Smith", Combo shows: "Fred Smith"

To do this you concatenate the two fields using a string expression in
your RowSource query. For example:

Select Firstname & " " & Lastname as Fullname from Clients;

Of course, combo boxes can and usually do have more than one column, and
that is reflected by the number of columns (fields) in your rowsource
query. For example:

Select ClientID, Firstname & " " & Lastname as Fullname, City, Phone
from Clients order by Firstname, Lastname;

This would give you four columns. You set the ColumnCount property to
match.

The BoundColumn property indicates which column will hold the Value of
the combo box.

The ColumnWidths property sets the widths of the individual columns. A
width of zero hides the column completely (common for such fields as
autonumber IDs). The first column with a non-zero width will be the one
displayed when the combo is not dropped down.

The Column property of the combo returns the data in any column - for
example, with the above RowSource, cboName.Column(2) would return the
City (third column) of the currently selected client (note the numbering
starts at 0).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Is there a way that I can make a combo box pull data from multiple
fields.

Thanks

 

[Gary]

Larry,
I really appreciate your help, Since I have taught myself this program thru
books and trial and error, there is still a lot for me to learn. Books
don't tell you all. I basically understand what you are saying (although-I
won't know for sure until I try it, but then again thats how I got as far as
I did, just by trying) except the last part. Not sure what a related table
is, hopefully the books will explain. This is an apartment complex and each
apartment could have anywhere from 1 tenant (head Tenant) with up to 2
addtional co-tenants (thats as far as they take the paperwork), although
they do keep track of household members, but they are listed separately,
kind of like the tenants, (HHM1, HHM2,etc.). I don't want to really take up
more of your time, so again, I really want to thank you. I never realized
you could get this kind of help.

Gary