how to Bind combo box to primary key but display a description field using vba code?

[lianghua19]

Reference: http://www.techonthenet.com/access/c...bind_index.php



the link above shows how to manipulate it manually, but what i thought is using vba code to execute it automatically when i click a button on a form.

Here is the code what i thought, but apparently it seems there are errors in it.

Private Sub lookup()
Set db = CurrentDb
Set fld = db.TableDefs("TABLE_NAME").Fields("TABLE_FIELD")
Set p = f.CreateProperty("DisplayControl", dbInteger, 111)
fld.RowSourceType = "Table/Query"
fld.RowSource ("TABLE_QUERY")
fld.BoundColumn (1)
fld.ColumnCount (2)
fld.ColumnHeads (False)
fld.ColumnWidths = "0;1 "
fld.ListRows (8)
fld.ListWidth (100)
fld.LimitToList (True)
tdf.Fields.Append p
tdf.Fields.Refresh
DoCmd.Close
End Sub

Thanks in advance.

[boblarson]

Is this in Access or in something else? If the controls on the form are in Access you can simply set the control source of a text box to

=[ComboNameHereInBrackets].[Column](1)

where (1) is the second column of the combo (because it is zero based).

[lianghua19]

Is this in Access or in something else? If the controls on the form are in Access you can simply set the control source of a text box to

=[ComboNameHereInBrackets].[Column](1)

where (1) is the second column of the combo (because it is zero based).

sorry, i am still confused, would mind show me the certain statement?

Here i am using MS-ACCESS 2003, i have no idea what a "combox name" is, what i have is a query_table with id as a foreign key in my main table.

thanks buddy in advance.

[boblarson]

sorry, i am still confused, would mind show me the certain statement?

Here i am using MS-ACCESS 2003, i have no idea what a "combox name" is,

You have in your title - "How to Bind combo box to primary key but display a description ..."

ComboBox Name is the name of the combo box. The code I gave you is such that you would replace the ComboBoxNameInBrackets with the actual name of your combo box. So if the combo box has the name of

Combo32 then you would use

=[Combo32].[Column](1)

as the control source for the text box.

But that is only if you wanted to display the PK ID in the combo box. But now that I read your post title again, perhaps you just want to have the combo box show the description but have the PK ID as the part that is saved in the table. And that would be correct.

Now, just so we are on the same page - you should NOT be using combo boxes directly in the tables. See here for why:

You use them on forms that are bound to tables.

And to set up a combo box you can, if you wish, simply place the combo box on the form and then follow the wizard and it will help you set it up so that the ID is the saved part but the description is the part showing.

To do it manually you would change the combo box's ROW SOURCE to either use a table, query, or a SQL Statement. And you would want to have the ID field as the first field and then the description as the second. The only table in the query or SQL statement would be from the table which has both the ID and Description.

The Column Count property of the combo box needs to be set at 2 (if you have two fields in the row source). And then the Bound Column property would be 1 (if the ID is in the first column). Then the column widths property of the combo box would be set to 0";2" (where 0" means the first column won't show and then 2" is what the second column will be set to).

I hope that helps.

[lianghua19]

You have in your title - "How to Bind combo box to primary key but display a description ..."

ComboBox Name is the name of the combo box. The code I gave you is such that you would replace the ComboBoxNameInBrackets with the actual name of your combo box. So if the combo box has the name of

Combo32 then you would use

=[Combo32].[Column](1)

as the control source for the text box.

But that is only if you wanted to display the PK ID in the combo box. But now that I read your post title again, perhaps you just want to have the combo box show the description but have the PK ID as the part that is saved in the table. And that would be correct.

Now, just so we are on the same page - you should NOT be using combo boxes directly in the tables. See here for why:

You use them on forms that are bound to tables.

And to set up a combo box you can, if you wish, simply place the combo box on the form and then follow the wizard and it will help you set it up so that the ID is the saved part but the description is the part showing.

To do it manually you would change the combo box's ROW SOURCE to either use a table, query, or a SQL Statement. And you would want to have the ID field as the first field and then the description as the second. The only table in the query or SQL statement would be from the table which has both the ID and Description.

The Column Count property of the combo box needs to be set at 2 (if you have two fields in the row source). And then the Bound Column property would be 1 (if the ID is in the first column). Then the column widths property of the combo box would be set to 0";2" (where 0" means the first column won't show and then 2" is what the second column will be set to).

I hope that helps.

hi boblarson, I just wanna say thank you so much for your patient, you got what i mean here, actually we do combo box in the table is because it's comprehensive for people read the table (the description not just ID number), because the fact the table in ms-access is linked to the Oracle, if i can write a script to execute the combo box(look-up tables and main tables) combination, we dont need to do the re-combination manually again when i re-link all the tables from Oracle. This project, we don't use form, so it doesnt bothered. Thanks buddy.

[boblarson]

Oh, and I forgot to include this link:
http://www.mvps.org/access/lookupfields.htm

[lianghua19]

hi boblarson, I just wanna say thank you so much for your patient, you got what i mean here, actually we do combo box in the table is because it's comprehensive for people read the table (the description not just ID number), because the fact the table in ms-access is linked to the Oracle, if i can write a script to execute the combo box(look-up tables and main tables) combination, we dont need to do the re-combination manually again when i re-link all the tables from Oracle. This project, we don't use form, so it doesnt bothered. Thanks buddy.