how do i create a combo box?

[Guest]

Newbie User:
I am trying to create a form which contains a combo box. Now the combo box
is based on fields from one table that lists the employees names. I want to
be able to select the first and last name for an employee, and then have
Access include the corresponding information into the fields I list.
For example, If i select Miles Davis, I would want to see his telephone #,
location, and employee number automatically without having to select them
from a list of over 1500. Is there an easy way to do this?

thanks

 

[Guest]

Hi, DKS1.

A combo box has several key properties which control how it behaves:

Control Source
The field in the form's underlying RecordSource in which to store the user's
selection, as defined by the BoundColumn property. This is normally a
numeric key field that uniquely identifies the record.

RowSource
An SQL query statement that "populates" the rows of the combo box. This can
include both standard and calculated fields.

BoundColumn
The index, beginning with 1, of the column to be stored in the ControlSource
when the user makes the selection. Normally, this is set to 1 so that the
key field is stored.

ColumnWidths
How much space on the screen is allocated to each column in the drop-down
list, separated by semi-colons. When the user makes a selection, the first
non-zero width column is displayed.

So, in your case, your combo box is likely to be bound to (i.e., its
ControlSource is set to) a numeric EmployeeID field.

The RowSource, assuming that the last name and first name are separate
fields, and that you'd like to display them in "LastName, FirstName" format,
would be:

SELECT YourTable.EmpID, [LastName] & ", " & [FirstName] AS EmpName,
[PhoneNumber], [Location]
FROM YourTable
ORDER BY [LastName] & ", " & [FirstName];

You would replace "YourTable" with the name of your employee table, and
adjust the fieldnames as appropriate.

Set the BoundColumn to 1. Be sure that the ControlSource matches type with
the EmpID field. Normally both would be the Integer or AutoNumber type.

To display only the name in the dropdown, set the ColumnWidths property to:

0";x";0";0"

where x is large enough to display the longest name.

Finally, to display any of the other columns in a separate textbox, use the
combo box' Column property to set the textbox' ControlSource property:

=YourComboBox.Column(Index)

where Index is the column number, beginning with 0. For example, to display
the PhoneNumber in a textbox (the 3rd column), assuming your combo box is
named cboEmpName, set the textbox' ControlSource to:

=cboEmpName.Column(2)

If your EmpName field is a single field rather than separate Last and First
names, you can also use the wizard to create the combo box. Select "Hide Key
Field" to set the first Column Width to zero, then size the others
dynamically on screen, and follow the rest of the prompts. To enable the
wizard from form design view, select View, Toolbox, and toggle on the button
with the wand and stars.

Hope that helps.
Sprinks

 

[Guest]

Thanks a lot, but I am still a little confused. My table is called
Associates. The fields I want to use on my form are First, Last, Telephone,
ext, emp #. Right now I have two combo boxes. One for first name, the other
for last name. I would like that once I select a first name, i can select
from all of the last names that have the same first name. Once I have a
match, it puts in the telephone, ext and empl # in the other fields. If that
is what you explained already, thanks and sorry to seem like a nimwit.

Hi, DKS1.

A combo box has several key properties which control how it behaves:

Control Source
The field in the form's underlying RecordSource in which to store the user's
selection, as defined by the BoundColumn property. This is normally a
numeric key field that uniquely identifies the record.

RowSource
An SQL query statement that "populates" the rows of the combo box. This can
include both standard and calculated fields.

BoundColumn
The index, beginning with 1, of the column to be stored in the ControlSource
when the user makes the selection. Normally, this is set to 1 so that the
key field is stored.

ColumnWidths
How much space on the screen is allocated to each column in the drop-down
list, separated by semi-colons. When the user makes a selection, the first
non-zero width column is displayed.

So, in your case, your combo box is likely to be bound to (i.e., its
ControlSource is set to) a numeric EmployeeID field.

The RowSource, assuming that the last name and first name are separate
fields, and that you'd like to display them in "LastName, FirstName" format,
would be:

SELECT YourTable.EmpID, [LastName] & ", " & [FirstName] AS EmpName,
[PhoneNumber], [Location]
FROM YourTable
ORDER BY [LastName] & ", " & [FirstName];

You would replace "YourTable" with the name of your employee table, and
adjust the fieldnames as appropriate.

Set the BoundColumn to 1. Be sure that the ControlSource matches type with
the EmpID field. Normally both would be the Integer or AutoNumber type.

To display only the name in the dropdown, set the ColumnWidths property to:

0";x";0";0"

where x is large enough to display the longest name.

Finally, to display any of the other columns in a separate textbox, use the
combo box' Column property to set the textbox' ControlSource property:

=YourComboBox.Column(Index)

where Index is the column number, beginning with 0. For example, to display
the PhoneNumber in a textbox (the 3rd column), assuming your combo box is
named cboEmpName, set the textbox' ControlSource to:

=cboEmpName.Column(2)

