By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

How to Derive Dual use of Combo Box

P: n/a
I have been searching the archives in an effort to discover how to
derive a dual use of a single combo box - so far no go. I found the
piece below which pretty much represents the usage. To summarize:

The cboKeyword box on the frmCustomer looks up Keywords from a
separate table that have just a list of keywords and a KeyID number.
The bound column is tied to the autonumber and is saved in the
frmCustomer table. The keywords appear in the combo boxes.

Now, what I need assistance with is the need to extract the keyword
name associated with each record on frmCustomer and saved it in a
textbox on the form and in the frmCustomer table because there are
several reports that show the related keywords. Currently, only the
KeyID appears in the report columns. Aside from running an update
query, how can the keyword name be additionally saved in the
frmCustomer table?

Thanks for any assistance. Dalan
From the archives circa 2-99:

I always use two fields in the lookup table: an autonumber ID and the
data element. The main table field to which the combo box is bound is
a long
integer data type. That way, with the ID stored in the main table, If
I ever
change the name of the lookup table data item, it does not require
massive
updating. Use two columns in the combo box, set the bound column to 1
and
the width of column 1 to 0. You don't have to set the width of the
second
column since it will automatically take up the width of the combo box.
The
query under the form must not include the lookup table. The number ID
to
which the combo box is bound will cause the combo box to lookup the
record
and display the 2nd column which is the data element you want to see.
This
method works with numerous combo boxes and lookup tables. A one to
many
relationship exists between the lookup tables and the main table you
are
working with.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Do you have the KeyID and Keyword as a Value List in the Row Source or are
they in a table and the Row Source is a Table/Query? If not the latter, make
a new table with the 2 fields and place the items there. Next, for your
report, include this table in the query that feeds your report. Link the
table with the present table that has only the KeyID field. To Link the
tables, drag KeyID from one table to KeyID on the other table. Next, instead
of including the KeyID field in the query's output, include the Keyword
field from the added table. This will give you the Keyword in the report
instead of the KeyID.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I have been searching the archives in an effort to discover how to
derive a dual use of a single combo box - so far no go. I found the
piece below which pretty much represents the usage. To summarize:

The cboKeyword box on the frmCustomer looks up Keywords from a
separate table that have just a list of keywords and a KeyID number.
The bound column is tied to the autonumber and is saved in the
frmCustomer table. The keywords appear in the combo boxes.

Now, what I need assistance with is the need to extract the keyword
name associated with each record on frmCustomer and saved it in a
textbox on the form and in the frmCustomer table because there are
several reports that show the related keywords. Currently, only the
KeyID appears in the report columns. Aside from running an update
query, how can the keyword name be additionally saved in the
frmCustomer table?

Thanks for any assistance. Dalan
From the archives circa 2-99:

I always use two fields in the lookup table: an autonumber ID and the
data element. The main table field to which the combo box is bound is
a long
integer data type. That way, with the ID stored in the main table, If
I ever
change the name of the lookup table data item, it does not require
massive
updating. Use two columns in the combo box, set the bound column to 1
and
the width of column 1 to 0. You don't have to set the width of the
second
column since it will automatically take up the width of the combo box.
The
query under the form must not include the lookup table. The number ID
to
which the combo box is bound will cause the combo box to lookup the
record
and display the 2nd column which is the data element you want to see.
This
method works with numerous combo boxes and lookup tables. A one to
many
relationship exists between the lookup tables and the main table you
are
working with.

Nov 12 '05 #2

P: n/a
I can't seem to get this to work. Here is some additional information:

1. The Row/Source Type is Table/Query

2. The Row Source is SELECT DISTINCTROW [tblKeyword].[Keyword],
[tblKeyword].[KeyID] FROM [tblKeyword] ORDER BY [tblKeyword].[Keyword]

3. Column Count is 2 and Bound Column is 2

4. The Record Source is Keyword

5. The Combo Box displays the names of the Keywords and the resulting
values saved in tblCustomers, Keyword fields are the KeyID numbers

6. Always have had two tables: tblCustomers and tblKeywords, the
latter having only two fields - Keyword and KeyID

7. In Relationships, the tables are joined by the ID autonumbers

8. Creating a new query with both of the above tables and dragging the
Keyword field to the grid from tblKeywords results in nothing
regardless of the type of join used

9. Consequently, I cannot base any report on the query to obtain the
name of the Keyword. Of course, if I use Keyword from tblCustomers,
then the KeyID number appears.
Any assistance or suggestions will be welcomed. Thanks.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<p2******************@newssvr23.news.prodigy. com>...
Do you have the KeyID and Keyword as a Value List in the Row Source or are
they in a table and the Row Source is a Table/Query? If not the latter, make
a new table with the 2 fields and place the items there. Next, for your
report, include this table in the query that feeds your report. Link the
table with the present table that has only the KeyID field. To Link the
tables, drag KeyID from one table to KeyID on the other table. Next, instead
of including the KeyID field in the query's output, include the Keyword
field from the added table. This will give you the Keyword in the report
instead of the KeyID.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I have been searching the archives in an effort to discover how to
derive a dual use of a single combo box - so far no go. I found the
piece below which pretty much represents the usage. To summarize:

