Form auto-fill questions

[Guest]

Is there anyway to select a value from a combo box and have a number of
fields in a form populated automatically. For example, I have a Rolodex
table that contains a business's name, address, city, state, etc., and I want
to be able to select a business name from a combo box and have these fields
populated automatically for display in their respective text boxes. Is this
possible? Currently, I have a form with these text boxes and a combo box
that is automatically populated when the form is opened, but when I select a
business name from the combo box for database record is modified to reflect
the combo box's value. I do not want to edit these records just display
their respective information in text boxes that will be used on a subform.

Thank you in advance.

 

[Guest]

Hi, Jim.

There are two ways to do this.

1. Base your form on a query that includes the fields from your Business
table that you wish to display, and all of the fields from the table the form
was based on. Be sure NOT to include the Primary Key from the Business
table, only its related Foreign Key from your other table. Then place
textboxes on your form like usual. They will display as soon as the foreign
key is entered.

2. Use the Column property of the combo box, using the index 0 to refer to
column 1, 1 for column 2, etc.. For example, if your RowSource is

SELECT ID, CompanyName, Addr1, Addr2, City, State, Zip, Phone
FROM Businesses
ORDER BY CompanyName;

to disply the Zip field (the seventh column), set the textbox' Control
Source to:

=Me!MyComboBox.Column(6)

Hope that helps.
Sprinks

 

[Guest]

I am a little confused. I tried the second option in the following was
placed in the text box: #Name? When I use the following for the Control
Source property: =[Me]![MyComboBox].[Column](6). Any ideas?

 

[Guest]

Hi, Jim.

It could be either:

- You need to change "MyComboBox" in the expression to the name of your
combo box.
- You do not have seven columns in the Row Source property of the cbox.

If you cannot resolve it, please post the Name and RowSource properties of
your combo box.

Sprinks

I am a little confused. I tried the second option in the following was
placed in the text box: #Name? When I use the following for the Control
Source property: =[Me]![MyComboBox].[Column](6). Any ideas?

Hi, Jim.

There are two ways to do this.

1. Base your form on a query that includes the fields from your Business
table that you wish to display, and all of the fields from the table the form
was based on. Be sure NOT to include the Primary Key from the Business
table, only its related Foreign Key from your other table. Then place
textboxes on your form like usual. They will display as soon as the foreign
key is entered.

2. Use the Column property of the combo box, using the index 0 to refer to
column 1, 1 for column 2, etc.. For example, if your RowSource is

SELECT ID, CompanyName, Addr1, Addr2, City, State, Zip, Phone
FROM Businesses
ORDER BY CompanyName;

to disply the Zip field (the seventh column), set the textbox' Control
Source to:

=Me!MyComboBox.Column(6)

Hope that helps.
Sprinks

 

[Guest]

My combo box Row Source is: "SELECT CompanyID,[Business Name] FROM Rolodex",
and the name is cboName

Hi, Jim.

It could be either:

- You need to change "MyComboBox" in the expression to the name of your
combo box.
- You do not have seven columns in the Row Source property of the cbox.

If you cannot resolve it, please post the Name and RowSource properties of
your combo box.

Sprinks

I am a little confused. I tried the second option in the following was
placed in the text box: #Name? When I use the following for the Control
Source property: =[Me]![MyComboBox].[Column](6). Any ideas?

Hi, Jim.

There are two ways to do this.

1. Base your form on a query that includes the fields from your Business
table that you wish to display, and all of the fields from the table the form
was based on. Be sure NOT to include the Primary Key from the Business
table, only its related Foreign Key from your other table. Then place
textboxes on your form like usual. They will display as soon as the foreign
key is entered.

2. Use the Column property of the combo box, using the index 0 to refer to
column 1, 1 for column 2, etc.. For example, if your RowSource is

SELECT ID, CompanyName, Addr1, Addr2, City, State, Zip, Phone
FROM Businesses
ORDER BY CompanyName;

to disply the Zip field (the seventh column), set the textbox' Control
Source to:

=Me!MyComboBox.Column(6)

Hope that helps.
Sprinks

:

Is there anyway to select a value from a combo box and have a number of
fields in a form populated automatically. For example, I have a Rolodex
table that contains a business's name, address, city, state, etc., and I want
to be able to select a business name from a combo box and have these fields
populated automatically for display in their respective text boxes. Is this
possible? Currently, I have a form with these text boxes and a combo box
that is automatically populated when the form is opened, but when I select a
business name from the combo box for database record is modified to reflect
the combo box's value. I do not want to edit these records just display
their respective information in text boxes that will be used on a subform.

Thank you in advance.

 

[Guest]

Hi, Jim. OK.

Right now you only have two columns in your combo box. You could add them
manually, but it's probably easier to simply use the wizard to add a new one,
this time selecting all of the fields you'd like to display in other
textboxes. Presuming you don't want to see all of these fields in your
drop-down list, set all of the other column widths (in the combo box' Column
Width property) to 0". Your resulting RowSource property will then look
something like:

"SELECT CompanyID, [Business Name], Address1, Address2, City, State, Zip
FROM Rolodex"

Then, to set a textbox to the value of one of the columns of the row
selected by the user, use the Column property with an index of the column
number minus one. So for Address1 in the above example, the ControlSource is:

=cboName.Column(2)

Hope that helps.
Sprinks

My combo box Row Source is: "SELECT CompanyID,[Business Name] FROM Rolodex",
and the name is cboName

Hi, Jim.

It could be either:

- You need to change "MyComboBox" in the expression to the name of your
combo box.
- You do not have seven columns in the Row Source property of the cbox.

If you cannot resolve it, please post the Name and RowSource properties of
your combo box.

Sprinks

I am a little confused. I tried the second option in the following was
placed in the text box: #Name? When I use the following for the Control
Source property: =[Me]![MyComboBox].[Column](6). Any ideas?

:

Hi, Jim.

There are two ways to do this.

1. Base your form on a query that includes the fields from your Business
table that you wish to display, and all of the fields from the table the form
was based on. Be sure NOT to include the Primary Key from the Business
table, only its related Foreign Key from your other table. Then place
textboxes on your form like usual. They will display as soon as the foreign
key is entered.

2. Use the Column property of the combo box, using the index 0 to refer to
column 1, 1 for column 2, etc.. For example, if your RowSource is

SELECT ID, CompanyName, Addr1, Addr2, City, State, Zip, Phone
FROM Businesses
ORDER BY CompanyName;

to disply the Zip field (the seventh column), set the textbox' Control
Source to:

=Me!MyComboBox.Column(6)

Hope that helps.
Sprinks

:

Is there anyway to select a value from a combo box and have a number of
fields in a form populated automatically. For example, I have a Rolodex
table that contains a business's name, address, city, state, etc., and I want
to be able to select a business name from a combo box and have these fields
populated automatically for display in their respective text boxes. Is this
possible? Currently, I have a form with these text boxes and a combo box
that is automatically populated when the form is opened, but when I select a
business name from the combo box for database record is modified to reflect
the combo box's value. I do not want to edit these records just display
their respective information in text boxes that will be used on a subform.

Thank you in advance.