Return "Text Value" to a Table from a Combobox instead of "ID"

[carsan552]

Hey,

I have made a form with a combobox where the user can choose to pick a specific company or pick ALL companies. The table which the combobox is built upon consists of two columns [CompanyID, Company] where CompanyID is the primary key. The problem I have is that the combobox only return the CompanyID to the table, and what ive understood thats what its suppose to do. But since the users of this database is only intrested in the "text value" of the company instead of the CompanyID I need to change it.

Ive tryed to use "DLookUp()" which works aslong as the user picks one specific company. But as soon as he picks "ALL" i get nothing. That is also not so strange since DLookUp() only returns one value what Ive understood.

Ive also tryed to change the two columns in the table to [Company, CompanyID (PK)]. One person told me that Combobox read from the left to the right. But for me this diden't work either.

Ive also heard you can use [Forms]![My Form]![My cbobox].[Column](1) or something. But I don't know if im doing right thing since I get a error message, "Function is not known" when I try this.

If you have any tips or trix how to fix this your more than welcome.

Greetings Access Newbie Carl

 

[veraloopy]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

On the property settings of the combo box, have a look under the 'Format' tab and the Column count should be set to 2. The 'Column Widths' should then be something like 0cm;2.54cm

If you're talking about the value being stored back in the table, then check the property settings of the combo box, under the 'Data' tab to see what column the value is bound to (Bound Column)

 

[carsan552]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

Thx Bob,

Ok from now one I will store the data as the "ID" , but do you mean that the combobox should be built upon a query instead of a table?.
Or should i store the data in a table , and the use a query to change the information.

Sry for asking again ;(

Greetings Carl

 

[carsan552]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

Thx I will try!

Greetings Carl

 

[carsan552]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

Ok fine, I changed the boundary to the column I want. And I now recieve my right information. Thanks alot.

There is just one thing i dont get , when i open the form there is a (*) in the combobox (Which i havent had before I made the change), when i use (*) in the combobox I get all companies, that is good , but the (*) is only there when i first enter the form. If i pick some other company first it dissapeares.

But the original "ALL" button which i had before no longer seems to work. Can it have something to do with that ive changed boundary and im code doesnt work anymore.

SELECT "*" AS CompanyID, "ALL" AS Company, 0 AS SortOrder FROM tblCompany UNION SELECT CompanyID,Company, 1 FROM tblCompany ORDER BY SortOrder, Company;

I really appreciated your responses. Ive been trying to figure this out all week , got only responses like "Go Die - Nooob"

Greetings
Carl

 

[carsan552]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

solved it! thx guys!

 

[Abbosy]

Re: Return "Text Value" to a Table from a Combobox instead of "ID"

Thanks very much boblarson and veraloopy,

You helped answering my post dabbed "Table doesn't read combo box info correctly"
I changed the values in the query and everything is working the way I wanted it to do.

Many thanks to all of you