If your EmpName field is a single field rather than separate Last and First
names, you can also use the wizard to create the combo box. Select "Hide Key
Field" to set the first Column Width to zero, then size the others
dynamically on screen, and follow the rest of the prompts. To enable the
wizard from form design view, select View, Toolbox, and toggle on the button
with the wand and stars.

Hope that helps.
Sprinks

Newbie User:
I am trying to create a form which contains a combo box. Now the combo box
is based on fields from one table that lists the employees names. I want to
be able to select the first and last name for an employee, and then have
Access include the corresponding information into the fields I list.
For example, If i select Miles Davis, I would want to see his telephone #,
location, and employee number automatically without having to select them
from a list of over 1500. Is there an easy way to do this?

thanks

 

[Guest]

Sorry Sprinks, I think I was a little loopy from lunch. Thank you for your
help. I was able to correct it, so that when I select the name, all of the
corresponding fields follow. My only question now is, how to add new data
that is not on the original list, without having to add it to the original
table?

Hi, DKS1.

A combo box has several key properties which control how it behaves:

Control Source
The field in the form's underlying RecordSource in which to store the user's
selection, as defined by the BoundColumn property. This is normally a
numeric key field that uniquely identifies the record.

RowSource
An SQL query statement that "populates" the rows of the combo box. This can
include both standard and calculated fields.

BoundColumn
The index, beginning with 1, of the column to be stored in the ControlSource
when the user makes the selection. Normally, this is set to 1 so that the
key field is stored.

ColumnWidths
How much space on the screen is allocated to each column in the drop-down
list, separated by semi-colons. When the user makes a selection, the first
non-zero width column is displayed.

So, in your case, your combo box is likely to be bound to (i.e., its
ControlSource is set to) a numeric EmployeeID field.

The RowSource, assuming that the last name and first name are separate
fields, and that you'd like to display them in "LastName, FirstName" format,
would be:

SELECT YourTable.EmpID, [LastName] & ", " & [FirstName] AS EmpName,
[PhoneNumber], [Location]
FROM YourTable
ORDER BY [LastName] & ", " & [FirstName];

You would replace "YourTable" with the name of your employee table, and
adjust the fieldnames as appropriate.

Set the BoundColumn to 1. Be sure that the ControlSource matches type with
the EmpID field. Normally both would be the Integer or AutoNumber type.

To display only the name in the dropdown, set the ColumnWidths property to:

0";x";0";0"

where x is large enough to display the longest name.

Finally, to display any of the other columns in a separate textbox, use the
combo box' Column property to set the textbox' ControlSource property:

=YourComboBox.Column(Index)

where Index is the column number, beginning with 0. For example, to display
the PhoneNumber in a textbox (the 3rd column), assuming your combo box is
named cboEmpName, set the textbox' ControlSource to:

=cboEmpName.Column(2)

If your EmpName field is a single field rather than separate Last and First
names, you can also use the wizard to create the combo box. Select "Hide Key
Field" to set the first Column Width to zero, then size the others
dynamically on screen, and follow the rest of the prompts. To enable the
wizard from form design view, select View, Toolbox, and toggle on the button
with the wand and stars.

Hope that helps.
Sprinks

Newbie User:
I am trying to create a form which contains a combo box. Now the combo box
is based on fields from one table that lists the employees names. I want to
be able to select the first and last name for an employee, and then have
Access include the corresponding information into the fields I list.
For example, If i select Miles Davis, I would want to see his telephone #,
location, and employee number automatically without having to select them
from a list of over 1500. Is there an easy way to do this?

thanks

 

[Guest]

Hi, DKS1.

Glad you have it working. To do this, use the combo box' LimitToList
property. Set it to Yes. If you enter a name not in the list, it will
trigger the NotInList event and run the procedure you define for this event.

Search the forum or Google for "Limit to List", and you should find what you
need.

Sprinks

Sorry Sprinks, I think I was a little loopy from lunch. Thank you for your
help. I was able to correct it, so that when I select the name, all of the
corresponding fields follow. My only question now is, how to add new data
that is not on the original list, without having to add it to the original
table?

Hi, DKS1.

A combo box has several key properties which control how it behaves:

Control Source
The field in the form's underlying RecordSource in which to store the user's
selection, as defined by the BoundColumn property. This is normally a
numeric key field that uniquely identifies the record.

RowSource
An SQL query statement that "populates" the rows of the combo box. This can
include both standard and calculated fields.

BoundColumn
The index, beginning with 1, of the column to be stored in the ControlSource
when the user makes the selection. Normally, this is set to 1 so that the
key field is stored.

ColumnWidths
How much space on the screen is allocated to each column in the drop-down
list, separated by semi-colons. When the user makes a selection, the first
non-zero width column is displayed.

So, in your case, your combo box is likely to be bound to (i.e., its
ControlSource is set to) a numeric EmployeeID field.

