Show data in table from cbo and not the number

[TKM]

I know it should show a number from a cbo selection in the table but how do I
show the actual selection (data) insteaad of the number from a cbo selection?

 

[missinglinq via AccessMonster.com]

Where the combobox is named YourComboBox, the textbox on your form is named
YourTextBoxName and you have a field in your table set as the Control Source
for a form's textbox use this code:

Private Sub YourComboBox_AfterUpdate()
Me.YourTextboxName = YourComboBox
End Sub

and the selection will be assigned to the field in the table.

 

[John W. Vinson]

I know it should show a number from a cbo selection in the table but how do I
show the actual selection (data) insteaad of the number from a cbo selection?

A Combo Box is based (usually) on a Query, the combo's RowSource. You can
include whatever field or fields you want to see in that query.

Set the combo's Column Count property to include all the columns that you want
to use; set the Bound Column property to the value you want stored in your
table (typically the numeric ID field); set the Control Source to the name of
the table field into which that value should be stored; and set the
ColumnWidths property to set the column width of the number field to 0, and
the width of the field you want displayed on the combo to the desired size of
that column.

For instance, you might have a query:

SELECT CustomerID, LastName & ", " & FirstName, Phone, EMail
FROM Customers
ORDER BY LastName, FirstName;

as the row source of a query to display all customer names in alphabetical
order by last name. Set the Column Count to 4, the Bound Column to 1, the
Control Source to CustomerID, and the ColumnWidths to

0;1.25;.5;2

to store the CustomerID in your table but display "Jones, Michael" when the
combo box is not dropped down. It will show

Jones, Michael 888-555-5555 [email protected]

when the combo is dropped down.

John W. Vinson [MVP]

 

[TKM]

Are you saying put this code on the form or? I just have a cbo that sends the
ID number that is associated with the selection to the field in the table. Do
I put this code after renameing in my control source of my cbo? Thanks again

 

[TKM]

I think were off track? All I want is the actual name of my company to show
in my table and not the company ID field that is associated with the compnay
that was selected by the user. Does this help?

 

[John W. Vinson]

I think were off track? All I want is the actual name of my company to show
in my table and not the company ID field that is associated with the compnay
that was selected by the user.

Well... No.

You do NOT want to store the name of the company anyplace other than the
company table.

If you need to see the name of the company in conjunction with other data in
your table, use either a Combo Box on the Form you're using to edit or display
the data (storing the ID but displaying the name); or use a Query joining your
table to the company table (e.g. for a Report).

Tables should NOT be used for data display, data entry, or data editing: their
function is data *storage*, and the user should neither know nor care whether
the table contains a text string or a numeric key. You're using a relational
database - *use it as one*!

John W. Vinson [MVP]

 

[TKM]

Missinglinq,

You have the closest of what I am looking for but could you clarify it for me.

thank you

 

[TKM]

I know why data should not be used here or there and why this and that.
However I was asked to do this and I am getting paid for it. However I dont
know how but kow there is an easy way. For those who write and tell me what I
should and should not do..this database will be torn down within months and a
new robust CORRECT ONE will be constructed. However this database needs to
follow the original bull standard practices from the orignal owner and this
is it. So if you have a way to help me I would appreciate it. If not please
dont answer.

 

[Douglas J. Steele]

Regardless what you may think, John's answer is the correct one.

You can always create a query that joins your two tables and includes the
text. You can then use the query wherever you would otherwise have used the
table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[John W. Vinson]

I know why data should not be used here or there and why this and that.
However I was asked to do this and I am getting paid for it. However I dont
know how but kow there is an easy way. For those who write and tell me what I
should and should not do..this database will be torn down within months and a
new robust CORRECT ONE will be constructed. However this database needs to
follow the original bull standard practices from the orignal owner and this
is it. So if you have a way to help me I would appreciate it. If not please
dont answer.

<shrug>

OK, if the boss insists that you do it wrong, realizing that it will make it
vastly harder to correct errors in company names, and that it is unnecessary
to do so....

Add a Text field in your table, Company.
Run an Update query by joining CompanyID in this table to CompanyID in the
lookup table; update the new text Company field to [Companies].[Company].

Change the RowSource of the combo box to select just the company name, not the
ID; change its control source to the new Company text field.

If you really want to burn your bridges, delete the CompanyID field.

John W. Vinson [MVP]