The cboKeyword box on the frmCustomer looks up Keywords from a
separate table that have just a list of keywords and a KeyID number.
The bound column is tied to the autonumber and is saved in the
frmCustomer table. The keywords appear in the combo boxes.

Now, what I need assistance with is the need to extract the keyword
name associated with each record on frmCustomer and saved it in a
textbox on the form and in the frmCustomer table because there are
several reports that show the related keywords. Currently, only the
KeyID appears in the report columns. Aside from running an update
query, how can the keyword name be additionally saved in the
frmCustomer table?

Thanks for any assistance. Dalan
From the archives circa 2-99:

I always use two fields in the lookup table: an autonumber ID and the
data element. The main table field to which the combo box is bound is
a long
integer data type. That way, with the ID stored in the main table, If
I ever
change the name of the lookup table data item, it does not require
massive
updating. Use two columns in the combo box, set the bound column to 1
and
the width of column 1 to 0. You don't have to set the width of the
second
column since it will automatically take up the width of the combo box.
The
query under the form must not include the lookup table. The number ID
to
which the combo box is bound will cause the combo box to lookup the
record
and display the 2nd column which is the data element you want to see.
This
method works with numerous combo boxes and lookup tables. A one to
many
relationship exists between the lookup tables and the main table you
are
working with.

Nov 12 '05 #3

P: n/a
> 7. In Relationships, the tables are joined by the ID autonumbers

Have you joined the autonumbers from each table? You should join the ID
autonumber from tblKeyword to the Keyword field in tblCustomers. You will
need this same join in the query. You can then include Keyword from
tblKeyword in the query and you will get the text description instead of the
ID number.

If you make the join in Relationships, this join will be automatically
created when you create the query, but you'll have to change and existing
query yourself.

--
Wayne Morgan
Microsoft Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I can't seem to get this to work. Here is some additional information:

1. The Row/Source Type is Table/Query

2. The Row Source is SELECT DISTINCTROW [tblKeyword].[Keyword],
[tblKeyword].[KeyID] FROM [tblKeyword] ORDER BY [tblKeyword].[Keyword]

3. Column Count is 2 and Bound Column is 2

4. The Record Source is Keyword

5. The Combo Box displays the names of the Keywords and the resulting
values saved in tblCustomers, Keyword fields are the KeyID numbers

6. Always have had two tables: tblCustomers and tblKeywords, the
latter having only two fields - Keyword and KeyID

7. In Relationships, the tables are joined by the ID autonumbers

8. Creating a new query with both of the above tables and dragging the
Keyword field to the grid from tblKeywords results in nothing
regardless of the type of join used

9. Consequently, I cannot base any report on the query to obtain the
name of the Keyword. Of course, if I use Keyword from tblCustomers,
then the KeyID number appears.
Any assistance or suggestions will be welcomed. Thanks.
"Wayne Morgan" <co***************************@hotmail.com> wrote in

message news:<p2******************@newssvr23.news.prodigy. com>...
Do you have the KeyID and Keyword as a Value List in the Row Source or are they in a table and the Row Source is a Table/Query? If not the latter, make a new table with the 2 fields and place the items there. Next, for your
report, include this table in the query that feeds your report. Link the
table with the present table that has only the KeyID field. To Link the
tables, drag KeyID from one table to KeyID on the other table. Next, instead of including the KeyID field in the query's output, include the Keyword
field from the added table. This will give you the Keyword in the report
instead of the KeyID.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I have been searching the archives in an effort to discover how to
derive a dual use of a single combo box - so far no go. I found the
piece below which pretty much represents the usage. To summarize:

The cboKeyword box on the frmCustomer looks up Keywords from a
separate table that have just a list of keywords and a KeyID number.
The bound column is tied to the autonumber and is saved in the
frmCustomer table. The keywords appear in the combo boxes.

Now, what I need assistance with is the need to extract the keyword
name associated with each record on frmCustomer and saved it in a
textbox on the form and in the frmCustomer table because there are
several reports that show the related keywords. Currently, only the
KeyID appears in the report columns. Aside from running an update
query, how can the keyword name be additionally saved in the
frmCustomer table?

Thanks for any assistance. Dalan
From the archives circa 2-99:

I always use two fields in the lookup table: an autonumber ID and the
data element. The main table field to which the combo box is bound is
a long
integer data type. That way, with the ID stored in the main table, If
I ever
change the name of the lookup table data item, it does not require
massive
updating. Use two columns in the combo box, set the bound column to 1
and
the width of column 1 to 0. You don't have to set the width of the
second
column since it will automatically take up the width of the combo box.
The
query under the form must not include the lookup table. The number ID
to
which the combo box is bound will cause the combo box to lookup the
record
and display the 2nd column which is the data element you want to see.
This
method works with numerous combo boxes and lookup tables. A one to
many
relationship exists between the lookup tables and the main table you
are
working with.

Nov 12 '05 #4

P: n/a
Thanks for the tip Wayne. If I understand correctly, joining the two
tables via the ID autonumbers, which I have done, will result in no
keywords appearing in the query. I presume that I will need to change
the Data Type of the Keyword field in frmCustomers from Text to Long
Integer to have compatiblity - well it won't work unless I do. Because
the data in the Keyword field in frmCustomers is no longer a text
value, but rather numeric from making the change to the bound column
(KeyID) in the combo box. Apparently, I have found the light switch.
Thanks again. Dalan
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Ta*******************@newssvr33.news.prodigy .com>...
7. In Relationships, the tables are joined by the ID autonumbers


Have you joined the autonumbers from each table? You should join the ID
autonumber from tblKeyword to the Keyword field in tblCustomers. You will
need this same join in the query. You can then include Keyword from
tblKeyword in the query and you will get the text description instead of the
ID number.

If you make the join in Relationships, this join will be automatically
created when you create the query, but you'll have to change and existing
query yourself.

--
Wayne Morgan
Microsoft Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I can't seem to get this to work. Here is some additional information:

1. The Row/Source Type is Table/Query

2. The Row Source is SELECT DISTINCTROW [tblKeyword].[Keyword],
[tblKeyword].[KeyID] FROM [tblKeyword] ORDER BY [tblKeyword].[Keyword]

3. Column Count is 2 and Bound Column is 2

4. The Record Source is Keyword

5. The Combo Box displays the names of the Keywords and the resulting
values saved in tblCustomers, Keyword fields are the KeyID numbers

6. Always have had two tables: tblCustomers and tblKeywords, the
latter having only two fields - Keyword and KeyID

7. In Relationships, the tables are joined by the ID autonumbers

8. Creating a new query with both of the above tables and dragging the
Keyword field to the grid from tblKeywords results in nothing
regardless of the type of join used

9. Consequently, I cannot base any report on the query to obtain the
name of the Keyword. Of course, if I use Keyword from tblCustomers,
then the KeyID number appears.
Any assistance or suggestions will be welcomed. Thanks.
"Wayne Morgan" <co***************************@hotmail.com> wrote in

message news:<p2******************@newssvr23.news.prodigy. com>...
Do you have the KeyID and Keyword as a Value List in the Row Source or are they in a table and the Row Source is a Table/Query? If not the latter, make a new table with the 2 fields and place the items there. Next, for your
report, include this table in the query that feeds your report. Link the
table with the present table that has only the KeyID field. To Link the
tables, drag KeyID from one table to KeyID on the other table. Next, instead of including the KeyID field in the query's output, include the Keyword
field from the added table. This will give you the Keyword in the report
instead of the KeyID.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
> I have been searching the archives in an effort to discover how to
> derive a dual use of a single combo box - so far no go. I found the
> piece below which pretty much represents the usage. To summarize:
>
> The cboKeyword box on the frmCustomer looks up Keywords from a
> separate table that have just a list of keywords and a KeyID number.
> The bound column is tied to the autonumber and is saved in the
> frmCustomer table. The keywords appear in the combo boxes.
>
> Now, what I need assistance with is the need to extract the keyword
> name associated with each record on frmCustomer and saved it in a
> textbox on the form and in the frmCustomer table because there are
> several reports that show the related keywords. Currently, only the
> KeyID appears in the report columns. Aside from running an update
> query, how can the keyword name be additionally saved in the
> frmCustomer table?
>
> Thanks for any assistance. Dalan
>

Nov 12 '05 #5

P: n/a
Well, what will show in the query depends on what fields you choose to show.
You could show both. By linking the two tables on this field, you will get
the value from the Keyword table that matches the ID in the Customer table.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
Thanks for the tip Wayne. If I understand correctly, joining the two
tables via the ID autonumbers, which I have done, will result in no
keywords appearing in the query. I presume that I will need to change
the Data Type of the Keyword field in frmCustomers from Text to Long
Integer to have compatiblity - well it won't work unless I do. Because
the data in the Keyword field in frmCustomers is no longer a text
value, but rather numeric from making the change to the bound column
(KeyID) in the combo box. Apparently, I have found the light switch.
Thanks again. Dalan

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.