The RowSource, assuming that the last name and first name are separate
fields, and that you'd like to display them in "LastName, FirstName" format,
would be:

SELECT YourTable.EmpID, [LastName] & ", " & [FirstName] AS EmpName,
[PhoneNumber], [Location]
FROM YourTable
ORDER BY [LastName] & ", " & [FirstName];

You would replace "YourTable" with the name of your employee table, and
adjust the fieldnames as appropriate.

Set the BoundColumn to 1. Be sure that the ControlSource matches type with
the EmpID field. Normally both would be the Integer or AutoNumber type.

To display only the name in the dropdown, set the ColumnWidths property to:

0";x";0";0"

where x is large enough to display the longest name.

Finally, to display any of the other columns in a separate textbox, use the
combo box' Column property to set the textbox' ControlSource property:

=YourComboBox.Column(Index)

where Index is the column number, beginning with 0. For example, to display
the PhoneNumber in a textbox (the 3rd column), assuming your combo box is
named cboEmpName, set the textbox' ControlSource to:

=cboEmpName.Column(2)

If your EmpName field is a single field rather than separate Last and First
names, you can also use the wizard to create the combo box. Select "Hide Key
Field" to set the first Column Width to zero, then size the others
dynamically on screen, and follow the rest of the prompts. To enable the
wizard from form design view, select View, Toolbox, and toggle on the button
with the wand and stars.

Hope that helps.
Sprinks

Newbie User:
I am trying to create a form which contains a combo box. Now the combo box
is based on fields from one table that lists the employees names. I want to
be able to select the first and last name for an employee, and then have
Access include the corresponding information into the fields I list.
For example, If i select Miles Davis, I would want to see his telephone #,
location, and employee number automatically without having to select them
from a list of over 1500. Is there an easy way to do this?

thanks

 

[Guest]

Thanks, will do

Hi, DKS1.

Glad you have it working. To do this, use the combo box' LimitToList
property. Set it to Yes. If you enter a name not in the list, it will
trigger the NotInList event and run the procedure you define for this event.

Search the forum or Google for "Limit to List", and you should find what you
need.

Sprinks

Sorry Sprinks, I think I was a little loopy from lunch. Thank you for your
help. I was able to correct it, so that when I select the name, all of the
corresponding fields follow. My only question now is, how to add new data
that is not on the original list, without having to add it to the original
table?

Hi, DKS1.

A combo box has several key properties which control how it behaves:

Control Source
The field in the form's underlying RecordSource in which to store the user's
selection, as defined by the BoundColumn property. This is normally a
numeric key field that uniquely identifies the record.

RowSource
An SQL query statement that "populates" the rows of the combo box. This can
include both standard and calculated fields.

BoundColumn
The index, beginning with 1, of the column to be stored in the ControlSource
when the user makes the selection. Normally, this is set to 1 so that the
key field is stored.

ColumnWidths
How much space on the screen is allocated to each column in the drop-down
list, separated by semi-colons. When the user makes a selection, the first
non-zero width column is displayed.

So, in your case, your combo box is likely to be bound to (i.e., its
ControlSource is set to) a numeric EmployeeID field.

The RowSource, assuming that the last name and first name are separate
fields, and that you'd like to display them in "LastName, FirstName" format,
would be:

SELECT YourTable.EmpID, [LastName] & ", " & [FirstName] AS EmpName,
[PhoneNumber], [Location]
FROM YourTable
ORDER BY [LastName] & ", " & [FirstName];

You would replace "YourTable" with the name of your employee table, and
adjust the fieldnames as appropriate.

Set the BoundColumn to 1. Be sure that the ControlSource matches type with
the EmpID field. Normally both would be the Integer or AutoNumber type.

To display only the name in the dropdown, set the ColumnWidths property to:

0";x";0";0"

where x is large enough to display the longest name.

Finally, to display any of the other columns in a separate textbox, use the
combo box' Column property to set the textbox' ControlSource property:

=YourComboBox.Column(Index)

where Index is the column number, beginning with 0. For example, to display
the PhoneNumber in a textbox (the 3rd column), assuming your combo box is
named cboEmpName, set the textbox' ControlSource to:

=cboEmpName.Column(2)

If your EmpName field is a single field rather than separate Last and First
names, you can also use the wizard to create the combo box. Select "Hide Key
Field" to set the first Column Width to zero, then size the others
dynamically on screen, and follow the rest of the prompts. To enable the
wizard from form design view, select View, Toolbox, and toggle on the button
with the wand and stars.

Hope that helps.
Sprinks
:

Newbie User:
I am trying to create a form which contains a combo box. Now the combo box
is based on fields from one table that lists the employees names. I want to
be able to select the first and last name for an employee, and then have
Access include the corresponding information into the fields I list.
For example, If i select Miles Davis, I would want to see his telephone #,
location, and employee number automatically without having to select them
from a list of over 1500. Is there an easy way to do this?

thanks